Syntax Error Missing Operator but Where?

[kabradley]

Hey guys, I'm using an embedded sql statement tied to a command button to append records in a certain table. The code for the sql statement is as follows.

'Define sql string
strSQL = "INSERT INTO LPID (LPName, LPUnitPrice, VendorFamily, LPCatagoryID, LPStructure)" & _
"SELECT LPID.LPName, LPID.LPUnitPrice, LPID.VendorFamily, LPID.LPCatagoryID, LPID.LPStructure" & _
"FROM LPID" & _
"WHERE (([LPID.LPName] = [Forms]![Investment Manager]![subfrmVendor]!LPName)" & _
"([LPID.LPUnitPrice] = [Forms]![Investment Manager]![subfrmVendor]!LPUnitPrice)" & _
"([LPID.VendorFamily] = [Forms]![Investment Manager]![subfrmVendor]!VendorFamily)" & _
"([LPID.LPCatagoryID] = [Forms]![Investment Manager]![subfrmVendor]!LPCatagoryID)" & _
"([LPID.LPStructure] = [Forms]![Investment Manager]![subfrmVendor]!LPStructure));"
 
'Run Sql
DoCmd.RunSQL strSQL
 

It gives me an error saying syntax error(missing operator), but I can't seem to find where the error is. Any help would be appreciated.

[Rabbit]

You need spaces. Also, you may need to change [Forms]![Main Form]![Sub Form]![Control Name] to [Forms]![Main Form]![Sub Form]![Form]![Control Name] but I'm not entirely sure about this.

'Define sql string
strSQL = "INSERT INTO LPID (LPName, LPUnitPrice, VendorFamily, LPCatagoryID, LPStructure)" & _
" SELECT LPID.LPName, LPID.LPUnitPrice, LPID.VendorFamily, LPID.LPCatagoryID, LPID.LPStructure" & _
" FROM LPID" & _
" WHERE (([LPID.LPName] = [Forms]![Investment Manager]![subfrmVendor]!LPName)" & _
" ([LPID.LPUnitPrice] = [Forms]![Investment Manager]![subfrmVendor]!LPUnitPrice)" & _
" ([LPID.VendorFamily] = [Forms]![Investment Manager]![subfrmVendor]!VendorFamily)" & _
" ([LPID.LPCatagoryID] = [Forms]![Investment Manager]![subfrmVendor]!LPCatagoryID)" & _
" ([LPID.LPStructure] = [Forms]![Investment Manager]![subfrmVendor]!LPStructure));"
 
'Run Sql
DoCmd.RunSQL strSQL
 

[kabradley]

Thanks rabbit, that along with adding AND's on all of the 'WHERE' statements fixed the error. Now though, It doesn't give an error, but it also does not 'see' any data to append to the table LPID. The whole purpose of this sql statement was because I have a subform that is to update a table. That subform though was not updating the table even though it's control was set to the proper control for that table. I also was worried about users entering data and not really wanting it. This way, the sql statement will only run after the submit button is clicked. This should cut down on the amount of bad data tremondously.

Any other help you can give me on this problem would be greatly appreciated.

[Rabbit]

I would try to get this append query working in a regular query and then just copy it's SQL from the SQL view.

[kabradley]

Ok Rabbit, I've decided to go a different route, but even this new route I'm having trouble with. I've decided against using an append query and instead have the information entered into the table through the subform. The subform has a few places to enter data that go directly into the table. If you close the form with the 'x' and then check the corrosponding table it works fine. For this application though, this does not work. At times the user will need to enter several "sheets" of data at once. So, what I have done is added a 'submit' button. The purpose of the button is so that when the user is finished with that "sheet" of data they hit submit. This should then produce a msgbox that says "your data has been submitted successfully". And then it should wipe the two combo boxes and the 4 text boxes so that it is waiting for a new set of data. I've gotten the msgbox to work *although it pop's up unconditionally, meaning whether or not the data was actually added it pops up and says that it has added it* I can also wipe all of the text boxes. I cannot however 'wipe' the two combo boxes. That though is not the biggest problem. The biggest problem is that after hitting the button and all this taking place if I close the form and then check my table, nothing has been added! To me this seems weird especially since the data that has been entered as been assigned an autonumber value.
I really don't know what's wrong with it or why it is not adding the data to the table. If this explanation is to convuleted for you to understand please ask any question that you need to. Thank you again for your help.

[Rabbit]

The problem is that when you clear the data, you're actually clearing the record which then auto saves to the table. Rather than clearing the fields manually, have the form go to a new record and that should take care of everything.

[kabradley]

I did not realize that you could 'force a new record' via VBA. So, if I'm understanding you right what I would do is take this VBA command and tie it to the submit button? I'll have to go look up the command and try that. Also, is there any way for that msg box to popup conditionally. I.e. check to make sure that the record did in fact get updated in the table? Thank you again!

[kabradley]

Oh wow!! Ok, I just tried the gotorecord method and it worked splendidly. Thank you for your help Rabbit.

If there isn't a way to check for the new record to make sure that it did submit before printing the msgbox just let me know. Thank you again!

[Rabbit]

If the form is bound to the table then Access takes care of all updates automatically. As long as you get rid of the code that's clearing out the fields when you hit submit, whatever's typed into the form will be saved to the table automatically. Actually, the data gets saved before you ever hit the submit button. Most of the time, the submit button doesn't actually tell the form to save the data to a table because the table is already bound to the form.

[kabradley]

If the form is bound to the table then Access takes care of all updates automatically. As long as you get rid of the code that's clearing out the fields when you hit submit, whatever's typed into the form will be saved to the table automatically. Actually, the data gets saved before you ever hit the submit button. Most of the time, the submit button doesn't actually tell the form to save the data to a table because the table is already bound to the form.

Alright, that makes sense and is actually what I assumed would be happening. It's just weird I guess to run across things like that and then look back at other programs that you've used and wonder / realize that a lot of the things in the gui are just superfluous and aren't really 'reporting' anything. Thanks again for your help rabbit!

[Rabbit]

Not a problem, come back if you have any more questions.