Get field value for each entry in query and store to a set of variables.

[weasel7711]

A little pre-information so that my question will make more sense:
I have a database that tracks our company's repairs with two main tables (there are other tables but they basically hold lookup information for data entry).

These two tables are called RMA and Product.
[tblRMA] contains all the information for each of the repairs. It's primary key is the .[RMA] field which is a unique number to each entry (obviously). The [tblProduct] contains an .[RMA] field, a .[Serial] number, and a .[Model] number.
The two tables have a many to one relationship. That is, there can be many [tblProduct] entries for one [tblRMA]. They are related on the .[RMA] field.

The QUESTION:
So I have a form that allows us to search for a serial number using a textbox and a command button. I have a universal report that shows the information for a [tblRMA] and a subtable that shows that .[RMA]'s associated Products by displaying each [Model] and [Serial]. I know how to filter this report using the WhereCondition for all of the items in [tblRMA] but I am confused as to how to filter it for a search criteria based on the [tblProduct] subtable. I created a query (lets call it subqrySerialSearch) that returns all of the entries in [tblProduct] where the [Serial] matches the text box. Now after this I figured I can take each .[RMA] in this query and use an IN clause to filter the report. Something like:

WhereCondition = "IN {set of all RMAs in the subqrySerialSearch}"

So I need to figure out how to create that set for the WHERE clause


I figured I can traverse the query using a loop and append each .[RMA] value to a string that I can use in formulating the WhereCondition.

OR

If that's a stupid idea, please suggest something more efficient.

[SteveF]

A little pre-information so that my question will make more sense:
I have a database that tracks our company's repairs with two main tables (there are other tables but they basically hold lookup information for data entry).

These two tables are called RMA and Product.
[tblRMA] contains all the information for each of the repairs. It's primary key is the .[RMA] field which is a unique number to each entry (obviously). The [tblProduct] contains an .[RMA] field, a .[Serial] number, and a .[Model] number.
The two tables have a many to one relationship. That is, there can be many [tblProduct] entries for one [tblRMA]. They are related on the .[RMA] field.

The QUESTION:
So I have a form that allows us to search for a serial number using a textbox and a command button. I have a universal report that shows the information for a [tblRMA] and a subtable that shows that .[RMA]'s associated Products by displaying each [Model] and [Serial]. I know how to filter this report using the WhereCondition for all of the items in [tblRMA] but I am confused as to how to filter it for a search criteria based on the [tblProduct] subtable. I created a query (lets call it subqrySerialSearch) that returns all of the entries in [tblProduct] where the [Serial] matches the text box. Now after this I figured I can take each .[RMA] in this query and use an IN clause to filter the report. Something like:

WhereCondition = "IN {set of all RMAs in the subqrySerialSearch}"

So I need to figure out how to create that set for the WHERE clause
I figured I can traverse the query using a loop and append each .[RMA] value to a string that I can use in formulating the WhereCondition.

OR

If that's a stupid idea, please suggest something more efficient.

HMMM...

Let's consider a different approach:

I'm presuming your report obtains its data directly from your table(s). If so, consider changing the data source to a query. The ability to filter becomes more flexible, because you can then have a query that looks something like this:

SELECT * FROM tblRMA WHERE [RMA] IN ( SELECT [RMA] FROM tblProduct WHERE [Serial] = [Forms]![FormName]![TextBoxName] );

This query should return what you're looking for.

Steve

[weasel7711]

Thanks Steve,
That actually sounds like a much better idea. I will try that out and reply with the results.

Edit. I did have a follow up question:
One reason why I chose using the reports is the ease of using "WhereCondition". On my search form I also have a power search that can search any number of multiple criteria using a few different listboxes. The code I created for a specific command button traverses those list boxes and dynamically creates a Where clause. Is there a similar property of Access queries such as "WhereCondition" that I can use when using DoCmd.OpenQuery?

Or possibly can you set the "RecordSource" property of the Report using VBA code?

-Weasel

[SteveF]

Thanks Steve,
That actually sounds like a much better idea. I will try that out and reply with the results.

Edit. I did have a follow up question:
One reason why I chose using the reports is the ease of using "WhereCondition". On my search form I also have a power search that can search any number of multiple criteria using a few different listboxes. The code I created for a specific command button traverses those list boxes and dynamically creates a Where clause. Is there a similar property of Access queries such as "WhereCondition" that I can use when using DoCmd.OpenQuery?

Or possibly can you set the "RecordSource" property of the Report using VBA code?

-Weasel

If you know how to dynamically create a WHERE clause, you'll have no problem dynamically creating an entire query. You can "push" a dynamically-created SQL query string into a named query, and use that query as the record source for your report. Here's how:


Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim SQLstr As String

Set dbs = CurrentDb

...
...
...

'build SQL query string using user-input parameters from form
'this will be very similar to building the WHERE clause you already have
SQLstr = "SELECT ... FROM ... WHERE ... ;"

'load the SQL string into the named query for the report
Set qdf = dbs.QueryDefs("NameOfReportQuery")
qdf.SQL = SQLStr
qdf.Close

'clean up workspace
Set qdf = Nothing
Set dbs = Nothing

...
...
...

Steve

[weasel7711]

I understand everything you have said thus far. However I am running into a problem in determining how to display the data. I have been playing around with different queries so I know what I will want my code to do in the end.

So far I have a subquery that joins the Product (RMA, Model, Serial) and the Model (Model, Manufacturer, Type) tables on the Model field:

qryJoinProdMod

SELECT RMA, tblProduct.Model, Serial, tblModel.Type, tblModel.Manufacturer
FROM tblProduct INNER JOIN tblModel ON tblProduct.Model=tblModel.Model;

The resulting table is
[RMA] [Model] [Serial] [Type] [Manufacturer]

I use this query in my main query, where I join the RMA table and the query qryJoinProdMod. So far:

qryWBMaster

SELECT tblRMA.RMA, Customer, POStatus, RepairStatus, Expedite, TgtDateOut, Problem, PONum, InvNum, DateIn, DateOut, qryJoinProdMod.Model, qryJoinProdMod.Serial
FROM tblRMA
INNER JOIN qryJoinProdMod
ON tblRMA.RMA = qryJoinProdMod.RMA

Naturally this returns a table with a lot of redundant data. I want to end up displaying it on the report similar to how tblRMA looks in datasheet view, with all its information one one line and a subdatasheet below it showing the linked tblProduct data for each RMA entry in the table.

I'm confused how to accomplish this because if I use this master query as the recordsource of the report, I will have multiple entries with the same RMA and many fields of redundant data. I want to compress this.

Thank you for your help so far.


Here's what I would like my report to show:
[RMA 1] [...] [...] [...] [DateIn] [DateOut]
[Model 1] [Serial 1]
[Model 2] [Serial 2]

[RMA 2] [...] [...] [...] [DateIn] [DateOut]
[Model 1] [Serial 1]

[RMA 3] [...] [...] [...] [DateIn] [DateOut]
[Model 1] [Serial 1]
[Model 2] [Serial 2]
[Model 3] [Serial 3]

[RMA 4] [...] [...] [...] [DateIn] [DateOut]
[Model 1] [Serial 1]
[Model 2] [Serial 2]

[weasel7711]

Come to think of it, I could just use the qryWBMaster (defined above) as the main search tool and just have an ordinary report/subreport architecture and let access do all the work of linking them by RMA field.

If I design a report so it looks a certain way, but I change the source control, will that make design changes and dynamically add/remove fields to my report? Or will it just evaluate the source and fill it whatever it can with the premade design?

[SteveF]

Sounds like now is the time you start to get into something new: setting up Groups in your report.

If you design a new report, you can have the Report Wizard set up a Group -- all you want at this point is to group by RMA.

If you're modifying an existing report, try this -- This is for Access 2010; I hope it's similar in 2007 (I kind of skipped that version):
In the Report Design tab, there's a button for Group and Sort--turn this on.
In the bottom section of the report design window, select Add a Group, choose the RMA field to Group By. Specify whether you want just a Group Header or Header and Footer. Footers are useful if you need totals on the detail fields for each RMA.

Back in the report design, place any fields you want to print just once per RMA in the RMA group header section. Keep the multiple lines (model, serial, etc.) in the detail section.

Try this out & see how it works.

Steve

[weasel7711]

Is the Group function in reports similar to using an SQL group by?

[weasel7711]

Steve,
That is exactly what I was looking for. Now I just have to plan my code.

One last question. For the items in the detail section. Is there a way to have them display below as a subsection? (See attached picture)

[SteveF]

As a subsection? Yes, sort of...

What I do to achieve that sort of layout is to move the detail field-name labels from the Report or Page Header into the Group Header, underneath the Header's data controls; then slide the detail fields around so they line up underneath.

There are alignment tools available so that you can have them line up exactly, if you wish.

Steve