Results 1 to 10 of 10
  1. #1
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25

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

    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.

  2. #2
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    Quote Originally Posted by weasel7711 View Post
    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

  3. #3
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25
    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

  4. #4
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    Quote Originally Posted by weasel7711 View Post
    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

  5. #5
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25

    Question

    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
    Code:
    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
    Code:
    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]

  6. #6
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25
    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?

  7. #7
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123

    A Grouped Report is the answer here

    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

  8. #8
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25
    Is the Group function in reports similar to using an SQL group by?

  9. #9
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25
    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)

  10. #10
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-23-2011, 09:01 PM
  2. Access 2003 automatic field entry
    By RANCHLAW56 in forum Forms
    Replies: 6
    Last Post: 12-30-2010, 02:57 PM
  3. add a new entry to the field in VB code
    By Qvatra in forum Programming
    Replies: 3
    Last Post: 12-25-2010, 03:28 PM
  4. Append Query using variables
    By hawg1 in forum Queries
    Replies: 2
    Last Post: 06-09-2010, 08:59 AM
  5. Replies: 0
    Last Post: 09-25-2006, 03:42 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums