Form filter gives incorrect results at first

[jmaustin]

I have a table with about 8000 parts (Access 2003). On my "search" form, I
allow users to enter search criteria, then I open another form (based on a
query of the parts table) to display their search results. This part works
fine. However, if they specify a certain kind of search criteria, I have to
change the query I use to get their results, so when I open the results form,
they'll get the correct information. Here's my code:

docmd.openform "Parts for Users", acnormal, qryQueryByAsset

where "qryQueryByAsset" is a querydef I create on the fly based on the user's
critieria.

When the results form is displayed, the data is incorrect, but the user's
cursor will be an hourglass; after a second, the correct results are
displayed. However, my users find it very disconcerting to see that
incorrect part number (it's always the same one, I'm assuming it's the first
part in the table) which then changes to the correct part information.

Any ideas on how to prevent this? I don't really want to make another form
based on this different query (I have several other forms and reports based
on the results form, so I'd end up with extra copies of those, too). Is the
problem because I've got so many records?

Thanks in advance,

Jenifer A.

 

[Jeff Boyce]

To what dataset is that form that displays initially bound? From your
description, it sounds like it displays a record (?the first) until it
receives the new record. Why have it display anything initially?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jmaustin]

The form was originally bound to a query containing information from two
tables, so that all the controls on the form were properly bound. I just
tried removing the recordsource from the results form, and now instead of the
user seeing an incorrect part number, they will see "#Name" in every field
until the correct information is found. Is there any way to have my form
display blank fields until the correct info can be loaded? Do you have any
idea why this delay happens? Is my query just that inefficient? By the way,
I'm using Access 2002, not 2003, sorry about that.

Thanks,

Jenifer

To what dataset is that form that displays initially bound? From your
description, it sounds like it displays a record (?the first) until it
receives the new record. Why have it display anything initially?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with about 8000 parts (Access 2003). On my "search" form, I
allow users to enter search criteria, then I open another form (based on a

[quoted text clipped - 30 lines]

Jenifer A.

 

[Jeff Boyce]

Jenifer

An approach I've used is to create a query that looks to a combobox on a
form for a selection criterion of which record(s) to display.

When the form first opens, there's nothing selected in the combobox, so the
form displays "that" record (i.e., nothing, no #Name, no data, nothing).

In the combobox (unbound, by the way), the AfterUpdate event includes:

Me.Requery

So after I select a record (usually by name, not by record number) in the
combobox, the AfterUpdate kicks in, the form requeries the query, the query
"sees" the selected record, and the form displays it.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The form was originally bound to a query containing information from two
tables, so that all the controls on the form were properly bound. I just
tried removing the recordsource from the results form, and now instead of
the
user seeing an incorrect part number, they will see "#Name" in every field
until the correct information is found. Is there any way to have my form
display blank fields until the correct info can be loaded? Do you have
any
idea why this delay happens? Is my query just that inefficient? By the
way,
I'm using Access 2002, not 2003, sorry about that.

Thanks,

Jenifer

To what dataset is that form that displays initially bound? From your
description, it sounds like it displays a record (?the first) until it
receives the new record. Why have it display anything initially?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with about 8000 parts (Access 2003). On my "search" form,
I
allow users to enter search criteria, then I open another form (based on
a

[quoted text clipped - 30 lines]

Jenifer A.

 

[jmaustin via AccessMonster.com]

Hmm.... I'm not sure I understand this. So the results form is still bound
to my original query, right? Then I put an (unbound) combobox on it, with
the requery in the AfterUpdate event, and when I open the form (with my new
query), I put something -- anything -- in the combobox so that the requery
runs? But what's in the rest of the controls on my form that usually have
the part's title, approval information, etc.? They're still bound to the
original query, so won't they still show information?

Thanks,

Jenifer

Jenifer

An approach I've used is to create a query that looks to a combobox on a
form for a selection criterion of which record(s) to display.

When the form first opens, there's nothing selected in the combobox, so the
form displays "that" record (i.e., nothing, no #Name, no data, nothing).

In the combobox (unbound, by the way), the AfterUpdate event includes:

Me.Requery

So after I select a record (usually by name, not by record number) in the
combobox, the AfterUpdate kicks in, the form requeries the query, the query
"sees" the selected record, and the form displays it.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The form was originally bound to a query containing information from two
tables, so that all the controls on the form were properly bound. I just

[quoted text clipped - 28 lines]

 

[Jeff Boyce]

I was pointing out that you would NOT leave the form bound to the original
query, but to the new query that includes the selection criterion that
points to the combobox on the form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hmm.... I'm not sure I understand this. So the results form is still
bound
to my original query, right? Then I put an (unbound) combobox on it, with
the requery in the AfterUpdate event, and when I open the form (with my
new
query), I put something -- anything -- in the combobox so that the requery
runs? But what's in the rest of the controls on my form that usually have
the part's title, approval information, etc.? They're still bound to the
original query, so won't they still show information?

Thanks,

Jenifer

Jenifer

An approach I've used is to create a query that looks to a combobox on a
form for a selection criterion of which record(s) to display.

When the form first opens, there's nothing selected in the combobox, so
the
form displays "that" record (i.e., nothing, no #Name, no data, nothing).

In the combobox (unbound, by the way), the AfterUpdate event includes:

Me.Requery

So after I select a record (usually by name, not by record number) in the
combobox, the AfterUpdate kicks in, the form requeries the query, the
query
"sees" the selected record, and the form displays it.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The form was originally bound to a query containing information from two
tables, so that all the controls on the form were properly bound. I
just

[quoted text clipped - 28 lines]

Jenifer A.

 

[jmaustin via AccessMonster.com]

So, I only have one form (search form) instead of two (search and results)?
The search form has a bunch of combo boxes (so the user can search by part
number, title, etc.), and every time they choose a new piece of criteria, I
do the requery and show the results on the same form?

I think I get it now, thanks very much! I really appreciate your help!

Regards,

Jenifer A.

I was pointing out that you would NOT leave the form bound to the original
query, but to the new query that includes the selection criterion that
points to the combobox on the form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hmm.... I'm not sure I understand this. So the results form is still
bound

[quoted text clipped - 41 lines]

 

[jmaustin via AccessMonster.com]

I get it now! I tried it, still with two forms, and it works beautifully.

Thanks so very much,

Jenifer A.

So, I only have one form (search form) instead of two (search and results)?
The search form has a bunch of combo boxes (so the user can search by part
number, title, etc.), and every time they choose a new piece of criteria, I
do the requery and show the results on the same form?

I think I get it now, thanks very much! I really appreciate your help!

Regards,

Jenifer A.

I was pointing out that you would NOT leave the form bound to the original
query, but to the new query that includes the selection criterion that

[quoted text clipped - 10 lines]