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!

 

[OnTheFly]

Are you using multi-select list boxes or just single value select from the list boxes?

If they are the code gets a bit more complicated. Also, in order to provide code you would need to let us know if the key fields being returned from the list boxes are string or numeric values.



Hope this helps.

OnTheFly

 

[CSatGal]

They are multi-select boxes (i assume that means that you can choose more than one value?). As for the "key fields being returned", maybe examples would help...

(1)the first set of values in the first listbox are Satisfaction Codes (like "A11" or "SA12"). They have descriptions associated with them, too, but those aren't as important as the codes themselves. They're stored in a seperate satisfaction codes table and are linked to Customer Comments via a joining table (many-to-many).

(2)the second listbox has agent names. these names are stored in the Customer Comments table. They are formatted like John_Smith in the table (the way they are imported into the database) but i have converted them into FirstName and LastName fields in a query.

(3) the third listbox has various product lines. these are also stored in the Customer Comments table, and are alphanumeric (ie "6J", "83" or "KV").

Hope that's what you needed!

CSatGal

 

[OnTheFly]

I will give you code for one of the list boxes. Since they are all string values you will just have to create the other parts similarly. This is assuming your list box for Satisfaction Code is named lstSatisfactionCode and that the first column has the code value in it.

Dim strSatisfactionValues as String
Dim strAgentValues as String
dim strProductValues as String
Dim intListCt as Integer
Dim strAddQuote as String
Dim strFilter as String

strAddQuote="'"
strSatisfactionValues=””
strAgentValues=””
strProductValues=””
strFilter=””

For intListCt=0 to Me.lstSatisfactionCodes.ListCount – 1
If Me.lstSatisfactionCodes.Selected(intListCt) = True Then
If strSatisfactionValues=”” Then
strSatisfactionValues=strAddQuote & Me. lstSatisfactionCodes.Column(0,intListCt) & strAddQuote
Else
strSatisfactionValues= strSatisfactionValues & “, “ & strAddQuote & Me. lstSatisfactionCodes.Column(0,intListCt) & strAddQuote
End If
End If
Next intListCt

‘Finish the string value

If strSatisfactionValues<>”” then
strSatisfactionValues = "In (" & strSatisfactionValues & ")"
End If
….
….


Same for other list boxes. This is when the value you are extracting is in column 1 of the list box values. If you have only one column you can use the following instead of the column property Me.lstSatisfationCodes.ItemData(intListCt) to get the value selected.

To create the filter you will need to check to see if each string has a value

….
….

If strSatisfactionValues<>”” Then
strFilter = “[Satisfaction Code] “ & strSatisfactionValues
End If

If strAgentValues<>”” Then
If strFilter<>”” Then
StrFilter=strFilter & “ And [Agent Name] “ & strAgentValues
Else
StrFilter=”[Agent Name] “ & strAgentValues
End If
End If

If strProductValues<>”” Then
If strFilter<>”” Then
strFilter=strFilter & “ And [Product] “ & strProductValues
Else
strFilter=”[Product] “ & strProductValues
End If
End If

‘Open the report returning only the filtered records
DoCmd.OpenReport “YourReportName”, acViewNormal, , strFilter


Hope this helps.

OnTheFly

 

[CSatGal]

thank you so much! that's a huge help... I'm still getting an error message when I compile my database though... it points to this line in the code:


For intListCt=0 to Me.lstSatisfactionCodes.ListCount – 1

 

[OnTheFly]

What is the error message?

 

[CSatGal]

the error message says "compile error: syntax error

 

[OnTheFly]

Did you name your list box lstSatisfactionCodes? and are you entering the code in the module attached to the form that the list box control is on?

If not you will need to change the Me reference to the actual form reference

Forms!frmName.lstSatisfactionCodes.ListCount

Hope this helps.

OnTheFly

 

[CSatGal]

Ok, so now I'm getting a run-time error 2465- "Application-defined or object defined error" that points to
For intListCt = 0 To Forms!frmCSatMakeReport.lstSatisfactionCodes.ListCount–1
in the code....

I'm really sorry to be sush a pain- I have no idea what I'm doing. You're being a fantastic "coach" though- thank you!!

 

[OnTheFly]

Sorry, my mistake, try

For intListCt=0 To Forms!frmCSatMakeReport!lstSatisfactionCodes.ListCount-1

see if this helps. If it does you need to replace the Me. with the Forms!frmCSatMakeReport!



Hope this helps.

OnTheFly

 

[CSatGal]

Ok, that seems to be better now, but I'm getting a new error message... Run-time error 2465 "Microsoft Office Access can't find the field 'lstSatisfactionCodes' refered to in your expression"

Have I done something wrong? Here's the code I have right now...

Private Sub lstSatisfactionCode_BeforeUpdate(Cancel As Integer)
Dim strSatisfactionValues As String
Dim strAgentValues As String
Dim strProductValues As String
Dim intListCt As Integer
Dim strAddQuote As String
Dim strFilter As String

strAddQuote = "'"
strSatisfactionValues = ""
strAgentValues = ""
strProductValues = ""
strFilter = ""

For intListCt = 0 To Forms!frmCSatMakeReport!lstSatisfactionCodes.ListCount - 1
If Forms!frmCSatMakeReport!lstSatisfactionCodes.Selected(intListCt) = True Then
If strSatisfactionValues = "" Then
strSatisfactionValues = strAddQuote & Forms!frmCSatMakeReport!lstSatisfactionCodes.Column(0, intListCt) & strAddQuote
Else
strSatisfactionValues = strSatisfactionValues & "," & strAddQuote & Forms!frmCSatMakeReport!lstSatisfactionCodes.Column(0, intListCt) & strAddQuote
End If
End If
Next intListCt

'Finish the string value

If strSatisfactionValues <> "" Then
strSatisfactionValues = "In (" & strSatisfactionValues & ")"
End If

End Sub

 

[PHV]

What is the REAL name of the control ?
lstSatisfactionCode or lstSatisfactionCodes ?
Due to the name of the event procedure I guess you have to replace all occurrences of lstSatisfactionCodes by lstSatisfactionCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OnTheFly]

I see two issues. First, based on your first post, I don't think you want this code in the BeforeUpdate event of the list box. If three list boxes are involved you would want to have a command button or something to run the code so it can check all the list boxes before creating the filter.

Second, your list box is named lstSatisfactionCode and your code has lstSatisfactionCodes. That is why you are getting the error.



Hope this helps.

OnTheFly

 

[CSatGal]

Wow- this is turning out to be way harder than i imagined it would be. Thanks again for your patience with me, and your help!

I moved the code to the AfterUpdate event... and I fixed the lstSatisfactionCode problem... Thank you. I'm not getting an error message when I click on a value in the listbox now.

I'm not sure what to do next, though. How do I make a command button run code?? I know how to create command buttons to perform form operations and run queries and stuff- is if like that? And then how do i create a filter?

 

[OnTheFly]

Adding code to a command button is the same as you did to the list box. If you create the command button with the wizard option turned off you will get just the plain command button control. You want to place the code in the On Click event of the command button.

I would suggest that you move the code from the list box event to the command button event. Once there you will have to write the code for the other list boxes. It should be basically the same as the code for the lstSatisfactionCode starting with the For statement and substituting the appropriate list box control names for Agents and Products and creating the strAgentValues and strProductValues strings.

The information on creating and applying the filter to your report is in the second (blue) part of my earlier post.

Hope this helps.

OnTheFly

 

[FancyPrairie]

Well, here's another way of doing it (see FAQ faq181-5497). All you have to do to make it works is to create a new module, copy and paste the function from the FAQ into the new module and define the tag property as specified in the header of the FAQ. Don't worry about understanding the code. You don't need to.

What the code does is loops thru all of the controls on your form and returns the Where clause for you. It works for single and multi-select list boxes, combo boxes, text boxes, date ranges, option groups, and check boxes.

To open a report, all you have to do is type:

DoCmd.OpenReport "YourReport",,,BuildWhere(Me)

The key to making it work is setting the Tag property of each of the controls correctly.

What's nice about this function is that you can use it in other database you create without having to write custom code for each report criteria form you create.

 

[CSatGal]

Thanks for the advice, FancyPrairie. I took a look at the FAQ and copied the code into a new module. It's still not working, though. Maybe I didn't understand this whole Tags thing well enough?? I'm not sure. Or maybe I have to do something to connect the module to my form?

I'm really sorry to be such a dummy about this, but I have no idea what I'm doing!!

Would you mind giving me a bit of a step by step for how to make this work (other than cutting and pasting the code from that FAQ).

Thanks!!

 

[FancyPrairie]

Instead of trying to make all 3 list boxes work, let's just focus on 1. Once you get that working, the others will be done the same way.

I'm assuming you have created a new module and copied and pasted the functions from the FAQ and it compiled with no errors. Now create a query (using the Query Design Builder) that would be a valid query for your report and set the criteria of the query to one item that the user would select from the list box. Note that we're not actually using the list box at this point. We're just trying to get a query to run. So you are hardcoding the criteria, not referencing the list box on the form.

Once you have the query running correctly, switch to SQL view and check out the SQL code the Query Builder generates. The SQL code of your query might look something like this: Select * from ... Where [TableOfCodes].[Satisfaction Code] = 'SA12';

Note how the where clause looks. Now we can build the Tag property of the Satisfaction Code list box. In this case the Tag property of the Satisfaction Code list box would look like this: Where=[TableOfCodes].[Satisfaction Code],String

Now put a command button on your form and in the OnClick event, enter the following code:

Msgbox BuildWhere(Me)

Finally, select one or more items from the Satisfaction code list box and click on the command button you just put on the form. It should return your where clause without the word where.

In the above example I assumed that the Statisfaction code is stored as a string. Hence the ",String" argument in the Tag property. If it is a Long Integer, then change it to ",Long". (The comments in the BuildWhere function explains all of this.)

Once you have this one list box working, then do the same for the other 2.

The query for your report should be just like the one you created in the Query Builder above, without the where clause. Now open your report like this:

DoCmd.OpenReport "YourReportName",,,BuildWhere(Me)

 

[Phil4tektips]

This is a post I put up on the same issue. It does the job without little coding. I know to any programmer it will be cringe worthy, but my simple Access knowledge needs nurturing! Here ya go:

As I am relatively new to MS Access I didnt know how to solve this problem, but I came up with a solution. I just wanted to know if it is crazy and will cause problems in the future or if it is perfectly feasible.

By the way it is working perfect for me atm.

I have a main data table with many fields to keep records of risks. Lets say Joe Bloggs can report into the DB, he reports for a certain directorate, on certain projects. He may want to see a report on all the risks that affect his project, but only from his directorate. (Thats set the scene!)

A form has 7 combo boxes on it that are set to "All". Joe Bloggs therefore would select the project combo box and select his project, and select the directorate combo box and select his directorate. These fields populate a separate table (always one row).

When the user clicks on the 'enter' button a generic report is opened and the combo boxes are set back to 'All'. (In VBA Code I put this line - [combobox1] = "All") This works by a Query that calls up my (Main Data Table) and the table with the answers to the users selection (Functionality). The data is filtered in the query by using IIF statements in the criteria boxes. If the answer was 'All' it doesnt filter on anything. If the answer was a directorate/project then it filters the main table by that answer. Criteria box code for the project column:

IIf([Functionality]![Project lookup]="All",[Main Data table]![Project],[Functionality]![Project lookup])

This query then populates a general report which looks like a table. Joe Bloggs now has a list of everything affecting his project from his function at the current time.

The setup of the combo boxes menu means I have one generic query and one generic form.

What do you think?

If you keep getting errors with the code given to you, it might be worth giving this a pop!

-Phil4tektips-

 

[CSatGal]

Thanks once again for all of the great advice! I'm still stuck though...

I have followed FancyPrairie's latest advice, but I'm a little confused. First of all, how do I link that command button to the listbox?? I'm obviously missing something because after setting the tag property as advised, and creating that command button as advised, and trying to select items from my listbox and use the command button, all I get is an error message that says:

"Microsoft Office Access can't find the macro 'MsgBox BuildWhere(Me).'

The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under."

So... was I supposed to have saved the module that I created from the FAQ with a particular name? Do I have to do something else to the command button?

I'm still a little lost on how this command button will help create the query or report that I want too...

Sorry to be such an Access Dummy! Thanks so much for all of your help!