Still having trouble running reports with multi select list boxes, and Happy New Year all!

[Gina Maylone]

Ok, let me see what I can do.

[Gina Maylone]

SampleDatabase010221.zipOk, here's a sample db. Sorry for the mess. Login info is on the main menu. Once you're in, go to reports and choose Comparison Report by Manufacturer. Another form will pop up. I've been using the same dates for both so I can verify data (10/1/20 - 10/31/20). Then select all in both bid status and mfg or just one or a couple. Currently, I have text boxes that get filled, but I'm not using them anymore. Just keeping them in case I need to go back that direction. Preview Report works, per your (Orange) assistance. The Summary and Detail at the bottom do NOT work. I made details available on the Summary report to verify data.

Eventually the date comparison will be different dates. To compare October to November for example. There are a bunch of hidden buttons on the form, all of my failed tries to get this to work!SampleDatabase010221.zip

Thanks again for your time and patience!

[orange]

Seems this may be a frontend/backend set up. When opening the database, I get an error saying the backend .mdb file is not available/invalid path???

[Gina Maylone]

Seems this may be a frontend/backend set up. When opening the database, I get an error saying the backend .mdb file is not available/invalid path???

It was, sorry, I thought I made the tables local. Here are the tables. sampledbtables.zip

[orange]

I can open this latest database, but no forms, reports or modules. Some of the tables do not have a primary key. So relationships(business rules) are unknown/incomplete.

[Gina Maylone]

I can open this latest database, but no forms, reports or modules. Some of the tables do not have a primary key. So relationships(business rules) are unknown/incomplete.

It's just the tables.

[Gina Maylone]

I think I've got it!

Private Sub Command50_Click()On Error GoTo Err_Handler
   Dim varItem As Variant      'Selected items status
    Dim varItem2 As Variant      'Selected items mfg
    Dim strWhere As String      'String to use as WhereCondition status
    Dim strWhere2 As String      'String to use as WhereCondition mfg
    Dim lngLen As Long          'Length of string status
    Dim lngLen2 As Long          'Length of string mfg
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.first report
    Dim strDoc2 As String        'Name of report to open.second report
    Dim strcriteria As String   'Combine strings
    
    
    strDelim = """"            'Delimiter appropriate to field type.
    strDoc = "BidReportbyMfrSummary"


    'Loop through the ItemsSelected in the list box.
    With Me.BidStatus
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'Build up the description from the text in the visible column. See note 2.
                'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
    
        With Me.MFG
        For Each varItem2 In .ItemsSelected
            If Not IsNull(varItem2) Then
                'Build up the filter from the bound column.
                strWhere2 = strWhere2 & strDelim & .ItemData(varItem2) & strDelim & ","
            End If
        Next
    End With
    
    
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "[bidstatus] IN (" & Left$(strWhere, lngLen) & ")"
        'lngLen = Len(strDescrip) - 2
'        If lngLen > 0 Then
'            strDescrip = "Status: " & Left$(strDescrip, lngLen)
'        End If
Debug.Print "1st String " & strWhere


    End If
    lngLen2 = Len(strWhere2) - 1
    If lngLen > 0 Then
        strWhere2 = "[mfg] IN (" & Left$(strWhere2, lngLen2) & ")"
        'lngLen = Len(strDescrip) - 2
'        If lngLen > 0 Then
'            strDescrip = "Status: " & Left$(strDescrip, lngLen)
'        End If
Debug.Print "MFG " & strWhere2
    End If
      strcriteria = strWhere & "and" & strWhere2
  'Report will not filter if open, so close it. For Access 97, see note 3.
'    If CurrentProject.AllReports(strDoc).IsLoaded Then
'        DoCmd.Close acReport, strDoc
'    End If
     Debug.Print "Strcriteria " & strcriteria
    'Omit the last argument for Access 2000 and earlier. See note 4.
    DoCmd.Openreport strDoc, acViewPreview, WhereCondition:=strcriteria
 


Exit_Handler:
    Exit Sub


Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
    End If
    Resume Exit_Handler
End Sub

So far I'm getting correct data! Now I'm trying to figure out if I can pass the criteria onto the sub report somehow. I'd like to keep them on one page if possible.

I thank you ALL for all of your help!!! You are the greatest!!

[Gina Maylone]

Ok, I have another question, if only BidStatus is chosen and no MFG, I get an error and no data. How can I include all when nothing is selected in the 2nd list box (mfg)?

Thanks!

[Gicu]

Hi Gina,
I would focus on this line of code:

'strcriteria = strWhere & "and" & strWhere2
strcriteria = strWhere & IIF(strWhere2="",""," and " & strWhere2) 'try this instead

Cheers,
Vlad

[Micron]

Downloaded to try to help but there is no event for command50 in the db so I substituted what you wrote in a copy of the form (was command44 perhaps) and then tried to compile your code but it will not. Unless someone spots the reason for your current issue, you might want to think about re-posting with a code project that will compile. As soon as I see that form code references don't match form control names, I think it's time to stop looking - unless it's my own db that I'm troubleshooting of course.

Having said that, I'll take a stab at it anyway.
You are adding "and" regardless if str2 contains anything or not.
strcriteria = strWhere & "and" & strWhere2

[orange]

@Micron,

Do you have a database with a form with listboxes of Gina's data?
If so can you post what you have so we can see the issue in context?
If not, then we have to get something concrete from Gina.
It's difficult to work with extremely limited info, but almost impossible when you have to guess at the business, the rules, the data...
I think we need a test database with actual form and events and related tables to experience the issue.

Just my $.02

[moke123]

It seems there is also a lot of extra code in there, presumably from various efforts to get things working. Makes it very difficult to sort out.
For instance the listbox has both a click event and a double click event

[Micron]

@orange, I downloaded the first link in post 17 (fe) and then 19 (be) and connected the tables. At least I think I've correctly stated which is which.
Agree, it's a bit cluttered but the main thing is, it doesn't compile. At least one reason for that is that a form control reference in code is wrong; there is no such control on the form.

Vlad, have to say I don't think your proposal will work if the first list is chosen from and the 2nd is not, and you know how users can be. I'd be trying a Select Case block if the idea is to allow one but not the other and either could be omitted. That is because as designed, the choices are

1 and 2
1 but not 2
2 but not 1
neither

[moke123]

I hate to be the bearer of bad news but I went through every module and added Option Explicit to those that were missing it.
I commented out every procedure that didn't compile which left very few procedures when done. Too many to count.

You really need to have option explicit declared at the top of EVERY MODULE.
If you look in the VBE and go to tools>Options, check off the box that says "Require Variable Declaration"
This will ensure that Option Explicit is added automatically.

Just to show an example of one error that occurred numerous times was

'Private Sub ProdGrp_AfterUpdate()
''add selected values to string
'Set ctl = Me.ProdGrp    <<<<<<<<<<<<<<<<<<<<<<<< You dont declare ctl as a control in a Dim statement 
'For Each varItem In ctl.ItemsSelected
'  strWhere = strWhere & ctl.ItemData(varItem) & " or "
'  'Use this line if your value is text
'  'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
'Next varItem
'
''trim trailing comma
'strWhere = Left(strWhere, Len(strWhere) - 4)
'Me.txtprodgrp = strWhere
'
''open the report, restricted to the selected items
'Me.Projects.Requery
'
'End Sub

that line could also have been written as

For Each varItem In Me.ProdGrp.ItemsSelected

[orange]

Thanks all,
Based on the comments and confusion, I think we have to wait for Gina to provide some solid information.
--Overview of requirements, some test data and test scenarios.

I don't think there is any long term value in patching -especially since it doesn't compile. Perhaps there is a test database Gina can supply and adjust systematically.
Thoughts?