code to add data from a form to a table in access


Keywords:ms  access 


Question: 

CurrentDb.Execute "INSERT INTO convenati(conid, location, surname, firstname, middlename, phone, email, dob, sex, mstatus, status, violated, date)"&_
    "VALUES(" & Me.txtid & ", '" & me txtlocation & "','" & Me.txtsurname & "','" & Me.txtmiddlename & "','" & Me.txtfirstname & "','" & Me.txtphone & "','" & Me.txtemail & "','" & Me. txtdate &"','" &_ Me.cbogender &"','" &_ Me.cbomstatus &"','" &_ Me.cboviolated & "','" & Me.txtdate & "')"

Please I need help with this code.

I have a table called convenati, a form called formconvenati and a subform called convenati subform.

In my table I have these fields:

  • conid
  • location
  • surname
  • firstname
  • middlename
  • phone
  • email
  • dob
  • sex
  • mstatus
  • status
  • violated
  • data.

help me with it


1 Answer: 

Some of your problems:

  • you're missing a space after the ).

  • If any of the fields in the table are field type Date/Time, then there SQL should have # around the date, instead of '

  • Double-check the number of fields you list in the first half of the query, with the data you're adding (in the last half of the query). One is missing somewhere.

  • Missing a . after a me..

  • Put Option Explicit at the top of your modules (first line) to help find & fix undeclared/mishandled variables, objects, etc.

  • assume you don't have it all squished together like it appears in your example, but if you do, remove all _'s except at the end of the line.

    CurrentDb.Execute "INSERT INTO convenati(conid, location, surname, firstname, " & _
      "middlename, phone, email, dob, sex, mstatus, status, violated, date) " & _
      "VALUES(" & Me.txtid & ", '" & Me.txtlocation & "','" & Me.txtsurname & _
      "','" & Me.txtmiddlename & "','" & Me.txtfirstname & "','" & Me.txtphone & _
      "','" & Me.txtemail & "','" & Me.txtdate & "','" & Me.cbogender & "','" & _
      Me.cbomstatus & "','" & Me.cboviolated & "','" & Me.txtdate & ") "
    

Also, is the last field you listed in your question date or data?

...the problem all these things have in common is lack of attention to detail. "Close" doesn't count in coding.

One way to troubleshoot problems with SQL being used in VBA is to test the actual SQL output of your statement, in an actual query.

Using the statement above as an example, change CurrentDb.Execute to Debug.Print, and make the next line say Stop. Run your code, and when it breaks at the Stop, hit Ctrl+G to open the Immediate Window, and take a look at the complete SQL.


If you still can't find a problem, copy and paste that output into a Blank Query (in SQL view) and try running the query. It it won't run there either, then you need to start removing parts to simplify the query, troubleshooting by the process of elimination.

I don't know what values each of your form controls hold, nor what data type is expected for each of the fields in the table, so I replaced them all with ___ and got:

INSERT INTO convenati (conid, location, surname, firstname, middlename, 
    phone, email, dob, sex, mstatus, status, violated, date) VALUES(___, 
    '___','___','___','___','___','___','___','___','___','___','___') 

More debugging tips here.