Dynamically refreshing a query in a subform

[DougW via AccessMonster.com]

I have a form bound to a table. I want to use the form to enter new records
in the table. There are 3 fields in each record that are limited selections -
the choices are kept in separate tables and these fields are joined to the
related fields in the main table. The 3 fields are displayed as list boxes
on my data entry form. The user just clicks on the choice they want from
each list box. After making selections from these lists and typing in text
in a fourth field, the user will click an "add record" button to create the
new record.

Unfortunately I have to allow for redundant (duplicate) combinations of my 3
fields.

What I would like to do is to show a query in a subform, that is linked to
the main table, and use the selections from the 3 list boxes as criteria to
drive the query. I would like the query to refresh immediately as each list
box is clicked on. I am trying to use the query to show the user all of the
similar records that have already been put in the table, before the user goes
ahead and creates a possibly duplicate record. If I can't get it to refresh
as the lists are clicked on, it would be okay to trigger the requery from a
"check similar records" button before they add the new record.

What's the smart way to do this? I'm fairly code-impaired so a simple
approach would be best.

Thanks!

-- Doug

 

[DougW via AccessMonster.com]

I figured it out.

I used the form fields as criteria in my query. Added the query as a sub-
form to the main form. Created a macro to requery my query (actually the
requery operates on the name of the control on my form that holds the query
subform). Set the "on click" event on each of my list boxes to run the
macro. Now as I click through my choices in the list boxes, the query in the
subform updates dynamically.

I did have to "unbind" the form from the table - otherwise every time the
query was triggered, Access tried to create a new record in the table. So
for my final step I'm going to add a command button that will run an append
query to add one record to the table, based on the values on the form.

 

[DougW via AccessMonster.com]

As it turns out, I did NOT need to unbind the form from the table.
It was a lack of understanding on my part about how to properly define the
"on click/requery" macro.
After getting that sorted out I was able to re-bind the form and its fields
to the table, so adding my new record now works properly from the form,
without needing to create an append query.

I figured it out.

I used the form fields as criteria in my query. Added the query as a sub-
form to the main form. Created a macro to requery my query (actually the
requery operates on the name of the control on my form that holds the query
subform). Set the "on click" event on each of my list boxes to run the
macro. Now as I click through my choices in the list boxes, the query in the
subform updates dynamically.

I did have to "unbind" the form from the table - otherwise every time the
query was triggered, Access tried to create a new record in the table. So
for my final step I'm going to add a command button that will run an append
query to add one record to the table, based on the values on the form.

I have a form bound to a table. I want to use the form to enter new records
in the table. There are 3 fields in each record that are limited selections -

[quoted text clipped - 23 lines]

-- Doug