Search code examples
sqlms-accessvbaruntime-errormismatch

run-time error 3075 syntax error

I have been struggling with this NUMBER(listed in Table) syntax this morning - I have a form with a combo box [cboIndicator] that looks up the value [Severity Indicator] from a table called tbl_ASPACSplitInventorySelect. Right now Severity Indicators 1-5 are correct, but the NULL value in the combobox box are spitting out an 3075 error.

Private Sub cboIndicator_AfterUpdate()
Dim myIndicator As String
myIndicator = "Select * from tbl_ASPACSplitInventorySelect where ([Severity Indicator] = '" & Me.cboIndicator
Me.ASPACSplitInventorySelect_subform1.Form.RecordSource = myIndicator
Me.ASPACSplitInventorySelect_subform1.Requery
Me.cboGrouping = Null
Me.Combo830 = Null
End Sub

I'm receiving a run-time error 3075 syntax error. As you can tell there is a subform listed and double checked all source objects. Any help would be greatly appreciated. Line Four appears to be the highlighted for data mismatch.

Solution

  • in the statement

    myIndicator = "Select * from tbl_ASPACSplitInventorySelect where ([Severity Indicator] = '" & Me.cboIndicator

    a quote and a ')' are missing.

    If the table field Severity Indicator is defined as TEXT:

    myIndicator = "Select * from tbl_ASPACSplitInventorySelect where ([Severity Indicator] = '" & cstr(Me.cboIndicator) & "');"

    If the table field Severity Indicator is defined as NUMBER:

    myIndicator = "Select * from tbl_ASPACSplitInventorySelect where ([Severity Indicator] = " & cstr(Me.cboIndicator) & ");"

    Please note that before this statement it would be useful to check the value of Me.cboIndicator, with something like:

    If not isnull(Me.cboIndicator) and not isempty(Me.cboIndicator) then ...