none
How can one record by inserted in a table by picking the value from form. RRS feed

  • Question

  • I am trying to write VBA code in Access 2007 to insert a record in table by picking the values from controls in the form. Following SQL was used and bound to command button

    Private Sub Command4_Click()
    Dim cnn1 As ADODB.Connection
    Set cnn1 = CurrentProject.Connection
    Dim MySql As String
    Dim A As String
    Dim B As String
    A = Me.Text0.Value
    B = Me.Text2.Value
    MySql = "INSERT INTO Table1 (fielda,fieldb) Values (A,B)"
    DoCmd.SetWarnings False
    DoCmd.RunSQL MySql
    DoCmd.SetWarnings True
    End Sub

    While executing the above code by clicking on the command button, the dialogue button opened for the values of A and B instead of taking the value from textbox "text0" and "text2".

    Can I be advised how can id insert the values in the table from text box. I am new to access and I also have no formal education on any computer application.

    Thanks

    Manoj

    Monday, March 12, 2012 5:01 AM

Answers

  • Hi manoj,

    You may try to use double quotes around the Variables when you are dealing with Text fields.

    To do so, you can use the Chr(34) in VBA to simulate the double quote.

    So the modified code will look like this:

    Private Sub Command4_Click()
     Dim cnn1 As ADODB.Connection
     Set cnn1 = CurrentProject.Connection
     
     Dim MySql As String
     Dim A As String
     Dim B As String
     
     A = Me.Text0.Value
     B = Me.Text2.Value
     
     MySql = "INSERT INTO Table1 (fielda,fieldb) Values (" & Chr(34) & A & Chr(34) & "," & Chr(34) & B & Chr(34) & ")"
     ' use the connection execute command running the SQL
     cnn1.Execute MySql
    
     Set cnn1 = Nothing
     End Sub

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Monday, March 12, 2012 9:54 PM
    Moderator
  • Hi Manoj,

    Yes you are welcome, so in case of a Text Field you add the Chr(34) around the variable, in case of Number you don't need the Chr(34) around it.

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Monday, March 12, 2012 10:02 PM
    Moderator
  • Thanks

    Monday, March 12, 2012 10:17 PM

All replies

  • Hi Manoj,

    Because A and B are variables you need to use them as such in your SQL statement.

    Also I suggest using the cnn1.Execute command to run the SQL statement as part of your Connection you created.

    With these suggestions your code will look like this:

    Private Sub Command4_Click()
     Dim cnn1 As ADODB.Connection
     Set cnn1 = CurrentProject.Connection
     
     Dim MySql As String
     Dim A As String
     Dim B As String
     
     A = Me.Text0.Value
     B = Me.Text2.Value
     
     MySql = "INSERT INTO Table1 (fielda,fieldb) Values (" & A & "," & B & ")"
     ' use the connection execute command running the SQL
     cnn1.Execute MySql
    
     Set cnn1 = Nothing
     End Sub

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Monday, March 12, 2012 6:29 AM
    Moderator
  • Hi Danishani,

    Thanks for your guidance, This still not working for the following error

    "Run Time Error , No Value given for one or more required parameters."

    When I check the value of A and B in the immediate window, I get the values of related text box. I have also tried following SQL by replaceing single qotation mark around the variable A and B:

    "INSERT INTO Table1 (fielda,fieldb) Values (' & A & ',' & B & ')"
    In this case values "&A&, &B& have been iserted in the table1 for fielda and fieldb respectively.

    please advise,

    Thanks

    manoj

    Monday, March 12, 2012 9:45 PM
  • Hi manoj,

    You may try to use double quotes around the Variables when you are dealing with Text fields.

    To do so, you can use the Chr(34) in VBA to simulate the double quote.

    So the modified code will look like this:

    Private Sub Command4_Click()
     Dim cnn1 As ADODB.Connection
     Set cnn1 = CurrentProject.Connection
     
     Dim MySql As String
     Dim A As String
     Dim B As String
     
     A = Me.Text0.Value
     B = Me.Text2.Value
     
     MySql = "INSERT INTO Table1 (fielda,fieldb) Values (" & Chr(34) & A & Chr(34) & "," & Chr(34) & B & Chr(34) & ")"
     ' use the connection execute command running the SQL
     cnn1.Execute MySql
    
     Set cnn1 = Nothing
     End Sub

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Monday, March 12, 2012 9:54 PM
    Moderator
  • Hi Danishani,

    Code works fine for number value in text boxes, If any one text box has value in text it is giving error as advised earlier.

    Manoj 

    Monday, March 12, 2012 9:56 PM
  • Many Thanks,

    It is working now.

    Now i will find the reason why it was not working earlier.

    Many Thanks for your guidance

    Manoj

    Monday, March 12, 2012 10:00 PM
  • Hi Manoj,

    Yes you are welcome, so in case of a Text Field you add the Chr(34) around the variable, in case of Number you don't need the Chr(34) around it.

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Monday, March 12, 2012 10:02 PM
    Moderator
  • Thanks

    Monday, March 12, 2012 10:17 PM