edrz01
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
0,0,1
Bound to Column 1 where the UID is located. In the String variable, the UID is located.
ASKER
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
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
I expected to see something like Forms("MyFormName")!cmb_Na meList.Col umn(1) rather than just cmb_NameList.Column so, based on Rey's comment:
strSelect = "[UID] = '" & Forms("MyFormName")!cmb_NameList.Column(1) & "'"
ASKER
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
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
Ed,
Are you sure the points should go to me and not Rey?
Are you sure the points should go to me and not Rey?
ASKER
Doug, yes you are correct... I didn't see his until now.. I'll try and figure out how EE can adjust.
Request attention at the bottom of your original post.
ASKER
Done and requesting Admin to award points to Rey. Thank you Doug.
ASKER
This solution worked very nicely. And since the UID is a text field I failed to enclose correctly.
Thank you very much Rey.
~Ed
Thank you very much Rey.
~Ed
u r welcome! glad to help.
Open in new window
what do you get?