Creating a Report from a Form with Listboxes in Microsoft Access 2

[CSatGal]

I'm attempting to design a form for an Access database that will allow users to choose one or more vlaues from at least 3 seperate listboxes (ie Product Line, Agent, Product, etc.). I know how to put listboxes into forms, but I have no idea how to take those forms and create a customized report based on the selected values... help!! I'm also somewhat of a VBA rookie, so please help me out with how to write the necessary code!!

Thanks!

 

[Phil4tektips]

I'm trying the same thing and getting the same error as CSatGal.

Although my solution up above works....I have to hardwire it in everytime. So FancyPrairie's Module is the way to go.

Nice work by the way FancyPrairie.

-Phil4tektips-

 

[FancyPrairie]

CSatGal, it sounds like you didn't copy and paste the function from the FAQ correctly. This is how it should have been done.

1. Create a new module
2. Highlight and copy the functions from the FAQ and paste them into the new module
3. Save the new module and name it something like basBuildWhere (note that the module name can not be the same as the function name).

That should take care of it.

Phil4tektips, I do something similar to what you do as far as adding the word ALL. I place a label above the list box that contains something like "All Employees" and set the background and foreground color of the label so it looks like it is highlighted. When the user selects an item in the list box, I call a function that checks to see how many items have been selected (I display that total in another label). If at least 1 item is selected in the list box, I unhighlight the "All Employees" label. When the user clicks on the label (All Employees), I call a function that sets all of the selected items in the listbox to false and set the other label to reflect that 0 items have been selected. It looks something like this:

+--------------------+
| All Employees |
|--------------------|
| John Smith |
| Justin Smith |
| .... |
|--------------------|
| (1 item selected) |
+--------------------+

Note there are 3 controls here. I name these controls something like "lblAllEmp", "lstAllEmp", "lblAllEmp_Total". By using a naming convention like this, the function that controls all 3 controls can be used for muliple list boxes.

 

[Phil4tektips]

Excellent advice.

Thats a neat little trick.
Cheers,
-Phil4tektips-

 

[CSatGal]

Hi FancyPrairie-

Thanks again for sticking with me!

I created a new module (again)- copied the code into it, saved it as basBuildWhere. then i went back to Access, opened the form, tried selected one or two SatCodes, clicked the command button, and I got the same error message. Is the button supposed to be 'connected' to a qeury/report? I'm still confused as to why I'm even using this button, or what I'm doing with it.

Thanks for all of the advice!

 

[FancyPrairie]

I just had you use the command button so that you can see what BuildWhere is returning to you so that you can verify that it is returning the correct information. In that same post it told you how to launch the report using BuildWhere.

Paste your code from the OnClick event here in tek-tips so we can see it.

 

[CSatGal]

Maybe I misunderstood you when i made the command button... I inserted the command button on my form, then I went into the properties for the command button> Event> On Click and typed exactly what you asked me to type:
MsgBox BuildWhere(Me)

Should this have been done in VBA? and if so, is there more to what I should have typed? If I add (under the command button's click event) MsgBox BuildWhere(Me) to the code, it tells me the name is ambiguous.

 

[FancyPrairie]

Ok, the reason the name is ambiguous is because your first copy and paste of the functions from the FAQ worked. So delete the last one you created (basBuildWhere).

Yes, you should have placed the code within the form module of the OnClick event (vba) of the command button. The onclick event of the command button should look like this [Event Procedure]. Then click on the ... button to the right. Access will bring up the module for the OnClick event. Just add the code "Msgbox BuildWhere(Me)" within the vba code.

 

[CSatGal]

Gotcha- thanks. The command button is now returning the BuildWhere info. how do i take this and get the query/report to work now??

 

[FancyPrairie]

Comment out the MsgBox statement and add the following code right below it

DoCmd.OpenReport "NameOfYourReport",acViewPreview,,BuildWhere(Me)

 

[CSatGal]

Thanks! i've got the report opening now when I click the command button... only problem I'm having now is that the report is blank. the headings, etc. are there, but no data seems to come up...

 

[FancyPrairie]

Does your report print results when you don't include the "BuildWhere(Me)" argument in the Docmd.OpenReport method? Is your report based on a query or table?

 

[CSatGal]

Yes- the report seems fine when I don't include the "BuildWhere(Me)" portion in the On Click event in the code.

The report is based on a query. As with (almost) all of the reports in this database, the data comes from more than one table (all related).

 

[FancyPrairie]

When you displayed the results of BuildWhere via the Msgbox, did the results look valid? If so, open in design view the query your report is using and enter the results of BuildWhere, via the message box, in the criteria section of the query builder and see if it works.

 

[CSatGal]

The results in the Msgbox displayed the primary key from the table from which the SatCodes are taken. This struck me as odd (I thought it should be displaying the SatCodes themselves), but I thought that since the primary key is also a column (hidden) in the listbox, that it would still work. Should the msgbox results have shown the codes themselves??

 

[FancyPrairie]

It all depends on your query. For now, forget about the list boxes, forget about your form, forget about your report. Just open the query, in design view, that your report is using and hard code a SatCode in to make it work. Your query should only show the records that contain that SatCode. When you have it working, switch the query builder to SQL view and examine the Where clause. Highlight and copy the SQL statement and paste it here so we can see it.

 

[CSatGal]

Here it is!

SELECT tblCDissatCategories.CDissatID, tblCDissatCategories.CDissatCode, tblCDissatCategories.MainCategory, tblCDissatCategories.SubCategory, tblCDissatCategories.Description, tblCustomerComments.ProductLine, tblCustomerComments.ResponsibleAgent, tblCustomerComments.CaseID, tblCustomerComments.ReasonForSatisfaction, tblCustomerComments.ReasonForDissatisfaction, tblCustomerComments.ReasonForDissatisfactionWithReachingXA, tblCustomerComments.ReasonForDissatisfactionWithAgent, tblCustomerComments.CustomerComments
FROM tblCustomerComments INNER JOIN (tblCDissatCategories INNER JOIN tblCDissatJoining ON tblCDissatCategories.CDissatID = tblCDissatJoining.DissatisfactionCode) ON tblCustomerComments.CustomerCommentsID = tblCDissatJoining.DissatReason
WHERE (((tblCDissatCategories.CDissatCode)="A13"))
ORDER BY tblCDissatCategories.CDissatCode, tblCustomerComments.ProductLine;

 

[CSatGal]

I've figured out where I went wrong... the report is working just fin now. Thank you so much for your help!!

 

[FancyPrairie]

The tag property of the listbox that lists the CDissatCode should look like this: Where=[tblCDissatCategories].[CDissatCode],String;

And the bound column of the list box is the column that contains "A13".

 

[CSatGal]

FancyPrairie- you mentioned creating an All label, and "calling a function" that checks to see how many items have been selected and displaying them in a label below the listbox. Would you mind explaining how to do this too? It sounds like a fantastic way to let users know (1) how many objects they select, and (2) that they can "select" all objects (even if it's by not selecting any of them!).

Thanks again for all of your help with the listbox/report. Your advice has been fantastic!

 

[FancyPrairie]

As I stated in my previous post, the key to make the 3 controls work together is having some kind of naming convention for the 3 controls. This is what I have chosen:

+---------------------+
| All Employees | Label: lblEmp_All
-----------------------
| List Item 1 | ListBox: lstEmp
| List Item 2 |
| List Item 3 |
| ... |
| List Item n |
|---------------------|
| (n items selected) | Label: lblEmp
+---------------------+

Note the basename of each of the controls is "Emp" (it can be anything you want). So, the control names are of this format:

lbl<basename>_All
lst<basename>
lbl<basename>

In the OnClick event of the label lblEmp_All type this: =ProcessListBox([lblEmp_All])

In the OnClick event of the label lblEmp type this: =ProcessListBox([lblEmp])

Then copy and paste the following code into a module within your form:

(NOTE: I stripped some code out of my procedure, so the following code has not been tested, but it should work, and you should get the idea).

Public Function ProcessListBoxClick(ctlCaller As Control, _
                           Optional varBackColor_All As Variant = 16777215, _
                           Optional varForeColor_All As Variant = 0)

'********************************
'*  Declaration Specifications  *
'********************************
    
    Dim frmListBox As Form                          'Active form
    
    Dim ctlActive As Control
    
    Dim lngForeColor_Off As Long                    'Color of the label when "All" is NOT selected
    Dim lngForeColor_On As Long                     'Foreground color of the label when "All" IS selected.
    Dim lngBackColor_Off As Long                    'Background color of the label when "All" is NOT selected
    Dim lngBackColor_On As Long                     'Background color of the label when "All" IS selected
    Dim lngColor As Long                            'Working variable
    
    Dim i As Long                                'Working variable
    
    Dim strName_LB As String                        'Name of list box control
    Dim strName_All As String                       'Name of label control appended with "_All"
    Dim strName_Total As String                     'Name of label control that shows totals
    
    Dim varItem As Variant                          'Working variable

'****************
'*  Initialize  *
'****************

    On Error GoTo ErrHandler
    
    Set frmListBox = Screen.ActiveForm

    Set ctlActive = ctlCaller
    
'*******************************************
'*  Determine the names of the 3 controls  *
'*******************************************

    If (InStr(ctlActive.Name, "_All") > 0) Then                                 'IFT, User clicked on "_All" label control
        
        strName_LB = "lst" & Mid(ctlActive.Name, 4, Len(ctlActive.Name) - 7)    '"lstName"
        strName_All = ctlActive.Name                                            '"lblName_All"
        strName_Total = "lbl" & Mid(strName_LB, 4)                              '"lblName"
    
    Else                                                                        'IFT, User clicked on list box
        
        strName_LB = ctlActive.Name                                             '"lstName"
        strName_All = "lbl" & Mid(strName_LB, 4) & "_All"                       '"lblName_All"
        strName_Total = "lbl" & Mid(strName_LB, 4)                              '"lblName"
    
    End If
    
'********************************************************************
'*  Determine the color of the "All" label when it is NOT selected  *
'********************************************************************

    lngForeColor_Off = ctlActive.ForeColor
    lngBackColor_Off = ctlActive.BackColor
    
    
'****************************************************************
'*  Determine the color of the "All" label when it IS selected  *
'****************************************************************

    lngBackColor_On = varBackColor_All
    lngForeColor_On = varForeColor_All

'**********************************************************
'*  If the "All" label was selected, then:                *
'*     1. Highlight the "All" label                       *
'*     2. Deselect all of items selected in the list box  *
'**********************************************************

    If ((strName_All = ctlActive.Name) Or (frmListBox(strName_LB).ItemsSelected.Count = 0)) Then
        
        frmListBox(strName_All).ForeColor = lngForeColor_On
        frmListBox(strName_All).BackColor = lngBackColor_On
    
        For Each varItem In frmListBox(strName_LB).ItemsSelected       'Deselect list box items
            frmListBox(strName_LB).selected(varItem) = False
        Next varItem
    
    Else
        

        frmListBox(strName_All).ForeColor = lngForeColor_Off
        frmListBox(strName_All).BackColor = lngBackColor_Off

    
    End If
    
'**********************************************************
'*  Display the number of items selected in the list box  *
'**********************************************************

    If (frmListBox(strName_LB).ItemsSelected.Count = 1) Then
        frmListBox(strName_Total).Caption = "(" & frmListBox(strName_LB).ItemsSelected.Count & " item selected)"
    Else
        frmListBox(strName_Total).Caption = "(" & frmListBox(strName_LB).ItemsSelected.Count & " items selected)"
    End If


'***********************
'*  Exit Subprocedure  *
'***********************

ExitProcedure:
    
    Exit Function
    
'****************************
'*  Error Recovery Section  *
'****************************

ErrHandler:

    If (Err.number = 2164) Then Resume Next
    
    MsgBox Err.Description, vbExclamation
    
    Resume ExitProcedure
    
End Function