Error code 3346

J

jimphilly

I'm getting an error code 3346, "Number of query values and destination
fields are not the same" when I attempt to run the following code

strNUMBER = Me.Number.Value
strAY = "07/08"
strSQL = "INSERT INTO EMPLOYMENT ([Number], [ACAD_YR]) Values (""" &
strNumber & """),(""" & strAY & """)"
MsgBox strSQL
DoCmd.RunSQL strSQL

When the message box, displaying the SQL string, comes up the string looks
right. I'm baffled.

Thanks for the help
 
R

ruralguy via AccessMonster.com

Try leaving out the inner parens.
strSQL = "INSERT INTO EMPLOYMENT ([Number], [ACAD_YR]) " & _
"Values (""" & strNumber & """,""" & strAY & """)"

I'm getting an error code 3346, "Number of query values and destination
fields are not the same" when I attempt to run the following code

strNUMBER = Me.Number.Value
strAY = "07/08"
strSQL = "INSERT INTO EMPLOYMENT ([Number], [ACAD_YR]) Values (""" &
strNumber & """),(""" & strAY & """)"
MsgBox strSQL
DoCmd.RunSQL strSQL

When the message box, displaying the SQL string, comes up the string looks
right. I'm baffled.

Thanks for the help
Click to expand...
 
M

Marshall Barton

jimphilly said:
I'm getting an error code 3346, "Number of query values and destination
fields are not the same" when I attempt to run the following code

strNUMBER = Me.Number.Value
strAY = "07/08"
strSQL = "INSERT INTO EMPLOYMENT ([Number], [ACAD_YR]) Values (""" &
strNumber & """),(""" & strAY & """)"
MsgBox strSQL
DoCmd.RunSQL strSQL
Click to expand...


You should only have one set of ( ) in the VALUES part.

Are you sure the Number field is a Text field?

Assuming Number is a numeric type field, try using:

strSQL = "INSERT INTO EMPLOYMENT ([Number], [ACAD_YR]) _
VALUES(" & strNumber & ",""" & strAY & """)"
 
B

boblarson

And in addition to what Allan and Marshall have both said, you really should
not use an Access Reserved Word for a field (or object) name. Number is an
Access Reserved word and so you really should rename that field or it can
cause unintended problems at various times.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________
 
J

jimphilly

Thanks for the response. Deleting the second set of () worked.

However, I have a second question. Immediately after this code:

strNBR = Me.Number.Value
strAY = "07/08"
strSQL = "INSERT INTO EMPLOYMENT ([Nbr], [ACAD_YR]) Values (""" &
strNBR & """),(""" & strAY & """)"
MsgBox strSQL
DoCmd.RunSQL strSQL

I'm attempting to run this code

strSQL2 = "SELECT [SNbr],[FILE STATUS] FROM [Participant Table Data] WHERE
[Participant Table Data].[SNbr] = (""" & strNBR & """)"
DoCmd.RunSQL strSQL2

I'm getting an error message informing me that "A RunSQL action requires an
argument consisting of an SQL statement"

I can't figure out what is wrong with this statement!!

Thanks again

boblarson said:
And in addition to what Allan and Marshall have both said, you really should
not use an Access Reserved Word for a field (or object) name. Number is an
Access Reserved word and so you really should rename that field or it can
cause unintended problems at various times.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


jimphilly said:
I'm getting an error code 3346, "Number of query values and destination
fields are not the same" when I attempt to run the following code

strNUMBER = Me.Number.Value
strAY = "07/08"
strSQL = "INSERT INTO EMPLOYMENT ([Number], [ACAD_YR]) Values (""" &
strNumber & """),(""" & strAY & """)"
MsgBox strSQL
DoCmd.RunSQL strSQL

When the message box, displaying the SQL string, comes up the string looks
right. I'm baffled.

Thanks for the help
Click to expand...
Click to expand...
 
R

ruralguy via AccessMonster.com

Did you mean:
strSQL2 = "SELECT [Participant Table Data],[FILE STATUS] FROM [Participant
Table Data] WHERE [Participant Table Data].[SNbr] = (""" & strNBR & """)"

Thanks for the response. Deleting the second set of () worked.

However, I have a second question. Immediately after this code:

strNBR = Me.Number.Value
strAY = "07/08"
strSQL = "INSERT INTO EMPLOYMENT ([Nbr], [ACAD_YR]) Values (""" &
strNBR & """),(""" & strAY & """)"
MsgBox strSQL
DoCmd.RunSQL strSQL

I'm attempting to run this code

strSQL2 = "SELECT [SNbr],[FILE STATUS] FROM [Participant Table Data] WHERE
[Participant Table Data].[SNbr] = (""" & strNBR & """)"
DoCmd.RunSQL strSQL2

I'm getting an error message informing me that "A RunSQL action requires an
argument consisting of an SQL statement"

I can't figure out what is wrong with this statement!!

Thanks again
And in addition to what Allan and Marshall have both said, you really should
not use an Access Reserved Word for a field (or object) name. Number is an
Click to expand...
[quoted text clipped - 15 lines]
Click to expand...
 
J

jimphilly

I would like both the SNbr field and the file status fields to appear in my
query and then update my form with the file status field value.

Thanks

jimphilly said:
Thanks for the response. Deleting the second set of () worked.

However, I have a second question. Immediately after this code:

strNBR = Me.Number.Value
strAY = "07/08"
strSQL = "INSERT INTO EMPLOYMENT ([Nbr], [ACAD_YR]) Values (""" &
strNBR & """),(""" & strAY & """)"
MsgBox strSQL
DoCmd.RunSQL strSQL

I'm attempting to run this code

strSQL2 = "SELECT [SNbr],[FILE STATUS] FROM [Participant Table Data] WHERE
[Participant Table Data].[SNbr] = (""" & strNBR & """)"
DoCmd.RunSQL strSQL2

I'm getting an error message informing me that "A RunSQL action requires an
argument consisting of an SQL statement"

I can't figure out what is wrong with this statement!!

Thanks again

boblarson said:
And in addition to what Allan and Marshall have both said, you really should
not use an Access Reserved Word for a field (or object) name. Number is an
Access Reserved word and so you really should rename that field or it can
cause unintended problems at various times.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


jimphilly said:
I'm getting an error code 3346, "Number of query values and destination
fields are not the same" when I attempt to run the following code

strNUMBER = Me.Number.Value
strAY = "07/08"
strSQL = "INSERT INTO EMPLOYMENT ([Number], [ACAD_YR]) Values (""" &
strNumber & """),(""" & strAY & """)"
MsgBox strSQL
DoCmd.RunSQL strSQL

When the message box, displaying the SQL string, comes up the string looks
right. I'm baffled.

Thanks for the help
Click to expand...
Click to expand...
Click to expand...
 
B

boblarson

You can't run a select statement from DoCmd.RunSQL . It is for action
queries only (Append, Insert, Delete)

--

Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP
Free Access Resources at http://www.btabdevelopment.com


jimphilly said:
I would like both the SNbr field and the file status fields to appear in my
query and then update my form with the file status field value.

Thanks

jimphilly said:
Thanks for the response. Deleting the second set of () worked.

However, I have a second question. Immediately after this code:

strNBR = Me.Number.Value
strAY = "07/08"
strSQL = "INSERT INTO EMPLOYMENT ([Nbr], [ACAD_YR]) Values (""" &
strNBR & """),(""" & strAY & """)"
MsgBox strSQL
DoCmd.RunSQL strSQL

I'm attempting to run this code

strSQL2 = "SELECT [SNbr],[FILE STATUS] FROM [Participant Table Data]
WHERE
[Participant Table Data].[SNbr] = (""" & strNBR & """)"
DoCmd.RunSQL strSQL2

I'm getting an error message informing me that "A RunSQL action requires
an
argument consisting of an SQL statement"

I can't figure out what is wrong with this statement!!

Thanks again

boblarson said:
And in addition to what Allan and Marshall have both said, you really
should
not use an Access Reserved Word for a field (or object) name. Number
is an
Access Reserved word and so you really should rename that field or it
can
cause unintended problems at various times.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


:

I'm getting an error code 3346, "Number of query values and
destination
fields are not the same" when I attempt to run the following code

strNUMBER = Me.Number.Value
strAY = "07/08"
strSQL = "INSERT INTO EMPLOYMENT ([Number], [ACAD_YR]) Values (""" &
strNumber & """),(""" & strAY & """)"
MsgBox strSQL
DoCmd.RunSQL strSQL

When the message box, displaying the SQL string, comes up the string
looks
right. I'm baffled.

Thanks for the help
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working outlook via vba code with xlsx - error 0
Can't seem to retrieve primary key number of newly created record in VB 1
Error 2465; Can't Find Field 8
VBA code issue...Would love expert help. 5
Error in multiple select in a list box code 4
Using an Access form to Mass Update a table 7
Using a Form to Mass Update a Table 1
Code will work on one table and form but not on another? 19

Top