SQL statement not working

[LittleJess]

Hi,
I have the following piece of code in my database, It doesn't seem to be doing anything though. Any ideas why ?

Option Compare Database


Private Sub Combo0_AfterUpdate()
Dim strSQL As String
strSQL = "INSERT INTO tblLocation_People(LocationID)VALUES tblLocation(LocationID);"

End Sub


I am trying to get the auto number Identifier from the subform of Location into my linking table Location_People.
Anything would be greatly appreciated.

 

[LittleSmudge]

What you have done in the code is dim a string variable and then
set the string variable to a SQL string

What you haven't done is EXECUTE the SQL string


Try adding

DoCmd.RunSQL strSQL

before the End Sub




'ope-that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[LittleJess]

Hi LittleSmudge,
Thanks for your help. I have tried that and it comes up with a error

Run-Time error - 3134
Syntax error in INSERT INTO statement.

It seems that most things I try and do on this come up with a syntax error of some sort.
Thanks Again

 

[LittleSmudge]

Yes, loking at it again there is a noticable lack of spaces in the string

JET will look for spaces to work out where one bit starts and another ends.

JET will cope with 'extra' spaces but not with a shortage of spaces - so to make the string readeable to JET and people put space characters BOTH sides of each bracket and both ends of each key word


'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[DonQuichote]

INSERT INTO tblLocation_People(LocationID)VALUES tblLocation(LocationID);

What exactly is it what you want to do with this piece of code? If you want to insert a value, you should give a value, like:

INSERT INTO tblLocation_People(LocationID)VALUES (5);

If you want to insert something from a database, use a select clause:

INSERT INTO tblLocation_People(LocationID) SELECT ... FROM ... WHERE ...;

This form can cause more than one record to be appended.

Best regards.