MultiSelect List Box and some other issues

[snoopy92211]

I have a form, frmMain, that has the following items.

State checkbox and state combobox
divisions checkbox and divisions multiselect listbox.

The user is supposed to do several things.

1. check the state checkbox and/or division checkbox to see if they want to use that part of the query.
2. from there, the user is supposed to select a state and any x number of divisions to run a query.

in my form, the state combobox is populated by a query that lists each state. the divisions combobox is populated by a query that has the division name and abbreviation.

BUT...when the user selects for example, state: alabama and division 1234, I want the database to somehow use the state/division query and reference where I'm getting the information from, the main table, named tblMaster.


Please email or respond to me if you can help me...I'd really appreciate it. I'm a new coder and I'm struggling with this...
Here is the code: WHERE AM I GOING WRONG? I'm using Access 97.

Private Sub cmdRunQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim db As Database
Dim qdef As QueryDef
Dim i As Integer
Dim strsql As String
Dim strwhere As String
Dim strIN As String
Dim lstDivision As ListBox
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set db = CurrentDb()
strsql = "Select * from tblMaster"

For i = 0 To lstDivision.ListCount - 1
If lstDivision.Selected(i) Then
If lstDivision.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & lstDivision.Column(0, i) & "',"
End If

Next i

strwhere = " WHERE [tlbmaster.company_number] in (" & Left(strIN, Len(strIN) - 1) & ")"

If Not flgSelectAll Then
strsql = strsql & strwhere

End If

db.QueryDefs.Delete "qryCompanyNumber"
Set qdef = db.CreateQueryDef("qryCompanyNumber", strsql)

DoCmd.OpenQuery "qryCompanyNumber", acViewNormal

For Each varItem In Me.lstDivision.ItemsSelected
Me.lstDivision.Selected(varItem) = False
Next varItem

Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must select a Division from the list", , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

'If State is checked and Division is Checked

'If State is not checked and Division is Checked


If chkDivision = -1 And Me.lstDivision = "WHATEVER DIVISION THE USER CHOOSES" Then
selectstat = "Select *"
From = "tblMASTER"
Where = " WHERE (tblMaster.Company_Number) = (forms!frmMain!lstDivision)"
If Me.chkState = -1 Then
and1 = " AND ((tblMASTER.State) = (forms!frmMain!cboState))"

Else: End If
End Sub