Search form with results in subform

[TJBernard]

I have a request to build a search from in MS Access (it will contain a few drop down boxes for the users to choose from and then a button to click to run the search and show the results).

The results will contain two sub tables of information, so that there can be many results in the sub tables for each main table found result. So a list box is out of the question, or it will print the same Person more than once. (example below):

John A Doe | Expert | Alabama
John A Doe | Expert | Mississippi

So a subform with a subform would show the information better (example):

John A Doe | Expert | Alabama Mississippi

The problem is this information is supposed to be displayed on the same form as the search form (the results will show up on a hidden subform at the bottom of the form). When I try to use Requery and Refresh, the sub form still does not update correctly. I am not sure how to get the data on the subform to refresh for each search.

If anyone has any ideas, I would be glad to hear them.

Thank you,

T.J.

 

[bradcccs]

TJ,

How much of this task have you already completed?

Are you at step 1 or do you have your search working and just want to display as mentioned?

 

[TJBernard]

I already have the correct results, and the search is working correctly, just cannot get the subform to refresh correctly. The users love the design if it would only work correctly.

Basically if you choose from a drop down on a form, and want a subform to appear and show info based on that drop down choice, how do you get the form to refresh or requery correctly?

Thank you for your time,

T.J.

 

[dcx693]

First, how does the subform initially show up? Are there any records in it at all? To get a subform to refresh or requery, make sure you refer to it correctly in VBA.

The syntax can be scary, but it's generally this:
Forms("form_name").Controls("fsubform_Name").Form.Method
where you need to replace method with either Requery or Refresh.

By the way, sometimes I get around this problem by designing the search fields into my form header and placing the search results into the main form body. It skips the whole subform issue, but it won't suit every situation.