Filter on Load Report Based on A Form ComboBox

[bkyzer]

For my question here are the following:

Form Name: popup; Combo1 is the name of the Combobox I want to pass information to my report on load to filter the report output.
Query report is based on: Open Issues
Report Name: Active Issues by Physician (field I want to filter is Provider Name)

I have the form built and here is the code behind the cmd button set OnClick:

Private Sub Command6_Click()
 
 
On Error GoTo MyError
 
 
 
 
    DoCmd.OpenReport "active issues by physician", acViewReport
 
 
 
Leave:
 
 
    Exit Sub
 
MyError:
 
 
    MsgBox "Error " & Err.Number & ": " & Error$
    Resume Leave
 
       End Sub

I have also set my report to Yes for the Filter on Load property for the Report as the Selection Type. In the Filter property I have the following:

Provider Name=forms![popup]![combo1]

Currently, I'm getting my report output as if I have not selected anything in my ComboBox from the popup form.

Any and all help is much appreciated!!

[Seth Schrock]

What I would do is create a query that has the WHERE clause being the

[WHERE [Provider Name] = Forms!popup!combo1

Then you can base the report on that query and the results will be filtered every time.

Hint: It really helps to name your controls so that they tell you what they contain. For example, you could name your combo1: cboProviderName. That way whenever you see it in your code, you know which control you are referring to.

[bkyzer]

Thanks for the information. I've taken your advice and tried that route but I'm still hitting a stumbling block. Here is what I have in the Criteria field of the Query for the Provider Name:

=[Forms]![popup]![cboprovidername]

(I changed my referenced ComboBox to cboprovidername)

However, I'm still getting a blank report. I tried putting in manually one of the items that is in my combobox list directly into the WHERE statement and it worked beautifully for that one item typed in. Why is my ComboBox not talking to the query? That seems to me to be the issue?

Thanks!!

[Seth Schrock]

What is the row source for cboprovidername? If it is the name of a query, please attach the SQL code for that query.

Another method to troubleshoot what is happening, click on gray area to the left of row 9 in your OP where it is in the VBA editor. A read dot should appear. Then click your button. The code will stop at that point before it executes your code. In the Immediate window, type ?Me.cboprovidername (the Immediate window should be on the bottom of the VBA editor. If not, press Ctrl + G and it will appear). This will tell you the value that the combo box is passing to the query.

[bkyzer]

Here is the SQL from the row source of cboprovidername:

SELECT [Providers].[ID], Providers.[Provider Name] FROM Providers; 

In the meantime, I'm going to try the steps you've mentioned above with checking to see if the information is passing through.


Just tried the 2nd troubleshooting issue you mentioned and the immediate window retains ?Me.cboprovidername with no change or display of the value I selected in my cboprovidername ComboBox.

[Seth Schrock]

Check the Bound To Column property. It needs to be set to 1 if the query needs the ID field or 2 if it needs the [Provider Name] field. I'm guessing that since the criteria is

WHERE [Provider Name] = Form!popup!cboprovidername

that the query is looking for the text value (provider name) and not the number field (ID). If this is the case, then you need the Bound To Column to be 2, while the default is 1. Usually, queries are based on the primary key fields and not the text fields, so Access defaults to this design.

[bkyzer]

Brilliant!!! Thank You SO Much. That did it! I've been fighting this all afternoon.

[Seth Schrock]

Not a problem. Glad I could help. Keep what you've learned here. You will probably use it MANY times as you create/modify databases.

Back to the control naming thing, here is a link that tells the most commonly used naming: Naming Convention Just replace the X's with something that tells you what the control contains. Keeping spaces out of the names also helps when coding.