Run-Time error '3464' CurrentDb.Execute

[mcupito]

I am not sure what's wrong with my syntax for my INSERT INTO query. Does anyone see anything right off the bat?

CurrentDb.Execute "INSERT INTO WhatIfAwdPayoutTbl ( WhatIfPytDate, WhatIfPytUnits, WhatIfAwardID, WhatIfAwardDate, WhatIfAwardUnits, WhatIfEmployeeID, WhatIfForfeitedUnits )" & _
                    "VALUES (#" & FirstPayoutDate & "#, " & rs!WhatIfPytUnits & ", " & rs!WhatIfAwardID & ", #" & rs!WhatIfAwardDate & "#, " & rs!WhatIfAwardUnits & ", " & rs!WhatIfEmployeeID & ", " & rs!WhatIfForfeitedUnits & ")", dbFailOnError

I am not that great with SQL but I don't see anything wrong.
Just to attempt to fix it, I did put ' ' around the double quotes for each entry, which still did not fix it.

Thanks

[zmbd]

1) When reporting an error please:
Provide the Version of Office/Access you are using.
The EXACT title shown, if any, in the error message
The EXACT text as given in the error message
The EXACT number as given in the error message.
The numerics and text are recycled between versions, messages, and calling procedure.

2) If this is the typemismatch error then one of the values are are enteringing into the fields is not the correct type cast for the field.

3) The string theory:
You've stumbled upon one of my pet peeves by building the criteria string within the command - and it's not your fault because that's how a majority of examples show how to use the command.
Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.
So to use your code:

'----code omitted----
   DIM strSQL as string
'
   strSQL = "INSERT INTO " & _
      "WhatIfAwdPayoutTbl ( WhatIfPytDate, WhatIfPytUnits" & _
      ", WhatIfAwardID, WhatIfAwardDate" & _
      ", WhatIfAwardUnits, WhatIfEmployeeID" & _
      ", WhatIfForfeitedUnits )" & _
      "VALUES (#" & FirstPayoutDate & "#, " & _
      rs!WhatIfPytUnits & ", " & rs!WhatIfAwardID & _
      ", #" & rs!WhatIfAwardDate & "#, " & _
      rs!WhatIfAwardUnits & _
      ", " & rs!WhatIfEmployeeID & ", " & _
      rs!WhatIfForfeitedUnits & ")"
'
'now you can insert a debug print here for troubleshooting
' - press <ctrl><g> to open the immediate window
' - you can now cut and paste this information for review!
'
   debug.print "Your criteria = " & strSQL
'
'now use the string in your code:
CurrentDb.Execute strSQL, dbFailOnError

Do the <ctrl><g> and see what that string is returning and check your table design to verify that the values are attempting match the fields.

4) Another thing set a pointer to the database:

'---- code omitted ----
DIM cdb as DAO.Database
Set cdb = CurrentDb

Every time you use CurrentDb.Execu te you are opening a new pointer to the database which potentially ties up resources.

If you will make these little changes and post back the resolved string we can help you tweak the code.

[mcupito]

zmbd, thanks for the amount of time you put into your response. I am wondering if Database and DAO.Database are essentially the same, or if the DAO reference is important? I apologize for a lack of clarity in my OP, it is a type mismatch error. Luk3r,thanks again for your assistance!

[zmbd]

I am always specfic with the class typing of the database
DAO/ADO/etc... this avoids having the wrong syntax.
As most of the work I do will either be scaled to SQL-Server or stay within Access, I tend to use the DAO object model. If there is a chance that it might move to one of the other databases then I might look at the ADO model.

Using the generic DATABASE typecast leaves things to somthing else's control and that can cause you a lot of issues.

[mcupito]

Ah, gotcha. So if it is a type mismatch error, that means the formatting for my fields/variables are not equivalent?

[zmbd]

mcupito:

By pulling the SQL string out of the function method, you can debug.print the string to see what is actually being passed to the execute method.

If you can, you should go ahead and post that resolved string here so that we can go over it.

[mcupito]

Okay, I am getting an error: dbFailOnError = 128, Run-time error '3134' , Syntax error in INSERT INTO statement.

It looks like the value for " & rs!WhatIfForfei tedUnits & " is nothing, so the immediate window returns:

Your criteria = INSERT INTO WhatIfAwdPayoutTbl ( WhatIfPytDate, WhatIfPytUnits, WhatIfAwardID, WhatIfAwardDate, WhatIfAwardUnits, WhatIfEmployeeID, WhatIfForfeitedUnits )VALUES (#1/1/2015#, 1, -115725515, #6/1/2006#, 5, 100700, )

I'm not sure if that's throwing the error, though.

[zmbd]

WhatIfForfeited Units )VALUES (#1
Is missing a space between the closing parenthesis and "V". Fix the missing entry in the sql and insert the space there and that should take care of the err.num=3134

I also agree that rs!WhatIfForfei tedUnits looks like it's returning either a zls or a null. I'd double check the recordset you are pulling from.

[mcupito]

It still gave me the Error '3134' and I fixed the spacing issue:

Your criteria = INSERT INTO WhatIfAwdPayoutTbl ( WhatIfPytDate, WhatIfPytUnits, WhatIfAwardID, WhatIfAwardDate, WhatIfAwardUnits, WhatIfEmployeeID, WhatIfForfeitedUnits ) VALUES (#1/1/2015#, 1, -115725515, #6/1/2006#, 5, 100700, )

I checked the recordset, and the table definitions to ensure the date fields are dates, doubles are doubles etc. and everything checked out. I'm confused as to why I am getting the error. The

WhatIfForfeitedUnits

is not a required field, so null is allowed.

[Luk3r]

Please delete this post.

[zmbd]

MCUPITO:
You have to fix both issues that I stated in Post#8.
Any field mapped in the INSERT() must have a corresponding entry. Even if you omit the last comma you will have an error as the number of fields will not match the number of values... try it if you like.


Luk3r:
Recomending that someone enter random values for troubleshooting is not actually a recomended troubleshooting practice. Furthermore, what you are telling Mr.M to do, fix the missing entry, is basically what I have already said in post#8; however, you have provided some misleading suggestions that require clarification before they become misleading to others:

* The SQL string can (and should be) conditionally constructed such that any of the fields without a matching value could be excluded from the string. Method for such a construction would be offtopic to this thread.

* Removing the ending comma, will create an error as the number of fields will not match the number of values... try it if you like.

Luk3r, please, if you are going to offer help, please read the posts and provide correct information as I have already asked you to do twice.

[mcupito]

Thanks for your help, zmbd. I guess my questions aren't good enough to ask O.O from all of the animosity.

I'll try to set an

If IsNull ( WhatIfForfeitedUnits ) Then
WhatIfForfeitedUnits = 0 End If

and see if that takes care of the error, since I now know it isn't the spacing issue.
Thanks

[zmbd]

If you have issues with the string, start a new thread with the code and what you have and we'll get you back on track (^_^)
You might find the following insight article useful for dealing with the Null thing too What is Null? There's a nice discussion covering the propagation of nulls therein.

I will usually check for a Zero Length String when working with form controls; however, you're working with a recordset; thus, ZLSs may not apply.

I guess my questions aren't good enough to ask O.O from all of the animosity.

Your question is just fine.
As for the animosity, that puzzles me, I didn't see any.

[mcupito]

Well, regardless I am grateful for your assistance. Thanks again!