Pull all data from every table which meets certain criteria

[wily_wolf]

Hey,

I have a form with a combo box and some buttons. The user selects a Claim Number from the combo box and then selects a button in order to view whichever report they would like to see.



I am now in need of a button which pulls all of the data pertaining to that Claim Number from every table in the database. So, in other words, user selects a Claim Number from the combo box, hits a button, and then gets a report that has all of the data from every single table regarding that certain Claim Number.

My first thought was simply a query that utilizes all of the tables and in the "Criteria" field, put Like "*" & [Forms]![frmEntry1]![cboClaimNumber] & "*" Or Is Null, but apparently if you have all fields selected from each table, and multiple tables on top of that, you cannot specify certain criteria.

Anyways, any help/guidance would be awesome. Thank you in advance.

[aytee111]

Put the criteria in the "DoCmd.Open..." of the report. It will filter on that claim number.

[wily_wolf]

Hey aytee,

I figured that since this is basically a data dump that I would export the data (using the query) to an Excel spreadsheet using VBA. I think that might put a damper on this idea, right?

[aytee111]

Exporting data to Excel is, well, a dump.

[wily_wolf]

Right right. Say, if I use your idea, I would create an Access report and, using VBA, would create an event within the "On Open" property?

[aytee111]

No, when you are opening the report (see post #2)

[wily_wolf]

Well then, I guess I don't understand what you're talking about. Because you said to put the criteria in the "DoCmd.Open..." of the report. Where "on the report" are you talking about?

[aytee111]

user selects a Claim Number from the combo box, hits a button, and then gets a report

It is all in the "gets a report". How does "gets" happen?

[wily_wolf]

So, from the button then. That's all you had to say.

[aytee111]

Miscommunication!

[Bulzie]

Wolf if you can link all of your tables by a PK, then you can use a query to select all the fields and put the combo box syntax in the criteria for the Claim Number field in the query. This should pull all data for that claim number. Once you have that, you can export that query to excel using vba. Is that what you are trying to do?

[AccessPower]

First, I would suggest setting your report source to a query that includes all of the desired records from all tables.

For the criteria, I suggest having your button open the report using VBA. This way you can specify the criteria easily.

DoCmd.OpenReport "YourReportName", acViewReport, , "[YourCriteria]='" & Me.YourComboBox.Value & "'"