Query from Master Form to Sub Form

[ASGIAnalyst]

Hello,

I'm quite new to Access and unfortunately I feel like I'm about to ask a very rudimentary question. I've attached a picture of what I'm trying to accomplish, but in a nutshell my problem is that when I click the "Search" button, my results don't show up in the sub-form.

I know that the query I've written (and linked to the "Search" button) is correct because the results that I get are exactly what I want...only NOT where I want them. Can someone please help me connect the dots?? Thanks!

 

[stopher]

Welcome to the forum.

Search tools can be implemented in many different ways. It may be easier if you upload your database to the forum as this will make it easier for people to diagnose the problem.

Chris

 

[ASGIAnalyst]

Thanks Chris - the problem is that the database contains sensitive information that I can't make public. If I were to just upload the forms without the tables, would that work?

 

[ASGIAnalyst]

I definitely understand...unfortunately I can't upload the entire database as it contains sensitive non-public information such as hedge fund performance etc. Is there any thing else I can do to help you help me??

 

[stopher]

I definitely understand...unfortunately I can't upload the entire database as it contains sensitive non-public information such as hedge fund performance etc. Is there any thing else I can do to help you help me??

Ideally you can upload your database without any data in it. If not then you should try and post as much info as possible e.g. a screen dumpt of your table relationships, any relevant VBA, the query behind the form etc.

Chris

 

[ASGIAnalyst]

Hi Chris - apologies for the slow response! When you say "upload the database without any data in it," are you suggesting that I delete all of the data in my tables? If so, I attempted to do just that, but the file size was too large (even zipped)...I must be doing something wrong.

Let me try to explain my objective better though:

1. "Hedge Fund Search" is the main form
2. The combo boxes on the main form are bound to the "Matrix Database" table
   
3. "Hedge Fund Search Sub-Form" is the sub form to the main form
4. The "Search" command button is supposed to be a requeary search or macro that uses any or all of the data from the combo boxes in the main form, finds the relevant record(s) in the "Matrix Database" table, and lists those records in the "Hedge Fund Search Sub-Form." (see attached screenshot)
5. The command button next to each line in the "Hedge Fund Search Sub-Form," once clicked, will open up the "Main ASGI Database" form for the fund listed under "Hedge Fund Name." (see attached screenshot)
   

However, here's what I have yet to figure out:

1. How to link the results of my query to the sub-form.
2. How to link the command button next to the results in the "Hedge Fund Search Sub-Form" to the "Main ASGI Database" form.

I've written two queries that appear to work - the only problem is that the results are not being directed to the sub-form (so annoying).

I hope this makes sense. If you still need the database in order to help, I can try to delete some of the lesser-used tables so that the file size is small enough to attach.

Thanks for your time!

 

[HAMMAMABUARQOUB]

However, here's what I have yet to figure out:

1. How to link the results of my query to the sub-form.
2. How to link the command button next to the results in the "Hedge Fund Search Sub-Form" to the "Main ASGI Database" form.

1- I suggest you check your query to be the datasource of that subform
then write the controls "combo boxes" names as "Forms![FormName]![ControlName] in the criteria section under each of the fields as Or statment.. and open the form, it will be showing no data until you fill in on or tow of your lists in the main form...
that's what itried,,,

 

[stopher]

Hi Chris - apologies for the slow response! When you say "upload the database without any data in it," are you suggesting that I delete all of the data in my tables? If so, I attempted to do just that, but the file size was too large (even zipped)...I must be doing something wrong.

After you delete your data you need to do a compact and repair. This will resize your database. Obviously only do this on a copy!

How to link the results of my query to the sub-form.

Actually you don't need a subform. Your search boxes can be unbound (not linked to any data). See the example database I've attached. Take a look at frmSearchCustomer and look at the code behind the search button.

How to link the command button next to the results in the "Hedge Fund Search Sub-Form" to the "Main ASGI Database" form.

Take a look at the code behind the SELECT button in my example

hth
Chris

 

[ASGIAnalyst]

Thanks to both of you. I'll dive into your suggestions, but being able to compact the database for posting should hopefully make this a lot easier for all of us. I've attached the file (which still needed to be zipped for some reason).

HAMMAMABUARQOUB - I think you're right in that the syntax of my query might be wrong. Now that the database is attached, would you please take a look?

If either of you run "Matrix Database Query" or "Matrix Database Query 2", (I have yet to decide which one I want to use, but for the sake of this forum, let's use Matrix Database Query), the results should be listed in "Hedge Fund Search Sub-Form." From there, the user can click on the command button next to the record, which will open the "Main ASGI Database Sub-Form."

Thanks again for your assistance!