CODEWITHSUNDEEP
ms-accessvbams-access-2003
cdomination
cdomination

Reputation: 603

Open correct reports based off of combobox selections

I'm using an Access database in order to store/organize all of the parts for each project. I have 2 comboboxes set up and working correctly: The first is the Customer Name, and once that is selected, the second populates the list of Serial Numbers (project ID numbers) from that Customer. Each project has several components associated with it, in which the reports need to be printed out.

The problem is that most of the component's details are listed in another separate table, so the component number will have to be cross referenced with the table in which it is located and then that report has to be pulled.

This is the table where the comboboxes pull their information from. First the Customer Name, and then the Serial Number associated with that Customer. From there, I want the report from the USER3 table with the associated CATALOG numbers.

enter image description here

For example, here are my combobox selections:

CustomerName - choosing CIPLA would give me SerialNumbers of either EF-1092,96, or 99

I pick Serial Number EF-1096 next - which has CATALOG numbers EDF50-00170 (in the MV table from USER3),EDF12-01114 (in the FIL table), and EDF12-00532 (isn't assigned a table).

So, for EDF50-00170, here's the MV Table with all of the information I want out of it (which the MV report automatically includes): enter image description here

And here's the corresponding report:

enter image description here

Now is the tricky part where the EDF50-00170, EDF12-01114, and EDF12-00532 reports need to be automatically pulled from the system instead of me having to go find everything manually. This would be the Manual Valve (shown above), Filter, and Misc reports respectively, that have ALL of the Catalog numbers for the category in them.

I already have all of the tables connected to their respective reports, such as this from a previous combobox setup, and I'm assuming this new one will have to be similar:

 If partnumberselect.RowSource = "MV" Then
     DoCmd.OpenReport "RPTManualValve", acViewPreview, , "CATALOG = '" & Me.partnumberselect.value & "'"

To summarize, I need a group of reports that are associated with a specific Serial Number. Thank you in advance! I'm sorry this is quite the long winded question.

Upvotes: 0

Views: 67

Answers (1)

SunKnight0
SunKnight0

Reputation: 3351

Dim ThisDB As DAO.Database
Dim d As DAO.Recordset
Dim q As String

Set ThisDB = CurrentDb
q = "SELECT CATALOG, User3 FROM [mainSourceTableName] WHERE SerialNumber='" & Me.[SerialNumberSelected] & "'"
Set d = ThisDB.OpenRecordset(q, dbOpenDynaset)
d.MoveFirst
Do While Not d.EOF
    Select Case d!User3
        Case "MV"
            Rem Print MV Report for d!CATALOG
        Case "FV"
            Rem Print FV Report for d!CATALOG
        Rem same for other tables/reports
        End Select
    d.MoveNext
  Loop  
d.Close

Example for preview with message box:

DoCmd.OpenReport "RPTManualValve", acViewPreview, , "CATALOG = '" & d!CATALOG & "'"
MsgBox "Test"
DoCmD.Close acReport,"RPTManualValve"

Upvotes: 1

Related Questions