Button to filter report based off combobox selection

[Neobeowulf]

Team,

I have a button called "Run Report" that is supposed to open up a report that is filtered by a selection made in a combo-box. Only 1 selection can be made in the combo-box and there isn't a blank space.

For example, if I pick "for signature" as my combo-box selection then hit my button, it should open my report filtered by reports "for signature".

So far I have:
DoCmd.OpenReport "Decorations Pending", acViewPreview, , "Status = 'get items in list99"

Everything past acViewPreview I have no idea about. I don't know how to tell access to filter the report based off the combo selection. :banghead:

Any assistance would be greatly appreciated.

 

[Alansidman]

Look at this video

http://www.datapigtechnologies.com/flashfiles/passparamtoreport.html

Alan

 

[pbaldy]

See if this helps:

http://www.baldyweb.com/wherecondition.htm

 

[Neobeowulf]

Okay I followed the link and I think I put the code in right. What happens though is that now when I push my button, it prints a blank report Here's the modified code:

DoCmd.OpenReport "Decorations Pending", , , "Status = '" & Me.List99 & "'"

Decorations Pending is the name of my report. Status is the column I'm wanting it filtered by, List99 is the name of my combo box.

Alan, I wasn't able to view the video due network restrictions

 

[pbaldy]

Set a breakpoint or put in a message box and make sure that what is returned by the poorly named combo box is what you expect. If it's actually a list box, make sure it's not set to multi-select.

 

[Neobeowulf]

Sorry, yes it's a list box. I checked and multiselect is set to "none". What do you mean set a breakpoint? I'm a rookie.

 

[pbaldy]

http://www.baldyweb.com/Debugging.htm

or as I mentioned, throw a message box in there temporarily.

 

[Neobeowulf]

Alansidman, that was EXACTLY what I was looking for. I watched the vid when I got home and it worked perfectly.

 

[pkappaz]

I have a similar problem. Refer to the code below. What happens is that while my variables contain the correct data, the WhereString is ignored by the DoCmd.OpenReport action. The DoCmd action always presents a dialog box where I can enter a value, then the report is fine. Is there something wrong with the syntax of the WhereString?


Dim WhereString As String
Dim ReportName As String
Dim myvariable As String

myvariable = Me.Combo14.Column(0)
Debug.Print myvariable

If IsNull(Me.Combo14) = False Then
ReportName = "Executive Project Dashboard"
WhereString = " OpCo = " & myvariable
Debug.Print WhereString
DoCmd.OpenReport ReportName, acViewReport, , WhereString
End If

 

[pbaldy]

The parameter prompt is Access telling you it can't find something. Make sure everything is spelled correctly. What is the data type of the field?

 

[pkappaz]

I'm not 100% clear on the syntax structure of using quotes (single/double) and & and # for string formatting. I figured out my first problem though, I was missing some quotes.

However, I have another formatting problem with the following line out of the code below

WhereString = " ENCS Lead = """ & myvariable & """"

My label "ENCS Lead" has a space, and this WhereString is invalid with the space. I'm sure there is a simple addition of a quote or something to correct it. Can you help me out?


Case 1
If IsNull(Me.Combo14.Column(0)) Then
DoCmd.OpenReport ReportName, acViewReport
Else
myvariable = Me.Combo14.Column(0)
Debug.Print myvariable
WhereString = " OpCo = """ & myvariable & """"
Debug.Print WhereString
DoCmd.OpenReport ReportName, acViewReport, , WhereString
End If

Case 2
If IsNull(Me.ITLead.Column(0)) Then
DoCmd.OpenReport ReportName, acViewReport
Else
myvariable = Me.ITLead.Column(0)
Debug.Print myvariable
WhereString = " ENCS Lead = """ & myvariable & """"
Debug.Print WhereString
DoCmd.OpenReport ReportName, acViewReport, , WhereString
End If

 

[pbaldy]

You have to enclose the field name in square brackets.

 

[pkappaz]

Thanks for your assistance. However, I get a "datatype mismatch in criteria expression" error on the openreport command. The where statement is,

WhereString = " [ENCS Lead] = """ & myvariable & """"

I have a watch set and it shows the following

myvariable value is "Jim Quinton"
WhereString value is "[ENCS Lead] = "Jim Quinton""

So there must be another syntax issue somewhere in the string.

 

[pbaldy]

Are you sure the value is text, rather than an ID number or something? The mismatch error is typically because of that. If it is text you might try

WhereString = "[ENCS Lead] = '" & myvariable & "'"

 

[pkappaz]

Here is the result from your suggestion. I get the same error.

myVariable value is "Jim Quinton"
WhereString value is "[ENCS Lead] = 'Jim Quinton'"

 

[pbaldy]

You didn't address the data type question. The string is correct for a text value.

 

[pkappaz]

The field [ENCS Lead] on the report has a bound column of ID, but displays the name. I setup my combobox to have the name and id, but use the ID as the bound column for the selection criteria against the [ENCS Lead] column in the report.

So I think I'm messed up on the data types. See attachment if you have a chance. Still trying to figure it out myself. Thanks so much for your help and information.

Phil

 

[pkappaz]

Figured it out. Had to set it to integer and adjust the quotes to compare [ENCS Lead to the integer. Thanks for your help.

 

[pbaldy]

No problem, glad you got it sorted out.

 

[Redous]

Look at this video

link removed....... newbie style

Alan

Hi,

I am hoping someone can help, I have use the above resource to get my report working with just one combobox

Outcome: Report shows desired filtered records.

I have multiple comboboxes that I want to have control the report.

Is there a way that this can be done?

In the Querie Editor i used the same process for the different fields.

Like [Forms]![DC1_Query].[Date] & "*"
Like [Forms]![DC1_Query].[Department] & "*"

Outcome: Report shows blank

Hope this makes sense and your help on this would be much appreciated i will continue to try and resolve the issue and if i do ever get around it i will post.