asked on

How to populate Access report with a search form that creates a recordset?

Hi,

I am working on a small application that allows a user to search a table with Company details in it. Attached is what the search form looks like, the relationship diagram and the report in design view.

I am trying to figure out how to populate the report once the user selects the Search button from the form. My issue is passing the record set into the report and having the report fill the two list boxes with the numerous quadrants and divisions.

An example of the SQL that is produced when the user selects 1 and 2 for the division and 1 for the quadrant is:
SELECT DISTINCT CompanyList.ID, CompanyList.CompanyName, CompanyList.Address, CompanyList.Phone, CompanyList.Fax, CompanyList.Contact, CompanyList.Email, CompanyList.Rating, CompanyList.Comments FROM (CompanyList INNER JOIN cDivision ON CompanyList.ID = cDivision.cID) INNER JOIN cQuadrant ON CompanyList.ID = cQuadrant.cID WHERE (cDivision.DivisionNum = 1 OR cDivision.DivisionNum = 2) AND (cQuadrant.QuadrantNum = 1)

The attached code is how I pass the recordset to the report.

I am able to get all the fields to populate except the list boxes for the Quadrants and Divisions. I need to find out how to use the ID of each company in the recordset so i can populate the list boxes with the proper quadrants and divisions. I tried putting code in the on load event of the report to use the txtID field but this populates the list boxes for each company with the data for the first company record.

If I am not clear enough I will try and further explain what I mean.

Thanks for any help!

Pat

' This is from the Search Form
Private Sub btnSearch_Click()  
    'run the search
    Call RunSearch
    
End Sub

Sub RunSearch()
    Dim strSQL As String
    
    'get the SQL statement for the search
    strSQL = GetSQL
    
    'if the SQL statement was generated successfully
    If strSQL <> "ERROR" Then
        'execute the SQL statement against the
        'database and put results in recordset
        Set rsSearch = ProcessRecordset(strSQL)
    
        'THIS IS WHERE THE RESULTS NEED TO BE SENT TO THE REPORT
        openSearchReport (strSQL)
    
    End If
    
End Sub

'THIS IS THE CODE ON THE REPORTS OPEN EVENT
Public Sub openSearchReport(rsSearch As String)
  Set grst = CurrentDb.OpenRecordset(rsSearch)
  DoCmd.OpenReport "AllCompanies", acViewPreview
  grst.Close
  Set grst = Nothing
End Sub

Open in new window

companySearch.JPG
Relationship.JPG
Search-Report.JPG

agilebiz

ASKER

This image is of the results after the search. You can see the divisions and quadrants are the same for each Company. Currently I have the Row source for the Quadrant and Division list box set to this:

Division List box:
SELECT cDivision.DivisionNum FROM cDivision WHERE cID=Reports!SearchedReport!txtID;

Quadrant List box:
SELECT cQuadrant.QuadrantNum FROM cQuadrant WHERE cQuadrant.cID=Reports!SearchedReport!txtID;
Report-Preview.JPG

dqmq

Go to the properties of the listbox control and select the data tab. In rowsource type choose "Table/Query". In Row Source put "Select DivisionNum from cDivision where cID = [ID]"

agilebiz

ASKER

Thanks for the response dqmq. I tried this but it still returned the same values as the image i posted. Its linking each company to only one company ID

dqmq

I apologize for my overly simplistic answer, which I agree does not work. Upon reflection I think the solution is to put each of the list boxes inside a sub-report control. You can then base the sub-report on the child table and link it to the id column in the main report.

agilebiz

ASKER

I beleive I did what you said. I created a sub report and put it into the main report. I used the same SQL statement as you showed about but the results are still the same? I may have missed something, please let me know if what I did was correct.

Thanks for the help!

agilebiz

ASKER

I just realized my report was not opening properly. What do I have to do to get my report to open with the data from the recordset that is created when the user selects the different searching criteria? How do I get the text boxes in the report to populate with each column in the recordset if I cannot actually set the Control souce of the textboxes?

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Helen Feddema

See my Fancy Filters sample database for more details on this technique. Here is a screen shot of the form:
Fancy-Filters-Form.jpg

Helen Feddema

Here is the sample database.
accarch129.zip

agilebiz

ASKER

The open event of the report look like this...

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = strSearchSQL
End Sub

An example of the strSearchSQL is as follows:
SELECT DISTINCT CompanyList.ID, CompanyList.CompanyName, CompanyList.Address, CompanyList.Phone, CompanyList.Fax, CompanyList.Contact, CompanyList.Email, CompanyList.Rating, CompanyList.Comments FROM (CompanyList INNER JOIN cDivision ON CompanyList.ID = cDivision.cID) INNER JOIN cQuadrant ON CompanyList.ID = cQuadrant.cID WHERE (cDivision.DivisionNum = 1 OR cDivision.DivisionNum = 2) AND (cQuadrant.QuadrantNum = 1)

How do I link each text box with each item selected in the SQL statement?

Helen Feddema

I think your relationships may be set up incorrectly. What is the relationship between Quadrants and Divisions?

agilebiz

ASKER

The logic behind the database is that there is a list of Companies each company can have numerous Quadrants associated with it as well as Divisions.. To be honest I wasn't sure of how to set this up properly and dont have the best skills in database development.

Helen Feddema

It sounds to me as if Quadrants contain Divisions (or the other way around). I don't know which -- that would be a business decision. But if (for example) Quadrants contain Divisions, and Divisions contain Companies, then you need a one-to-many relationship between Quadrants and Divisions, and a one-to-many relationship between Divisions and Companies.

Helen Feddema

Can you define Quadrants and Divisions?

Helen Feddema

It's best to get these things figured out before going too far with database development.

agilebiz

ASKER

What we were looking for was a simple database application that keeps track of construction companies. Each company can have up to 3 Divisions but has at least 1. There are 16 different construction divisions to select from (1-16). Each company can have up to 7 different quadrants but has at least 1. There are 7 different quadrants. Quadrants are the locations within our province. Does this help explain a little what the divisions and quadrants are?

agilebiz

ASKER

Thank you for your comments Helen! This code actually got the report to work.

The only question I have is how do you set it so the report doesnt print automatically? Right now it opens the report and sends it directly to the printer, I am trying to get it so it shows up then the user can select print.

Again, thanks a lot for the sample code and the help!

strReport = "SearchedReport2"
        
        DoCmd.OpenReport ReportName:=strReport, _
            View:=acViewDesign, _
            windowmode:=acHidden
            
        Set rpt = Reports(strReport)
            
        rpt.RecordSource = strSQL
        
        DoCmd.OpenReport ReportName:=strReport, _
            View:=acViewNormal, _
            windowmode:=acWindowNormal

Open in new window

agilebiz

ASKER

Fixed it..

Changed View:=acViewNormal, _ to View:=acViewPreview, _

Thanks Helen.