Open an Access Report using the field selection from a Combo Box

I have a request to embed a filter to open an Access report. I have created a new form with a combo box and the field to use. When I run my VBA command, I get a message "Enter Parameter Value". The note has the Selection in the Title area, but not the parameter box. If I enter the parameter manually, the report opens with that filter. If I remove the WhereCondition statement, the entire filter is bypassed. What am I missing?

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
    Dim strRptName, strSelect As String
    strSelect = "UID = " & cmb_NameList.Column(1)
         
    strRptName = "rptHelpDeskReport"
    DoCmd.OpenReport strRptName, acViewPreview, WhereCondition:=strSelect
   
Exit_cmdOpenReport_Click:
    Exit Sub
Err_cmdOpenReport_Click:
    MsgBox Err.description
    Resume Exit_cmdOpenReport_Click
End Sub
edrz01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
what is the Row Source of the combo box? what is the bound Column?

is the UID field a Text or Number data Type?

if Text use this

 strSelect = "[UID] = '" & cmb_NameList.Column(1) & "'"

if number

 strSelect = "[UID] = " & cmb_NameList.Column(1)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DougCommented:
With the form open and a selection made in the combo box, if you go to the immediate window and type:
?cmb_NameList.Column(1)

Open in new window

what do you get?
edrz01Author Commented:
3 fields are being used. ID, UID, Name
0,0,1
Bound to Column 1 where the UID is located. In the String variable, the UID is located.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

edrz01Author Commented:
To answer your question "what do you get"... I see the selected record in the combo box, the Person's Name of the UID record I need. The UID record is the log-in account name which is the Bound Field or Column(1).

See the attachment. This is the UID of the record selected in Column(1). What the user will see, is Column(2) the full name. IF I re-enter the UID, then the Report filters correctly.
Error.jpg
DougCommented:
I expected to see something like Forms("MyFormName")!cmb_NameList.Column(1) rather than just cmb_NameList.Column so, based on Rey's comment:

 strSelect = "[UID] = '" & Forms("MyFormName")!cmb_NameList.Column(1) & "'"

Open in new window

edrz01Author Commented:
Doug,
Here's what I did...

strSelect = "UID = '" & cmb_NameList.Column(1) & "'"

I had forgotten to add the ending parameter &"'" and seeing your solution jogged my memory 'what' I didn't apply. And because the field is a text field and not a numeric... the single apostrophes were forgotten. Duh...
Thank you. ~Ed
DougCommented:
Ed,

Are you sure the points should go to me and not Rey?
edrz01Author Commented:
Doug, yes you are correct... I didn't see his until now.. I'll try and figure out how EE can adjust.
DougCommented:
Request attention at the bottom of your original post.
edrz01Author Commented:
Done and requesting Admin to award points to Rey. Thank you Doug.
edrz01Author Commented:
This solution worked very nicely. And since the UID is a text field I failed to enclose correctly.
Thank you very much Rey.
~Ed
Rey Obrero (Capricorn1)Commented:
u r welcome! glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.