List Box Code

[JHENDRYX]

Hi Everyone,

I am trying to create a query that will allow the user to selct mutiple items from three list boxes. The list boxes are as follows: Region, State, and ShowroomType. I would like the user to select one or many from each of the three lists.

I have a form set up with an "OK" button that the user would click on after making their selections. In the On Click property of the button, I have the following code:

Private Sub OK_Click()
' if nothing is selected, display message and exit.
If Me.Region.ItemsSelected.Count = 0 And _
Me.State.ItemsSelected.Count = 0 And _
Me.ShowroomType.ItemsSelected.Count = 0 Then
MsgBox "Select region(s), state(s), and showroom type(s)first."
Exit Sub
End If

Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String
Dim sRegion As String
Dim sState As String
Dim sShowroomType As String
Dim sCriteria As String
Dim varItem As Varient

'Build criteria string for selected Regions.
For Each varItem In Me.Region.ItemsSelected
sRegion = sRegion & "," & Me.Region.ItemData(varItem) & "'"
Next varItem
sRegion = Mid(sRegion, 2) ' remove leading comma.
sRegion = " Region in (" & sRegion & ")"

'build criteria string for selected States.
For Each varItem In Me.State.ItemsSelected
sState = sState & "," & Me.State.ItemData(varItem) & "'"
Next varItem
sState = Mid(sState, 2) ' remove leading comma.
sState = " State in (" & sState & ")"

'build criteria string for selected Showroom Types.
For Each varItem In Me.ShowroomType.ItemsSelected
sShowroomType = sShowroomType & "," & Me.ShowroomType.ItemData(varItem) & "'"
Next varItem
sShowroomType = Mid(sShowroomType, 2) ' remove leading comma.
sShowroomType = " Showroom Type (" & sState & ")"

'build SQL Statement.
If Me.Region.ItemsSelected.Count > 0 And _
Me.State.ItemsSelected.Count > 0 And _
Me.ShowroomType.ItemsSelected.Count > 0 Then
sCriteria = sRegion & "AND" & sState & "AND" & sShowroomType
Else
sCriteria = IIf(Me.Region.ItemsSelected.Count > 0, sRegion, sState, sShowroomType)
End If
SQL = " SELECT* " & _
" FROM tblProgramSummary " & _
" WHERE " & sCriteria

Set db = CurrentDb

'Delete query qryPicklist if exists.
On Error Resume Next
db.QueryDefs.Delete "qryPicklist"
On Error GoTo 0

'create and run query qryPicklist.
Set qDef = db.CreateQueryDef("qryPicklist", SQL)

DoCmd.OpenQuery "qryPicklist"

End Sub



When I click the "OK" button I get an error that highlights
Dim varItem As Varient and tells me User-defined type not defined. How do I fix this problem. I am not that familiar with code so I am kind of lost here.

Any help you could give me would be great.

Thanks,

Jennifer

 

[Tiro Cupidus]

'Varient' should be spelled 'Variant'

Edit:

p.s.
Jenny Hendryx... cool hehe

 

[JHENDRYX]

Thanks! That cleared up that problem, but now it highlights the IIf and gives me an error message that says Wrong number of arguments or invalid property assignment.

Sorry if these are stupid mistakes, but I copied code from a different thread and tried to adapt it to my database.

Thanks again,

Jennifer

 

[Tiro Cupidus]

Feel free to ask any questions at all. It's how we learn

Here is the format of an IIf function:
IIf(<condition>,<do this if true>,<do this if false>)

This:

IIf(Me.Region.ItemsSelected.Count > 0, sRegion, sState, sShowroomType)

Has too many commas.

 

[JHENDRYX]

Okay,

I am thinking I want to go in a little different direction. This will probably be more complicated, but i think it will look better. What I would actually like to do is create four forms. One form would give three command buttons (select region(s), select state(s), and select showroom type(s) and three text boxes along with a a command button for "OK" and a command button for "Cancel"

Then I would like to have a separate form for Region, State, and Showroom that would contain the actual list box.

When the select region(s) button is selected on the main form it would open up the region listbox form. The user could "Select All", "Deselect All", "OK" or "Cancel" using different command buttons.

This would be the same procedure for the state and the showroom type. Once all the items were selected, the user would hit OK on the main form and it would create the query.

I know this should complicated and if nobody can help me I completely understand.

I have included a couple of screen prints to kind of show what I am looking for.

Thanks,

Jennifer

 

[JHENDRYX]

Here is the second Listbox sample. This is what is would look like when the command button was selected on the main form.