Microsoft Access VBA - Run time error '3075'

Question

I've encountered the run time error '3075'. I'm a novice in VBA! >.< Can i know where did it went wrong? I can't solve it...

For e.g, if I enter a name "Sally" into a textbox (txtMainName), upon clicking on the search button the error pops-up.

The error:

Run-time error '3075':

Syntax error(missing operator) in query expression ' And [Main Applicant Name] Like 'Sally'".

Public Sub Search_Record()

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String


    stLinkCriteria = ""
    stDocName = "frmDisplayInfo"

    If (Me!txtMainName <> "") Then
        stLinkCriteria1 = "[Main Applicant Name] Like ' " & Me![txtMainName] & "'"
        stLinkCriteria = stLinkCriteria & " And " & stLinkCriteria1
    End If

    If (Me!txtIDNo <> "") Then
        stLinkCriteria2 = "[ID No] Like ' " & Me![txtIDNo] & "'"
        stLinkCriteria = stLinkCriteria & " And " & stLinkCriteria2
    End If

    '(*This part is highlighted*)
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Maximize

End Sub

Answer 1

Runtime error 3975 is Invalid operator, according to a quick Google search on access vba runtime error 3075 (which you could have made yourself).

The problem is that you only assign a value of "" to stCriteria. If the first if statement executes, stCriteria is then assigned the value of stCriteria AND stCriteria1, which is invalid.

The same thing happens if the second if statement is executed. (In fact, if both are actually run, stCriteria now contains AND stCriteria1 AND stCriteria2, which is even worse.)

The solution is to modify your code to only add the AND if it's needed:

stLinkCriteria = ""
stDocName = "frmDisplayInfo"

If (Me!txtMainName <> "") Then
    stLinkCriteria = "[Main Applicant Name] Like ' " & Me![txtMainName] & "'"
End If

If (Me!txtIDNo <> "") Then
    If stLinkCriteria <> "" then
        stLinkCriteria = stLinkCriteria & " AND "
    End If
    stLinkCriteria2 = "[ID No] Like ' " & Me![txtIDNo] & "'"
    stLinkCriteria = stLinkCriteria & " And " & stLinkCriteria2
End If

For future reference, the way to solve these kind of problems is to actually examine the value of the variable (in this case stCriteria) just before the line causing the error (here, it's the DoCmd.OpenForm line) executes. You do so by setting a breakpoint on the line where the error happens, running your app until the breakpoint is hit, and then examining the value of the variable. This shows you exactly what the variable contains, which can reveal the problem.

Answer 2

From the logic, it seems like your stLinkCriteria is starting with "and" which is causing the error:

You probably should put:

If (Me!txtMainName <> "") Then

    stLinkCriteria1 = "[Main Applicant Name] Like ' " & Me![txtMainName] & "'"

    if stLinkCriteria <> "" then                   <==== add this

       stLinkCriteria = stLinkCriteria & " and "   <==== and this

    end if                                         <==== and this

    stLinkCriteria = stLinkCriteria & stLinkCriteria1  <=== change this

End If    

Hope that works - otherwise, the logic looks good.