"WHERE" clauses in DoCmd.OpenForm no longer working

[Chris O''Neill]

I'm not sure what I've done, but I've messed something up big time.

I have a number of subs (mostly command buttons) where I open another form
filtering on a control in the current form. My syntax is like this:

strCriteria = "[GLID] = " & Me.txtGLPostingID
DoCmd.OpenForm "frmGeneralLedger", , , strCriteria

In the above case, GLID and Me.txtGLPostingID are both numbers. When the
fields/controls I'm working with are strings, I use this syntax:

strCriteria = "[Company] = " & Chr(34) & Me.txtCompany & Chr(34)
DoCmd.OpenForm "frmCompanyInfo",,, strCriteria

All of this worked up until today. Today, none of these "WHERE" clauses
work. So, I'm sure it's not a syntax problem with one subroutine but,
rather, something I've done application-wide. When I print out any of the
strCriteria lines in the Immediate Window, everything appears as it should
but they still don't work.

Thoughts, suggestions, hints/tips on what I might have messed up to cause
this problem? Any help offered is greatly appreciated!

Regards, Chris

 

[Allen Browne]

Chis, when everything goes haywire, it might be time to backup, compact,
decompile, and then compact twice again. Detailed steps:
http://allenbrowne.com/recover.html

Are you certain the other forms are not open already? Not even in design
view? The OpenForm won't apply the filter if they are.

You may also want to check under tools | Options in the VBA window, to make
sure you have your error handling set to Break on Unhandled Errors (and
Compile on Demand turned off), so you get notified of any error.

Beyond that, I take it that the form is opening, so see if the filter is
applied after you open it:
? Forms!frmGeneralLedger.Filter
? Forms!frmGeneralLedger.FilterOn
Access doesn't always report FilterOn correctly for reports, but it does for
forms.

 

[Chris O''Neill]

Thank you, Allen, for those suggestions. That's exactly the kind of info I
was looking for. I'll take a look at it in awhile... my wife insisted that
I make my famous ginger snap cookies for dessert tonight.

Btw, the forms all load but none of them are filtering. I opened them in
design mode and the Properties window shows the correct filter strings and
filtering on. I haven't checked in the Immediate Window to see what the
..Filter and .FilterOn properties are, but I'll do that after the cookies are
done.

Thanks, again, for your help. My bet is I'll find it's some *really*
stoooooopid mistake on my part. (sheepish grin!)

Regards, Chris

Chis, when everything goes haywire, it might be time to backup, compact,
decompile, and then compact twice again. Detailed steps:
http://allenbrowne.com/recover.html

Are you certain the other forms are not open already? Not even in design
view? The OpenForm won't apply the filter if they are.

You may also want to check under tools | Options in the VBA window, to make
sure you have your error handling set to Break on Unhandled Errors (and
Compile on Demand turned off), so you get notified of any error.

Beyond that, I take it that the form is opening, so see if the filter is
applied after you open it:
? Forms!frmGeneralLedger.Filter
? Forms!frmGeneralLedger.FilterOn
Access doesn't always report FilterOn correctly for reports, but it does for
forms.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm not sure what I've done, but I've messed something up big time.

I have a number of subs (mostly command buttons) where I open another form
filtering on a control in the current form. My syntax is like this:

strCriteria = "[GLID] = " & Me.txtGLPostingID
DoCmd.OpenForm "frmGeneralLedger", , , strCriteria

In the above case, GLID and Me.txtGLPostingID are both numbers. When the
fields/controls I'm working with are strings, I use this syntax:

strCriteria = "[Company] = " & Chr(34) & Me.txtCompany & Chr(34)
DoCmd.OpenForm "frmCompanyInfo",,, strCriteria

All of this worked up until today. Today, none of these "WHERE" clauses
work. So, I'm sure it's not a syntax problem with one subroutine but,
rather, something I've done application-wide. When I print out any of the
strCriteria lines in the Immediate Window, everything appears as it should
but they still don't work.

Thoughts, suggestions, hints/tips on what I might have messed up to cause
this problem? Any help offered is greatly appreciated!

Regards, Chris

 

[Chris O''Neill]

I found the problem and, yup, it's a *reallly* stoooopid oversight on my part.

Awhile back, I added some filtering functions to the OnOpen event of several
forms. It just happens that some of these are the same forms I'm now trying
to open with the "WHERE" clause of the DoCmd.OpenForm command. So, of
course, what's happening is that the DoCmd opens the form with the desired
filter, and then the OnLoad event immediately changes it to another filter
string! (DOH!!!!)

[He hangs his head in shame....]

I guess I'm going to have to use openargs to pass my new filter string and
then, using an IF/THEN/ELSE append that to the string in the OnLoad event
before I apply the filter. (The reason I'm going to do it this way is
because in most cases I want to open the forms with the filter coded in the
OnLoad event, but occassionally in specific circumstances I want to open it
with the other filter.) Does that make sense, everyone???

Anyway, Allen, I again thank you for pointing me in the right direction. I
probably would've muddled around for days until I figured that out on my own.
Btw, I'm now going to follow that advice in the link you gave me.... time
to backup, compact, etc. just to be sure everything's in order.

Thanks, again...

Regards, Chris

Chis, when everything goes haywire, it might be time to backup, compact,
decompile, and then compact twice again. Detailed steps:
http://allenbrowne.com/recover.html

Are you certain the other forms are not open already? Not even in design
view? The OpenForm won't apply the filter if they are.

You may also want to check under tools | Options in the VBA window, to make
sure you have your error handling set to Break on Unhandled Errors (and
Compile on Demand turned off), so you get notified of any error.

Beyond that, I take it that the form is opening, so see if the filter is
applied after you open it:
? Forms!frmGeneralLedger.Filter
? Forms!frmGeneralLedger.FilterOn
Access doesn't always report FilterOn correctly for reports, but it does for
forms.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm not sure what I've done, but I've messed something up big time.

I have a number of subs (mostly command buttons) where I open another form
filtering on a control in the current form. My syntax is like this:

strCriteria = "[GLID] = " & Me.txtGLPostingID
DoCmd.OpenForm "frmGeneralLedger", , , strCriteria

In the above case, GLID and Me.txtGLPostingID are both numbers. When the
fields/controls I'm working with are strings, I use this syntax:

strCriteria = "[Company] = " & Chr(34) & Me.txtCompany & Chr(34)
DoCmd.OpenForm "frmCompanyInfo",,, strCriteria

All of this worked up until today. Today, none of these "WHERE" clauses
work. So, I'm sure it's not a syntax problem with one subroutine but,
rather, something I've done application-wide. When I print out any of the
strCriteria lines in the Immediate Window, everything appears as it should
but they still don't work.

Thoughts, suggestions, hints/tips on what I might have messed up to cause
this problem? Any help offered is greatly appreciated!

Regards, Chris

 

[Tony Toews [MVP]]

I guess I'm going to have to use openargs to pass my new filter string and
then, using an IF/THEN/ELSE append that to the string in the OnLoad event
before I apply the filter. (The reason I'm going to do it this way is
because in most cases I want to open the forms with the filter coded in the
OnLoad event, but occassionally in specific circumstances I want to open it
with the other filter.) Does that make sense, everyone???

Yup, makes sense. I do that kind of thing all the time. For example
I might have a continuous form which you can view and search on from
the main menu. However sometimes I might want to call that from
another form limiting the search criteria by customer or unit or some
such.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/