Run-Time Error 3067

[crislbr1]

Hello,

I'm not sure if this SQL is possible, but this is what I need to do. I have a form with two variables I need to use. With these variables I need to insert them along with values from fields in a table, based on one of the variables. Now, the table I need to insert into does not relate to the table I'm pulling records from. The following code shows what I'm trying to do:

sqlString = "INSERT INTO tbl_Str_and_Signal_Temp VALUES([tbl_Engineering_Signal_List].SIG_ID,[tbl_Engineering_Signal_List].NAME, '" & strNumber & "', 'NO') WHERE Instr([tbl_Engineering_Signal_List].SIG_ID, '" & systemString & "')"

DoCmd.RunSQL (sqlString)

This is where I get the '3067' run-time error. Also, there are multiple records with the criteria of 'systemString'. How should I go about this?

Cheers,
Brendan

 

[MarkK]

Hi and welcome to the forum. Have you tried getting this insert query working the query design grid first? I usually do that as a first step and then I copy the SQL to code.

 

[boblarson]

You need to specify the fields:

sqlString = "INSERT INTO tbl_Str_and_Signal_Temp (ID, [Name], OtherFieldName) VALUES([tbl_Engineering_Signal_List].SIG_ID,[tbl_Engineering_Signal_List].[NAME], '" & strNumber & "', 'NO') WHERE Instr([tbl_Engineering_Signal_List].SIG_ID, '" & systemString & "')"

But you also have a field that is called NAME and that is BAD, BAD, BAD, BAD! You need to change it ASAP as NAME is an Access Reserved Word and one of the WORST to use as a field name or object name. Most Access Reserved Words you can get away with using, as long as you use brackets, and it won't cause too many problems, but NAME is one where it can be a problem even with bracketing. So, CHANGE IT ASAP!

 

[crislbr1]

boblarson,

I tried what you suggested, but I still get the same run-time error:

sqlString = "INSERT INTO tbl_Str_and_Signal_Temp (SIG_ID, SIG_NAME, STR_NUMBER, CHECK_BOX) VALUES([tbl_Engineering_Signal_List].SIG_ID,[tbl_Engineering_Signal_List].[NAME], '" & strNumber & "', 'NO') WHERE Instr([tbl_Engineering_Signal_List].SIG_ID, 'CPA')"

I appreciate the field naming tip. Unfortunately, I have no control over the naming convention of that specific table. It's given to use from an outside source and I cannot make any changes to it.

 

[boblarson]

Is STR_NUMBER in your table actually TEXT, or is it a number? Also is CHECK_BOX a text field or a Yes/No Field?

 

[crislbr1]

STR_NUMBER is Text
CHECK_BOX is Yes/No

 

[boblarson]

Then try this:

sqlString = "INSERT INTO tbl_Str_and_Signal_Temp (SIG_ID, SIG_NAME, STR_NUMBER, CHECK_BOX) VALUES([tbl_Engineering_Signal_List].SIG_ID,[tbl_Engineering_Signal_List].[NAME], '" & strNumber & "', [COLOR="red"]NO[/COLOR]) WHERE Instr([tbl_Engineering_Signal_List].SIG_ID, 'CPA')"

Or possibly

sqlString = "INSERT INTO tbl_Str_and_Signal_Temp (SIG_ID, SIG_NAME, STR_NUMBER, CHECK_BOX) VALUES([tbl_Engineering_Signal_List].SIG_ID,[tbl_Engineering_Signal_List].[NAME], '" & strNumber & "', [COLOR="Red"]FALSE[/COLOR]) WHERE Instr([tbl_Engineering_Signal_List].SIG_ID, 'CPA')"

 

[crislbr1]

Still no joy. Just keeps giving the same error.

 

[MarkK]

When I paste your SQL into my design grid I get a "Query input must contain at least one table or query" error. Maybe you need to replace VALUES with SELECT if you are drawing data from a table.