473,322 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Run-Time error '3464' CurrentDb.Execute

294 256MB
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.

13 3490
zmbd
5,501 Expert Mod 4TB
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
mcupito
294 256MB
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
5,501 Expert Mod 4TB
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
mcupito
294 256MB
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
5,501 Expert Mod 4TB
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
mcupito
294 256MB
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
5,501 Expert Mod 4TB
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
mcupito
294 256MB
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
Luk3r
300 256MB
Please delete this post.
Jan 21 '14 #10
zmbd
5,501 Expert Mod 4TB
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
mcupito
294 256MB
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
5,501 Expert Mod 4TB
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
mcupito
294 256MB
Well, regardless I am grateful for your assistance. Thanks again!
Jan 22 '14 #14

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

Similar topics

1
by: JMCN | last post by:
hello i receive a runtime error '2465' whenever i run my module in access 97. it says 'Run-time error '2465' OOB Reports can't find the field "DuplicatePayments' referred to in your...
1
by: teddysnips | last post by:
Scenaria - Access 2000 FE - 2 clients Access 2000 BE - on a network server. Tiny little application, all tried and tested code. Worked fine until this morning, when users began receiving the...
1
by: Will | last post by:
In the following code I am attempting to open a query and edit the lastMaintenance field for a particular equipment number. My code looks like this: Dim db As Database Dim rs As Recordset Dim...
1
by: GSteven | last post by:
I have a statemnt which says: CurrentDb.Execute strSQL, dbFailOnError where strSQL is a create query. Is there an overwrite option for the execute method so thath I don't get the 'file already...
4
by: Richard Hollenbeck | last post by:
I thought I was very specific in this SQL request. There is a form open with a selected record (and a corresponding "lngRecipeID" on that form. The table also has a field called "lngRecipeID". ...
1
MitchR
by: MitchR | last post by:
Hello Folks; I have run myself into a hole I cannot get of and need some assistance. I have a Form "Frm_ATandT_Activation_Center" that has a sub form "Frm_ATandT_Activation"....
5
by: cujofreak | last post by:
Hello Forum, When compiling my code I receive a runtime error. I am fairly new to vb coding, let alone interfacing it with access. I have attached my code to the thread hoping someone might...
1
by: Lauren Dobson | last post by:
This database was working days ago, outputting a vocabulary list into a word document. Any idea why I'm getting runtime error 3010? I just switched from Windows XP to Windows 7 but I'm still using...
4
by: shalskedar | last post by:
In my DB(Ms Access 2003)I want to delete a record from 1 table which is linked to another table. For ex- There are 2 tables "MasterType" which is the Master table & another table as "Sash DO"...
2
by: Higgs | last post by:
Hello, I have a function "ParseFileName" which removes the ".txt" in the file name which is stored in the variable varItem: Public Function ParseFileName(varItem As String) As String x =...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.