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!
' This is from the Search Form
Private Sub btnSearch_Click()
'run the search
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
'THIS IS THE CODE ON THE REPORTS OPEN EVENT
Public Sub openSearchReport(rsSearch As String)
Set grst = CurrentDb.OpenRecordset(rsSearch)
DoCmd.OpenReport "AllCompanies", acViewPreview
Set grst = Nothing