access create a report from a form input

Question

I have a need to create a report from a user input on an Unbound form.

I have two tables where the data will come from:

Table Name: RMAMaster
Fields:
RMANbr (5 digit number)
DateIssued (date in MM/DD/YYYY format)
CompanyName (text)
CustNbr (6 digit number)

Table Name: RMAItems
Fields (that are relevant for the search):
RMANbr (relationship established to RMAMaster.RMANbr)
PartNbr (text)
Warranty (Yes/No)
Disposition (lookup from another table)

The form (Name: RMA Report Search) has the following Controls on it, the idea being that the user can fill in any number of fields. Blank fields would mean "Search all for that field" and filled in fields would be "search for the data in those fields".

CustNbr (being in the RMAMaster table)
PartNbr (being in the RMAItems table)
Date Opened <start> to <end> (being in the RMAMaster table)
Disposition (being in the RMAItems table)
Warranty (drop down with choice "Yes", "No" and "Any") (being in the RMAItems table)

(Possibility to add more fields as the user sees fit)

For example, I enter in Part number "G-5645" and leave "Warranty" as "any" and it will search for all the RMA's that have the part number "G-5645" and display them in the report. the report would be paid out sort of like this:

RMAMaster.RMANbr    RMAMaster.DateIssued    RMAMaster.CustNbr, RMAMaster.CompanyName
|
|-- RMAItems.PartNbr    RMAItems.Warranty   RMAItems.Disposition
|-- RMAItems.PartNbr    RMAItems.Warranty   RMAItems.Disposition
|-- (possibly repeated depending on how many items the customer sent back

I can build the report but I'm not wrapping my head around getting the data from the Form RMA Report Search and build a Report off of it. Do I use a Query? VBA Code that calls the report? Not sure how I should go about doing this. Any help would be appreciated.

Thanks!

RyanL RyanL · Accepted Answer · 2016-01-22T18:03:25

If I understand you, you want to 'build' a report based on a search form. If that's correct, you don't need to build a report at run-time, but instead build a report based on a query that combines the fields you'd ultimately want to display from RMAItems, RMAMaster tables. The recordsource for this report should be this query.

Next, from the search form a user would select all applicable fields and open the report (maybe a button-click)? This button-click would generate a WHERE statement based on the selected fields. Rpt_Results would be the report you want to generate, stLinkCriteria is the criteria you want to pass. Iteratively build the linking criteria by adding to stLinkCriteria.

Dim stLinkCriteria As String
dim stDocName as String


 stDocName = "RPT_Results"

stLinkCriteria = "[" & [PARTNumber] & "]=" & forms!FRM_Search.PartNumber

... 

DoCmd.OpenForm stDocName, , , stLinkCriteria

access create a report from a form input

1 Answers