By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,254 Members | 2,386 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

Run-Time error '3464' CurrentDb.Execute

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

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "INSERT INTO WhatIfAwdPayoutTbl ( WhatIfPytDate, WhatIfPytUnits, WhatIfAwardID, WhatIfAwardDate, WhatIfAwardUnits, WhatIfEmployeeID, WhatIfForfeitedUnits )" & _
  2.                     "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
Jan 21 '14 #1

✓ answered by zmbd

WhatIfForfeitedUnits )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!WhatIfForfeitedUnits looks like it's returning either a zls or a null. I'd double check the recordset you are pulling from.

Share this Question
Share on Google+
13 Replies


zmbd
Expert Mod 5K+
P: 5,397
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:
Expand|Select|Wrap|Line Numbers
  1. '----code omitted----
  2.    DIM strSQL as string
  3. '
  4.    strSQL = "INSERT INTO " & _
  5.       "WhatIfAwdPayoutTbl ( WhatIfPytDate, WhatIfPytUnits" & _
  6.       ", WhatIfAwardID, WhatIfAwardDate" & _
  7.       ", WhatIfAwardUnits, WhatIfEmployeeID" & _
  8.       ", WhatIfForfeitedUnits )" & _
  9.       "VALUES (#" & FirstPayoutDate & "#, " & _
  10.       rs!WhatIfPytUnits & ", " & rs!WhatIfAwardID & _
  11.       ", #" & rs!WhatIfAwardDate & "#, " & _
  12.       rs!WhatIfAwardUnits & _
  13.       ", " & rs!WhatIfEmployeeID & ", " & _
  14.       rs!WhatIfForfeitedUnits & ")"
  15. '
  16. 'now you can insert a debug print here for troubleshooting
  17. ' - press <ctrl><g> to open the immediate window
  18. ' - you can now cut and paste this information for review!
  19. '
  20.    debug.print "Your criteria = " & strSQL
  21. '
  22. 'now use the string in your code:
  23. 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:
Expand|Select|Wrap|Line Numbers
  1. '---- code omitted ----
  2. DIM cdb as DAO.Database
  3. Set cdb = CurrentDb
Every time you use CurrentDb.Execute 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.
Jan 21 '14 #2

100+
P: 294
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!
Jan 21 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
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.
Jan 21 '14 #4

100+
P: 294
Ah, gotcha. So if it is a type mismatch error, that means the formatting for my fields/variables are not equivalent?
Jan 21 '14 #5

zmbd
Expert Mod 5K+
P: 5,397
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.
Jan 21 '14 #6

100+
P: 294
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!WhatIfForfeitedUnits & " is nothing, so the immediate window returns:

Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 21 '14 #7

zmbd
Expert Mod 5K+
P: 5,397
WhatIfForfeitedUnits )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!WhatIfForfeitedUnits looks like it's returning either a zls or a null. I'd double check the recordset you are pulling from.
Jan 21 '14 #8

100+
P: 294
It still gave me the Error '3134' and I fixed the spacing issue:

Expand|Select|Wrap|Line Numbers
  1. Your criteria = INSERT INTO WhatIfAwdPayoutTbl ( WhatIfPytDate, WhatIfPytUnits, WhatIfAwardID, WhatIfAwardDate, WhatIfAwardUnits, WhatIfEmployeeID, WhatIfForfeitedUnits ) VALUES (#1/1/2015#, 1, -115725515, #6/1/2006#, 5, 100700, )
  2.  
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
Expand|Select|Wrap|Line Numbers
  1. WhatIfForfeitedUnits
is not a required field, so null is allowed.
Jan 21 '14 #9

100+
P: 299
Please delete this post.
Jan 21 '14 #10

zmbd
Expert Mod 5K+
P: 5,397
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.
Jan 21 '14 #11

100+
P: 294
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
Expand|Select|Wrap|Line Numbers
  1. If IsNull ( WhatIfForfeitedUnits ) Then
  2. WhatIfForfeitedUnits = 0 End If 
and see if that takes care of the error, since I now know it isn't the spacing issue.
Thanks
Jan 22 '14 #12

zmbd
Expert Mod 5K+
P: 5,397
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.
Jan 22 '14 #13

100+
P: 294
Well, regardless I am grateful for your assistance. Thanks again!
Jan 22 '14 #14

Post your reply

Sign in to post your reply or Sign up for a free account.