?
Solved

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

Posted on 2010-03-25
18
Medium Priority
?
358 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:agilebiz
  • 9
  • 7
  • 2
18 Comments
 

Author Comment

by:agilebiz
ID: 28577659
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
 
LVL 42

Expert Comment

by:dqmq
ID: 28580560
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
 

Author Comment

by:agilebiz
ID: 28581458
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 42

Expert Comment

by:dqmq
ID: 28583896
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
 

Author Comment

by:agilebiz
ID: 28584838
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
 

Author Comment

by:agilebiz
ID: 28590737
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
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 28590753
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 28590843
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 28590903
Here is the sample database.
accarch129.zip
0
 

Author Comment

by:agilebiz
ID: 28590944
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 28591037
I think your relationships may be set up incorrectly.  What is the relationship between Quadrants and Divisions?
0
 

Author Comment

by:agilebiz
ID: 28591761
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 28592182
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 28592249
Can you define Quadrants and Divisions?
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 28592333
It's best to get these things figured out before going too far with database development.
0
 

Author Comment

by:agilebiz
ID: 28593208
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
 

Author Comment

by:agilebiz
ID: 28700986
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
 

Author Comment

by:agilebiz
ID: 28705412
Fixed it..

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

Thanks Helen.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question