access create a report from a form input
<p>I have a need to create a report from a user input on an Unbound form. </p>

<p>I have two tables where the data will come from:</p>

<pre><code>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)
</code></pre>

<p>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".</p>

<pre><code>CustNbr (being in the RMAMaster table)
PartNbr (being in the RMAItems table)
Date Opened &lt;start&gt; to &lt;end&gt; (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)
</code></pre>

<p>(Possibility to add more fields as the user sees fit)</p>

<p>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:</p>

<pre><code>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
</code></pre>

<p>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.</p>

<p>Thanks!</p>