Combo Box for Report

[BLD21]

access 2007,

I have a report that is using a query as the control source. This report is based on many Account Names. I have a command button that when clicked will preview the report for printing. This works fine.

What I would like to do is have the ability to pick from probably a combo box just one Account Name in need be and print that selection rather than printing the whole report.

Not quite sure how to set this up. Do I need to create the combo box on my form and have the control source set to the query?

And how do I get the report to accept the selected account name?

Thanks to all!


Bruce D.

[pbaldy]

I'd go one of two ways. If you will always pick a name, I'd use this:

BaldyWeb wherecondition

If you want to be able to leave the combo blank to get everybody, I'd do this:

http://access.mvps.org/access/queries/qry0001.htm

Now that I think about it, a third option would be choosing multiple names:

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

[BLD21]

Paul,

Thanks, I think I am going to try and use the 3rd option. This should be helpful in getting me started.

Thanks!!
Bruce

[pbaldy]

Happy to help Bruce!

[BLD21]

Can anyone figure out what I might be doing wrong? When I click on my button to run the report I am receiving:

'Syntax error in query expression 'AccountName IN('Agents Choice)'.

My table name is Export_Billing
The unbound List Box is called lstAccountName with the row source of:

SELECT DISTINCT Export_Billing.AccountName FROM Export_Billing;
My Event Procedure for the button is:

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.lstAccountName.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Customer"
Exit Sub
End If
'add selected values to string
Set ctl = Me.lstAccountName
For Each varItem In ctl.ItemsSelected
'strWhere = strWhere & ctl.ItemData(varItem) & ","
'Use this line if your value is text
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "'"
Next varItem

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "Copy Of Departmental Data Report", acPreview, , "AccountName IN(" & strWhere & ")"
Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub

I am thinking somewhere in the above might be where the syntax is wrong.

Any ideas?
Thanks!!!

[BLD21]

I just found out I was missing the , in the ctl.ItemData(varItem) & "',"

It is working now but I thought this would let me select more than entry in the List box? Anyway I can live with this.

Thanks.

[pbaldy]

The code should let you choose multiple items. Make sure the multiselect property of the listbox is set to allow it.

[BLD21]

Paul,

Yes, that was it!!

Bruce D

[pbaldy]

No problem Bruce!

[PeteDC]

I'd go one of two ways. If you will always pick a name, I'd use this:

BaldyWeb wherecondition

If you want to be able to leave the combo blank to get everybody, I'd do this:

http://access.mvps.org/access/queries/qry0001.htm

Now that I think about it, a third option would be choosing multiple names:

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

I used the first option, but it went to the printer without first previewing the report. How would I create a "Preview Button" so I can look at the report prior to printing?