Ref a tab in form for Report

[yagerlegi]

Hello,



I have Form with a tabctrl and list on the tabctrl that are being populated using SQL statement. I want to print what is on the tabctrl list to a report. I added a list on the form and referenced forms!frm_city!tabctrl.list20 but it's not working. Please help.

[pbaldy]

The tab control is not part of the reference. Leave it out.

[yagerlegi]

I did.. Still not working. Here is what I have [Forms]![frm_city]!
[list20]

What should it be?

[pbaldy]

That looks okay:

Forms Refer to Form and Subform properties and controls

Is the listbox multi-select? If so, code would be required, like:

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

[yagerlegi]

Here is my code and its not working

Private Sub cmdOpenReport_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.lstzipcode.ItemsSelected.Count = 0 Then
MsgBox " Must select at least 1 zipcode"
Exit Sub
End If

'add selected values to string
Set ctl = Me.lstzipcode
For Each varItem In ctl.ItemsSelected
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 "Zipcode", acPreview, , "City_Zipcode IN(" & strWhere & ")"

Exit_cmdOpenReport_Click:
End Sub

[pbaldy]

What does "not working" mean exactly? Is the field text? If so, you need the other line from the link.

[yagerlegi]

It a list field the gets the information from a table. When i highlight the number and tell it openreport it give me a vba error on 3075 " Syntax error( missing operator) in query expression 'City_zipcode IN (32455)'.

Thank you so much for your help

[pbaldy]

That looks okay for a numeric data type. Can you post the db here?

[yagerlegi]

I think I got.. it was missing the single and double quote in the middle... strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

Thank you.. Now I need to count(city) the unique site header on report.. Thank you again

[pbaldy]

Happy to help. That's why I asked about the data type. The extra quotes are required if the data type is text.