Mysterious Error 3071 (1 Viewer)

[mitxel]

Hi!
I’m creating a new view of an existing database, planning a new search form for it. After I filled the tables with some test data somehow this error massage appeared:
“This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)”
The massage happens every time I changing the record on the filtered main form (using the built-in Access navigation buttons). Funny thing is: everything seems to be working just fine; I just have to OK the error every time.
This is what I’m trying to do: on the main form there is a “search” button, the user clicks on it and a new form appear where he can set the criteria. After confirming the form goes away and the main form is filtered by the given information. I wanted a search where 1. they don’t have to use exact matches 2. they can filter by two or more fields in the same time.
Looking for the problem I found that this error shows when working with queries, usually caused by date fields. But I don’t have any date on the search from.
Ah yes, I tried repairing the database without any luck.
Any help would be appreciated. Please keep in mind I’m not educated in IT so fundamentals could be missing: S

This is the original code:

Module1:

Option Compare Database
Global myFilter As String
Option Explicit

Main form:

Private Sub Search_Click()
    myFilter = ""
‘The search panel will open where the SQL WHERE expression (myFilter) is built
    DoCmd.OpenForm "p_search", , , , acNormal, acDialog
    If myFilter = "" Then Exit Sub
    Me.Filter = myfilter
    Me.FilterOn = True
    If Me.Recordset.RecordCount = 0 Then
        MsgBox ("No results!")
        myFilter = ""
        Me.FilterOn = False
    End If
End Sub

„p_search” form:

Private Sub OK_Click()
’If everything left blank just close the form
If IsNull(Me.TextX) And IsNull(Me. ComboXX) And IsNull(Me. TextXXX) And //…// Then
    DoCmd.Close acForm, "p_search"
    Exit Sub
End If
’If field isn’t empty start to build an SQL WHERE expression
If Nz(Me.TextX, "") <> "" Then
    myFilter = "[p_00_number] Like '*" & Replace(Me.TextX, "'", "''") & "*' And "
End If
If Nz(Me. ComboXX, "") <> "" Then
    myFilter = myFilter & "[p_00_name] Like '*" & Replace(Me. ComboXX, "'", "''") & "*' And "
End If
If Nz(Me. TextXXX, "") <> "" Then
    myFilter = myFilter & "[p_00_person] Like '*" & Replace(Me. TextXXX, "'", "''") & "*' And "
End If
// … //
’Delete the “ and “ from the end of myFilter if there is anything in it
If myFilter <> "" Then
    myFilter = Left(myFilter, Len(myFilter) - 5)
‘If myFilter is empty just leave the Filter empty and off
    Else
    Me.Filter = ""
    Me.FilterOn = False
End If
‘Go back to the main form
DoCmd.Close acForm, "p_search"
End Sub

On the road to solve the issue I reduced (and slightly altered) the code to this (everything else is commented out), but the error still occurs:
„p_search” form:

Private Sub OK_Click()
If Nz(Me.TextX, "") <> "" Then
    myFilter = "[p_00_number]" & " Like " & Chr$(34) & "*" & Me.TextX & "*" & Chr$(34) & " And "
End If
If myFilter <> "" Then
    myFilter = Left(myFilter, Len(szűrő) - 5)
    Else
    Me.Filter = ""
    Me.FilterOn = False
End If
DoCmd.Close acForm, "p_ search "
End Sub

 

[CJ_London]

Congrats on using tags to highlight your code, but rather than use the quote tags, please use code tags (in the advanced editor), then your code would retain its indenting and be a lot easier to read. You will get more help that way - users, who give their time for free, look at a big block of unindented code and think 'why bother, I don't have time to decipher that'

However with dates expressed as strings, you need to use to use the US format ie.

"... =#" & format(me.txtDate,"mm/dd/yyyy") & "#"

With regards your error, this is probably due to an empty (or uninitialised) value (as opposed to null). I can't see anything obvious from the code provided but since it is triggered when you change the record, I would look at your form current event and step through the code until you find the error. I can see your search form populates a string called myFilter, but don't see how you are passing it back to your main form - perhaps it is not initialised?

 

[mitxel]

Thank you for your response!

I edited my original post – sorry for the bad tags.

There is no current event on the main form, and if I am right, this is the only event triggered when stepping on the next record. The myFilter is passed due its global variable nature. I’m aware of that this is not the best/correct way to use global values, but seemed to work until this point.

Based on your guidelines I’m checking the variables on the main form and looking for an alternative way to pass the myFilter string from the search form to the main form. I will give an update how these turned out.

 

[CJ_London]

Thanks for reposting the code - much more readable

There is no current event on the main form

What I meant was put a break there so the code stops and you step through

you need a code line for the break so just put some nonsense - a call to a msgbox or something like A=B

If you find nothing go back a stage - suggest the form before update event and step through from there until you get to the current event where you were before.

 

[mitxel]

At last! Some time to focus on the issue…

I played with the break line as you suggested. The error appears before the current event (If I put the break in the very first line the error happens and the break follows it right away). This means (?) the problem is in the code I originally posted and the other stuff on the main form has nothing to do with it.
To examine this further I commented out everything excluding the lines connected to the search function. Not just the code on the main form, literally everything. The error still occurs as before.

I looked for more information regarding how to pass variables/values from one form to another. On many places they suggested the exact way I used: global variables. Also my case seems really simple because I’m not closing the main form; it remains open while the user sets the filter.

I’m in ruins right now, I tried everything I could but nothing seems to work …

 

[CJ_London]

it is possible that your code is corrupted - have you tried to decompile the code?

If you don't know how - see post #4 on this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=273246&highlight=decompile

 

[CJ_London]

the very first line the error happens

And what line is that in your code?

 

[mitxel]

I followed the instructions but nothing changed regarding my problem.

Very handy tool by the way, the size is greatly reduced indeed

 

[mitxel]

Sorry I wasn’t clear: I meant the very first row = the first row of the current event which is empty.
There was only the break line; I didn't have current event before.

 

[CJ_London]

not being very clear. You need to determine when the code starts running and then step through the code until you get the error.

However I've just reviewed your posts and I think module 1 should be

Option Compare Database
Option Explicit
 
[COLOR=red]Public[/COLOR] myFilter As String

 

[mitxel]

I changed the code but no luck.

I am feeling very stupid that I cannot explain. The error pops up only when I use the navigation panel. When I click OK on the search panel everything goes fine: panel closing, main form is filtered and shows the expected number of records.

I inserted the break to every line in my code but without any use: my code isn’t touched when I am stepping on the next or previous record (break isn’t triggered). If I write the break into the current event the error happens before the break.

Is it possible I totally misunderstood the point of the break line?

And now it’s getting better: I put a button on the main form to step on the next record (see below). If I use this button the error isn’t showing. Hilarious!

Private Sub Parancsgomb56_Click()

DoCmd.GoToRecord , , acNext

End Sub

 

[CJ_London]

the break line is merely a way of halting the code so you can see what is happening. You then hit the F8 button to step to the next line and can do things like

in the immediate window type ?myFilter to see what the value is
hover over a variable to see what the value is
by stepping through you can see if an 'if' line is working or what error code is generated

It's all a major part of debugging your code

 

[mitxel]

Well, I definitely learned a lot about bug hunting.

I managed to understand the point of the breakpoint (I can be really dumb sometimes). I have to say: it’s an extremely useful feature to see your code “in action” line by line. As result I had to confirm: the code is working perfectly as indented.

So I turned my focus on other things. To make long story short: there was a filter set on one of the subforms (p_02_contract_ID='p_02_ontract_ID'; honestly I don’t know how it get there, because I created the subform by hand, without a wizard). It was hard to find because the FilterOnLoad property was set to false, so it only triggered when I used another filter myself…

Thank you very much for your help and patience!