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

Processional

What is the content of stLinkCriteria (the actual value it contains, not what you think it contains) when you get to DoCmd.OpenForm? (Set a breakpoint at the OpenForm line, run to that point, and look at what stLinkCriteria is when you get there.) – Calli

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.

Calli

Thanks very much! It works, but i think you've missed out a 'End If'. The explaination is clear, I can now understand what went wrong. – Processional

Yep, I did. I'll fix it. Thanks. :-) – Calli

back to where i stopped... how can i display my records? the textbox doesnt shows the results i want... – Processional

I don'r know. Nothing in your question asked about display ing the results, which means that's a new, separate question. – Calli

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.

Fructification

Answer 3

I get this error from an extra space between a dot and the field name in:

DoCmd.RunSQL "UPDATE  cp  INNER JOIN [char_lib] ON [cp]. [library_id]  = [char_lib].[id] set..

Note to Microsoft: please create more precise and useful error messages. Thanks. I've no doubt you'll get right on this.

Galilee