Run-time error '3075': Syntax error (missing operator) in query expression 'CustomerID='

Question

When there is data with the same CustomerID, the button is bold, otherwise it is not. This is my error when we click to add a new Customer.

Run-time error ‘3075’: Syntax error (missing operator) in query expression ‘CustomerID=’

Private Sub Form_Current()
If DCount("*", "County Address Table", "CustomerID=" & Me.CustomerID) Then
    Me.[CMDCounty Address Form Open Button].FontBold = True
 Else
    Me.[CMDCounty Address Form Open Button].FontBold = False
End If

If DCount("*", "BOA Table", "CustomerID=" & Me.CustomerID) Then
    Me.[CMD BOA History Button].FontBold = True
 Else
    Me.[CMD BOA History Button].FontBold = False
End If

If DCount("*", "Sanitary Permit table", "CustomerID=" & Me.CustomerID) Then
    Me.[CMD Sanitary History Button].FontBold = True
 Else
    Me.[CMD Sanitary History Button].FontBold = False
End If

If DCount("*", "WI Fund Table", "CustomerID=" & Me.CustomerID) Then
    Me.[CountyOrdersOpen].FontBold = True
 Else
    Me.[CountyOrdersOpen].FontBold = False
End If

If DCount("*", "Land Use Permit Table", "CustomerID=" & Me.CustomerID) Then
    Me.[CMD Land Use History Button].FontBold = True
 Else
    Me.[CMD Land Use History Button].FontBold = False
End If

If DCount("*", "Plat Approval Table", "CustomerID=" & Me.CustomerID) Then
    Me.[PLATHISTORYBUTTON].FontBold = True
 Else
    Me.[PLATHISTORYBUTTON].FontBold = False
End If

If DCount("*", "Rezoning Table", "CustomerID=" & Me.CustomerID) Then
    Me.[CMD REZONE HISTORY BUTTON].FontBold = True
 Else
    Me.[CMD REZONE HISTORY BUTTON].FontBold = False
End If

If DCount("*", "Land Division Table", "CustomerID=" & Me.CustomerID) Then
    Me.[CMD Land Division History Button].FontBold = True
 Else
    Me.[CMD Land Division History Button].FontBold = False
End If

End Sub

I added a space between CMD and County Address Table form, but it just moves on the the next line. In reading the error, I wondered it I should change my "CustomerID=" to 'CustomerID='.

`Me.CustomerID` has no value, I guess - and so the error tells. — Gustav, Jun 23 '23 at 15:40

Tim Williams · Answer 1 · 2023-06-23T18:38:06.897

0

You can streamline your code by using a function:

Private Sub Form_Current()
    
    Me.[CMDCounty Address Form Open Button].FontBold = AnyCustomerRecords("County Address Table")
    Me.[CMD BOA History Button].FontBold = AnyCustomerRecords("BOA Table")
    Me.[CMD Sanitary History Button].FontBold = AnyCustomerRecords("Sanitary Permit table")
    Me.[CountyOrdersOpen].FontBold = AnyCustomerRecords("WI Fund Table")
    Me.[CMD Land Use History Button].FontBold = AnyCustomerRecords("Land Use Permit Table")
    Me.[PLATHISTORYBUTTON].FontBold = AnyCustomerRecords("Plat Approval Table")
    Me.[CMD REZONE HISTORY BUTTON].FontBold = AnyCustomerRecords("Rezoning Table")
    Me.[CMD Land Division History Button].FontBold = AnyCustomerRecords("Land Division Table")
    
End Sub

'Return true if records exist for `Me.CustomerID` in table `tableName`
Function AnyCustomerRecords(tableName) As Boolean
    If Len(Me.CustomerID) > 0 Then 'see @Gustav's comment
        AnyCustomerRecords = DCount("*", tableName, "CustomerID=" & Me.CustomerID) > 0
    End If
End Function

edited Jun 23 '23 at 18:38

answered Jun 23 '23 at 17:10

Tim Williams

154,628
8
97
125

That didn't work. It's not make my buttons bold. – dulster Jun 23 '23 at 18:31
Fixed typo `CustomerRecords` >> `AnyCustomerRecords` – Tim Williams Jun 23 '23 at 18:38
Still not bolding my button. – dulster Jun 23 '23 at 21:21
Sorry I don't have your setup, so can't debug it here. Have you tried debugging? – Tim Williams Jun 23 '23 at 21:31
I have tried debugging. I just dawned on me it's because when adding a new customer my "customerID" field is blank is why it is throwing the error. I need to add something saying is "customerID" is blank, ignore the rest. – dulster Jul 14 '23 at 21:01

Run-time error '3075': Syntax error (missing operator) in query expression 'CustomerID='

1 Answers1