filtering records in a form from another query?

[paborden]

I have created two queries, query A and query B. I have also created a form that refers to query A. Is it possible to create checkboxes/buttons next to each record in form A such that, when manually checked, the corresponding record from query B will be excluded/filtered out?

If not, is there another way to do this more effectively?

 

[Dumbfounded]

Hi,

Not really sure what you're trying to achieve here but I've just done something similar. Mine was for complaints and requests. In the original table that the form was created from I added two extra fields, one for requests and one for complaints and made them yes/no fields. I then added these new fields to the form and created two queries marking the criterea as 'yes' in both.

Hope this helps you with what you are doing.

Dx

 

[Tim L]

Welcome to the forums!

Where are the records from query B being displayed? Is it the case that you have a single form that shows the result set from query A but when you check the box it then displays only those records that are in query A which have no corresponding record in query B?

If so, use the After Update event of the check box to change the Record Source of the form accordingly; but I think you need a query C to show the unmatched records.

If not then you will need to explain more clearly where your records are, which form(s) they reside in (is there a form/sub-form relationship) and possibly you need to have a field in the underlying table of query A to store the current value of the check box if it really is 'next to each record' - - because if you have a continuous form and an unbound check box you will soon find out that checking it against one record has the effect of checking it against all of them, and vice-versa; if you don't want this effect you will probably need to choose from one of the following: binding the checkbox to records (needs a field in the underlying table), stop using continous form display mode (if you're using it), move the check box off the 'record' onto the form header/footer.

I'm going away for a few days but if you do have further questions/information someone else may be able to get back to you, either that or wait a week or so for me to get home.

Hope this helps.

Tim

 

[paborden]

I've been fiddling with this for the past two hours, and here's what I've come up with (the joys of trying to expand my access proficency):

1) I need to create a Yes/No field in Query A

2) I then need to bring this Yes/No field into Form A, and connect this field to checkboxes. When each checkbox is checked or unchecked it changes the corresponding query value to yes or no.

3) I then need to link the edited Yes/No field in Query A to Query B, where only Yes values are displayed.

I think this is what needs to be done. Any thoughts?

 

[Dumbfounded]

Can you post it on the site so we can see what you're trying to do?

 

[Tim L]

The yes/no box needs to be in the underlying table, otherwise it can't appear in the query... Other than that, your plan looks sound.

Tim

 

[paborden]

SUCCESS:

For the record, to get around my limitations, I created a make-table query that brought together two linked tables, one containing the necessary records mapped to a number, the other containing checkboxes mapped to the same number. This query then creates a table showing record and checkbox. The form edits this table, which it turn determins inclusion/exclusion of a particular record.

Thanks for your help everyone!

 

[Dumbfounded]

Isn't it just great when everything comes together - that's the love bit of access lol.

Glad it worked for you.