Solved: SQL query

[BarkersIT]

Assuming that there is no error regarding the variable names or types, can someone tell me whats wrong with this statement?

query = "INSERT INTO [Assign Hardware] ([Base Unit ID],[Monitor ID],[Keyboard ID],[Printer ID]) VALUES (" & bid & "," & mid & "," & kid & "," & pid & ") WHERE [Employee ID] = " & eid & ";"

The error says "missing semi-colon", so I am guessing its a problem where I have tried to append the query string and the variables but I cant see it.

Any ideas

[moa]

If your ids are strings you may need to use single quotes around them:

query = "INSERT INTO [Assign Hardware] ([Base Unit ID],[Monitor ID],[Keyboard ID],[Printer ID]) VALUES ('" & bid & "','" & mid & "','" & kid & "','" & pid & "') WHERE [Employee ID] = '" & eid & "';"

[BarkersIT]

Hello Glen,

The 4 variables to be inserted are Strings but the variable in the WHERE statement is an Integer. So I tried:

query = "INSERT INTO [Assign Hardware] ([Base Unit ID],[Monitor ID],[Keyboard ID],[Printer ID]) VALUES ('" & bid & "','" & mid & "','" & kid & "','" & pid & "') WHERE [Employee ID] = " & eid & ";"

Alas, the same error.

[moa]

Maybe try CDbl(eid) in your SQL statement.

Little rusty with sql BTW.

[BarkersIT]

Not a problem, all help gratefully received.

[BarkersIT]

Tried CLng, CDbl, CInt. Still not having it. Pretty much all out of ideas.

[moa]

likewise.

Try putting a semicolon before VALUES.

otherwise you'll have to wait for an expert to answer...

[BarkersIT]

From what I gather reading on the net you cant have a WHERE clause with an INSERT clause unless there is also a SELECT clause so that the WHERE check is done on the information from the SELECT clause and not say a row index in the table I want to insert the data into. If that makes sense?

I have just added an extra column in my table for an arbitrary auto number and then used the following:

query = "INSERT INTO [Assign Hardware] ([Employee ID],[Base Unit ID],[Monitor ID],[Keyboard ID],[Printer ID]) VALUES (" & eid & ",'" & bid & "','" & mid & "','" & kid & "','" & pid & "');"

If nothing else you pointed out the need for speech marks around the String variables. Thanks for having a look.

[moa]

Ah, I see.

Could you not have have an imbedded select in the INSERT statement so you are inserting into just the selected records? Not sure how that would work though. Again, really rusty and I don't even have Access at work to test this stuff...

[BarkersIT]

I'm no SQL maestro either. I guess some clever individual could do it but its beyond me. My chewing gum fix will have to do for now. Now I get the fun task of ensuring there are no duplicate entries. Hooray.

[OBP]

BarkersIT, if you want an SQL man, private mail "Jimmy the hand" to have a look at it for you.
As you know I prefer to work with the Queries themselves.

[stanl]

I believe the rule is

1. If the target table does not exist, or you wish to replace it use
SELECT ( fieldlist ) INTO [target] FROM [source] WHERE [source].field='some value';

2. If the target exists and you are appending rows
INSERT INTO [target] SELECT (fieldlist ) FROM [source] WHERE [source].field='some value';

with #2 you have some responsibility with duplicate rows. Stan

[OBP]

Apologies Stanl.
BarkersIT, or ask Stanl of course.

[geekgirlau]

The WHERE refers to the SELECT object; in this case you are inserting values, so there is no table or query to test those values against.

If the Employee ID field is an autonumber, you shouldn't have to include that in your query as it will be automatically populated. However if you need to test that the Employee ID doesn't already exist in the table, you should test for this first:

[vba]
If IsNull(DLookup("[Employee ID]", "Assign Hardware", "[Employee ID]=" & eid)) Then
Query = "INSERT INTO [Assign Hardware] " & _
"([Employee ID],[Base Unit ID],[Monitor ID],[Keyboard ID]," & _
"[Printer ID]) " & _
"VALUES (" & eid & ",'" & bid & "','" & Mid & "','" & kid & _
"','" & PID & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL Query
DoCmd.SetWarnings True
End If
[/vba]