Adding Record from Form to Table not Working

[MHamid]

Hello,

I am getting an error message when I click on "Add Record" button. I am trying to add data that was entered in a form into a table.

Getting an error message with the code below. The error I am getting is "Number of query values and destination fields are not the same".

What am I doing wrong?

Private Sub AddRecord_Click()

CurrentDb.Execute "INSERT INTO MasterThroughput( [SID, Date, Case #, Entity Count, Referenced Entity #, Regional Assist?, Vendor Assist?, Fully Referenced?, LOB, Comments] )" & _
                " VALUES (" & Me.SID & ", " & Me.Date & ", " & Me.Case & ", " & Me.EntityCount & ", " & Me.ReferencedEntity & ", " & Me.RegionalAssist & ", " & Me.VendorAssist & ", " & Me.FullyReferenced & ", " & Me.LOB & ", " & Me.Comments & ")"
               
 
End Sub

 

[Russell Hauf]

Any reason why you don't want to build a form based on the table? Then you can have a new record button....However, if you want to do it like you've posted, you'll need to have brackets around individual fields in the INSERT INTO statement. Something like this:

"INSERT INTO MasterThroughput( [SID, [Date], [Case #], [Entity Count], [Referenced Entity #], [Regional Assist?], [Vendor Assist?], [Fully Referenced?], LOB, Comments )"

It isn't a great idea to name a field "Date," but if you do, you need the brackets around it (note that you may experience troubles with this field if you aren't really careful). Any fields with spaces or special characters in them need brackets.
Regarding the VALUES statement, you may need some pound signs (or hashtags - #) around Me.Date, or you may end up with odd results for that. Likewise you may need quotes around text values that you're inserting.

Have fun,

 

[MHamid]

Hello,

I made changes to the code and now I am getting run-time error '3134': Syntax error in INSERT TO statement.

Private Sub AddRecord_Click()

CurrentDb.Execute "INSERT INTO MasterThroughput( [SID], [Date], [Case #], [Entity Count], [Referenced Entity #], [Regional Assist?], [Vendor Assist?], [Fully Referenced?], [LOB], [Comments] )" & _
                " VALUES (" & Me.SID & ", # & Me.Date & #, " & Me.Case & ", " & Me.EntityCount & ", " & Me.ReferencedEntity & ", " & Me.RegionalAssist & ", " & Me.VendorAssist & ", " & Me.FullyReferenced & ", " & Me.LOB & ", " & Me.Comments & ")"
               
 
End Sub

 

[MHamid]

Hello,

I was able to get my code to work properly.

CurrentDb.Execute "INSERT INTO MasterThroughput([SID], [Date], [Case #], [Entity Count], [Referenced Entity #], [Regional Assist?], [Vendor Assist?], [Fully Referenced?], [LOB], [Comments])" & _
                "VALUES ('" & Me.SID & "', '" & Me.Date & "', '" & Me.Case & "', '" & Me.EntityCount & "', '" & Me.ReferencedEntity & "', '" & Me.RegionalAssist & "', '" & Me.VendorAssist & "', '" & Me.FullyReferenced & "', '" & Me.LOB & "', '" & Me.Comments & "');", dbFailOnError

Thank you for your input.

 

[Russell Hauf]

Great - was just about to reply with the same. Glad you got it working!

 

[MHamid]

Hello,

So I am having a similar issue with the code.
I just added one more column to add data to my table.
The issue I am having is that if I leave the End Date blank, I will get the runtime error 3464: Data type mismatch in criteria expression.
If I enter a date, then the code works. How can I get the code to work if the End Date is blank?

CurrentDb.Execute "INSERT INTO MasterThroughput([SID], [Start Date], [End Date], [CaseNo], [Entity Count], [Referenced Entity #], [Regional Assist?], [Vendor Assist?], [Fully Referenced?], [LOB], [Comments])" & _
                "VALUES ('" & Me.SID & "', '" & Me.StartDate & "', '" & Me.EndDate & "', '" & Me.Case & "', '" & Me.EntityCount & "', '" & Me.ReferencedEntity & "', '" & Me.RegionalAssist & "', '" & Me.VendorAssist & "', '" & Me.FullyReferenced & "', '" & Me.LOB & "', '" & Me.Comments & "');", dbFailOnError

 

[Russell Hauf]

If you have dates that are not entered, you are going to have to do some manipulation to your string so that you can pass "Null". So maybe create a variable and do something like this:

If IsNull(Me.EndDate) Then
    strEndDate = "Null"
Else
    strEndDate = "'" & Me.EndDate & "'"
End If

CurrentDb.Execute "INSERT INTO MasterThroughput([SID], [Start Date], [End Date], [CaseNo], [Entity Count], [Referenced Entity #], [Regional Assist?], [Vendor Assist?], [Fully Referenced?], [LOB], [Comments])" & _
                "VALUES ('" & Me.SID & "', '" & Me.StartDate & "', " & strEndDate & ", '" & Me.Case & "', '" & Me.EntityCount & "', '" & Me.ReferencedEntity & "', '" & Me.RegionalAssist & "', '" & Me.VendorAssist & "', '" & Me.FullyReferenced & "', '" & Me.LOB & "', '" & Me.Comments & "');", dbFailOnError

Note that this is just an example - you could do the same thing with Start Date. So I've removed the single quotes around the value for End Date, so that we just plug in strEndDate - because if there is no End Date entered, you want to pass Null (without quotes) to your Insert Into statement.

Have fun,

 

[MHamid]

Hello,

I was getting some error messages, but I got the code to work.
Thank you so much. I didn't even think about it like you described.


Thank you,
Miriam