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
agilebizAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

agilebizAuthor Commented:
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
0
dqmqCommented:
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]"
0
agilebizAuthor Commented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dqmqCommented:
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.

0
agilebizAuthor Commented:
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!
0
agilebizAuthor Commented:
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?
0
Helen FeddemaCommented:
I would not put anything into the report's Open event.  I like to create a concatenated filter based on selections from controls on a form, then created the filtered recordset in code, and apply it to the report before opening the report.  Here is some sample code:
[sample code fragment using the procedure]

   Dim dbs As DAO.Database
   Dim lngCount As Long
   Dim lngID As Long
   Dim rpt As Access.Report
   Dim rst As DAO.Recordset
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReport As String
   Dim strSQL As String
   Dim strTitle As String
   
   strRecordSource = "tblInventoryItemsComponents"
   strQuery = "qryTemp"
   Set dbs = CurrentDb

   'Numeric filter
   lngID = Nz(Me![ID])
   If lngID <> 0 Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[ID] = " & lngID & ";"
   End If

   'String filter
   strInventoryCode = Nz(Me![InventoryCode])
   If strInventoryCode <> "" Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[InventoryCode] = " & Chr$(39) & strInventoryCode & Chr$(39) & ";"
   End If

   'Date range filter from custom database properties
   dteFromDate = CDate(GetProperty("FromDate", ""))
   dteToDate = CDate(GetProperty("ToDate", ""))
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   'Date range filter from controls
   If IsDate(Me![txtFromDate].Value) = True Then
      dteFromDate = CDate(Me![txtFromDate].Value)
   End If

   If IsDate(Me![txtToDate].Value) = True Then
      dteToDate = CDate(Me![txtToDate].Value)
   End If

   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngCount
   If lngCount = 0 Then
      strPrompt = "No records found; canceling"
      strTitle = "Canceling"
      MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
      GoTo ErrorHandlerExit
   Else
      'Use this line if you need a recordset
      Set rst = dbs.OpenRecordset(strQuery)
   End If

   'Use SQL string as the record source of a form
   strFormName = "fpriLoadSoldPackingSlip"
   DoCmd.OpenForm FormName:=strFormName, _
      view:=acDesign
   Set frm = Forms(strFormName)
   frm.RecordSource = strSQL
   DoCmd.OpenForm FormName:=strFormName, _
      view:=acNormal
   
   'Use SQL string as the record source of a report
   strReport = "rptLoadSold"
   DoCmd.OpenReport ReportName:=strReport, _
      view:=acViewDesign, _
      windowmode:=acHidden
   Set rpt = Reports(strReport)
   rpt.RecordSource = strSQL
   DoCmd.OpenReport ReportName:=strReport, _
      view:=acViewNormal, _
      windowmode:=acWindowNormal
   'DoCmd.Save objecttype:=acReport, objectname:=strReport
   'DoCmd.Close objecttype:=acReport, _
      objectname:=strReport

=========================

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 6-Dec-2009

On Error Resume Next
   
   Dim qdf As DAO.QueryDef
   
   'Delete old query
   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   
   'Create new query
   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   
   'Test whether there are any records
   Set rst = dbs.OpenRecordset(strTestQuery)
   With rst
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   Else
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
   
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helen FeddemaCommented:
See my Fancy Filters sample database for more details on this technique.  Here is a screen shot of the form:
Fancy-Filters-Form.jpg
0
Helen FeddemaCommented:
Here is the sample database.
accarch129.zip
0
agilebizAuthor Commented:
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?
0
Helen FeddemaCommented:
I think your relationships may be set up incorrectly.  What is the relationship between Quadrants and Divisions?
0
agilebizAuthor Commented:
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.
0
Helen FeddemaCommented:
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.
0
Helen FeddemaCommented:
Can you define Quadrants and Divisions?
0
Helen FeddemaCommented:
It's best to get these things figured out before going too far with database development.
0
agilebizAuthor Commented:
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?  
0
agilebizAuthor Commented:
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

0
agilebizAuthor Commented:
Fixed it..

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

Thanks Helen.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.