By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,361 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

Filter on Load Report Based on A Form ComboBox

P: 11
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command6_Click()
  2.  
  3.  
  4. On Error GoTo MyError
  5.  
  6.  
  7.  
  8.  
  9.     DoCmd.OpenReport "active issues by physician", acViewReport
  10.  
  11.  
  12.  
  13. Leave:
  14.  
  15.  
  16.     Exit Sub
  17.  
  18. MyError:
  19.  
  20.  
  21.     MsgBox "Error " & Err.Number & ": " & Error$
  22.     Resume Leave
  23.  
  24.        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:

Expand|Select|Wrap|Line Numbers
  1. 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!!
Aug 3 '12 #1

✓ answered by 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
Expand|Select|Wrap|Line Numbers
  1. 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.

Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
What I would do is create a query that has the WHERE clause being the
Expand|Select|Wrap|Line Numbers
  1. [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.
Aug 4 '12 #2

P: 11
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:
Expand|Select|Wrap|Line Numbers
  1. =[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!!
Aug 4 '12 #3

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Aug 4 '12 #4

P: 11
Here is the SQL from the row source of cboprovidername:

Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 4 '12 #5

Seth Schrock
Expert 2.5K+
P: 2,941
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 4 '12 #6

P: 11
Brilliant!!! Thank You SO Much. That did it! I've been fighting this all afternoon.
Aug 4 '12 #7

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Aug 4 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.