Syntax error (missing operator)

[BSJoJaMAx4]

Hi

I have two tables set up and am trying to transfer a record between two. I understand that the best method is to maintain one table and use fields to make the record appear in either based on the field but at the moment need to maintain the two until I have more time to merge etc.



I am receiving the following error:
Syntax error (missing operator) in query expression 'Cardsmaintainedbyfacilities.33CAccessCard'.

Private Sub Command151_Click()On Error GoTo Err_Handler
 
Dim strSQL As String
strSQL = "INSERT INTO ExitingStaffData (ExitingStaffID, ExitingStaff , SupervisorDetails , ExecAccessCard , IDAccessCard , 33CAccessCard )"
strSQL = strSQL + " SELECT Cardsmaintainedbyfacilities.id, Cardsmaintainedbyfacilities.ExitingStaff , Cardsmaintainedbyfacilities.SupervisorDetails , Cardsmaintainedbyfacilities.ExecAccessCard , Cardsmaintainedbyfacilities.IDCard , Cardsmaintainedbyfacilities.33CAccessCard "
'strSQL = strSQL + " WHERE [NANW DC Spreadsheet].[d_DC]= ')" & Me.[d_DC] & "'"
 
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
MsgBox "Record has been transferred"
Me.Command151.Enabled = False
Me.Recordhasbeentransferred = True
Me.Card_returned.Locked = True
 
Err_Exit:
Exit Sub
Err_Handler:
   MsgBox Err.Description
   Resume Err_Exit
End Sub

Works fine for all other lines (especially when I delete the line from the code). Just gets stuck on that line. I have checked both tables and the table and all associated data match - its a yes/no data type (same as Execaccesscard and IDCard). Just that one line crashes.

[CJ_London]

Just gets stuck on that line

what line would that be?

You seem to be missing a FROM component in your sqlstr and you should use & rather than +

I would also comment out your setwarnings lines until you have the problem resolved

[BSJoJaMAx4]

Hi Ajax

Sorry but trying to tailer code found in another post. Happy to take suggestions on how to improve

When I remove reference to field (in bold) in the following line:

strSQL = strSQL & " SELECT Cardsmaintainedbyfacilities.id, Cardsmaintainedbyfacilities.ExitingStaff , Cardsmaintainedbyfacilities.SupervisorDetails , Cardsmaintainedbyfacilities.ExecAccessCard , Cardsmaintainedbyfacilities.IDCard , Cardsmaintainedbyfacilities.33CAccessCard "

it runs and transfers the record to the other table. When I leave the field in the code it fails and the syntax error message appears. The other fields:
Cardsmaintainedbyfacilities.ExecAccessCard , Cardsmaintainedbyfacilities.IDCard

[CJ_London]

I'm surprised it does since as previously advised, your code is missing the FROM part

and as previously asked - which line?

and have you removed setwarnings?

[BSJoJaMAx4]

So I think I have updated the code to what you are saying:

Added FROM (hopefully in the right spot)
Removed the setwarnings (Am I doing that right by adding ' to the front of the line?)

Private Sub Command151_Click()On Error GoTo Err_Handler
 
Dim strSQL As String
strSQL = "INSERT INTO ExitingStaffData (ExitingStaffID, ExitingStaff , SupervisorDetails , ExecAccessCard , IDAccessCard , 33CAccessCard )"
strSQL = strSQL & " SELECT id, ExitingStaff , SupervisorDetails , ExecAccessCard , IDCard , 33CAccessCard FROM Cardsmaintainedbyfacilities"
 
'DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
'DoCmd.SetWarnings True
MsgBox "Record has been transferred"
Me.Command151.Enabled = False
Me.Recordhasbeentransferred = True
Me.Card_returned.Locked = True
 
Err_Exit:
Exit Sub
Err_Handler:
   MsgBox Err.Description
   Resume Err_Exit
End Sub

But I am still receiving the syntax error message in Syntax error (missing operator) in query expression '33CAccessCard'.

If I change the code to (ie remove reference to 33CAccessCard) code runs and the record data appears in the other table:

Private Sub Command151_Click()On Error GoTo Err_Handler
 
Dim strSQL As String
strSQL = "INSERT INTO ExitingStaffData (ExitingStaffID, ExitingStaff , SupervisorDetails , ExecAccessCard , IDAccessCard )"
strSQL = strSQL & " SELECT id, ExitingStaff , SupervisorDetails , ExecAccessCard , IDCard FROM Cardsmaintainedbyfacilities"
 
'DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
'DoCmd.SetWarnings True
MsgBox "Record has been transferred"
Me.Command151.Enabled = False
Me.Recordhasbeentransferred = True
Me.Card_returned.Locked = True
 
Err_Exit:
Exit Sub
Err_Handler:
   MsgBox Err.Description
   Resume Err_Exit
End Sub

Just a question - is it because I have numbers in the descriptor? Its the only thing I can think of thats different??

[CJ_London]

Am I doing that right by adding ' to the front of the line?)

yes

for the 3rd time - and as previously asked - which line?

is it because I have numbers in the descriptor?

possibly, try putting square brackets around them - [33CAccessCard]

I suggest after the 'strSQL = strSQL & " SELECT id,...' line, put

dubug.print strSQL

when the code runs is will display the result in the vba immediate window.

Copy and paste this into the query window and try to run it and see what happens - perhaps 33CAccessCard is spelt differently in your table