Weird error with append query.

[Freakinchair]

Hey guys (again )

I've encountered another weird error when applying an append query through a button. Here's the code:

Private Sub cmdSaveChanges_Click()

On Error GoTo Err_cmdSaveChanges_Click
'Saves the new Inspector information into the information table.
'Adds the two references created by adding a new inspector into the XREF_FILE_INSPECTOR table.
'This is the case that the references are formed by adding a completely new inspector.
If (IsNull(cmbInspector) Or Me.cmbInspector = "") Then

'Saves Inspector information
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Adds references
DoCmd.RunSQL "INSERT INTO XREF_FILE_INSPECTOR" _
& "(FILE_NUMBER_CD, INSPECTOR_NUMBER_CD) VALUES " _
& "(" & [Forms]![GeneralFile].[txtGeneralFileNumber] & "," & [Forms]![AddInspector].[txtInspectorNumber] & ");"


Else
'This is the case where the user chooses an inspector from the provided combo box.
DoCmd.RunSQL "INSERT INTO XREF_FILE_INSPECTOR " _
& "(FILE_NUMBER_CD, INSPECTOR_NUMBER_CD) VALUES " _
& "(" & [Forms]![GeneralFile].[txtGeneralFileNumber] & "," & cmbInspector.Column(0) & " );"
End If
Exit_cmdSaveChanges_Click:
Exit Sub

Err_cmdSaveChanges_Click:
MsgBox Err.Description
Resume Exit_cmdSaveChanges_Click
End Sub

The problem is, when I run this I get a "Syntax error in INSERT INTO statement". This seems to only be the case for the first part of the IF statement, as the second part's append query works fine.

Help!

 

[Rabbie]

I think you need to check what is being passed in the first SQL string. Is the form [AddInspector] open when you are running the query and is txtInspectorNumber properly populated.

I would reccomend that you change your code as follows

Private Sub cmdSaveChanges_Click()
dim strSQL as string
On Error GoTo Err_cmdSaveChanges_Click
'Saves the new Inspector information into the information table.
'Adds the two references created by adding a new inspector into the XREF_FILE_INSPECTOR table.
'This is the case that the references are formed by adding a completely new inspector.
If (IsNull(cmbInspector) Or Me.cmbInspector = "") Then

'Saves Inspector information
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Adds references
strSQL =  "INSERT INTO XREF_FILE_INSPECTOR" _
& "(FILE_NUMBER_CD, INSPECTOR_NUMBER_CD) VALUES " _
& "(" & [Forms]![GeneralFile].[txtGeneralFileNumber] & "," & [Forms]![AddInspector].[txtInspectorNumber] & ");"
msgbox(StrSQL)
docmd.runsql strSQL
Else
'This is the case where the user chooses an inspector from the provided combo box.
DoCmd.RunSQL "INSERT INTO XREF_FILE_INSPECTOR " _
& "(FILE_NUMBER_CD, INSPECTOR_NUMBER_CD) VALUES " _
& "(" & [Forms]![GeneralFile].[txtGeneralFileNumber] & "," & cmbInspector.Column(0) & " );"
End If
Exit_cmdSaveChanges_Click:
Exit Sub

Err_cmdSaveChanges_Click:
MsgBox Err.Description
Resume Exit_cmdSaveChanges_Click
End Sub

This will let you see the SQL with values that you are actually running and should help you to find out the problem.

HTH

Rob

 

[Freakinchair]

I found the problem and slapped myself as soon as I saw it. I ended up putting a control source on the combo box in my form (this was the same control source as a text box on the same form) so when it tried to save the record I would get the error.

Thanks anyways. (Im so glad there's a forum like this otherwise I would be so lost 50% of the time )