ACCESS FEED: [MS_AccessPros] I need a help with Multiple Selection Using a List Box

Rabu, 30 November 2011

[MS_AccessPros] I need a help with Multiple Selection Using a List Box

I'm writing an Access Database Program, which few of the fields will use List Box for multiple selections of the parameters (items). The code I'm working with now after several trials is not giving any error but when selection is made the items selected from the list box are not stored in the table.

Here is the code. This code is attached to the click event of a command button.

Guide to understand the statements in the code:

RunQueryEasement_Type => name for the cmd button

tbCounty_Easement => sub table for the list box

EASEMENT_TYPE => name for list box field

qryCOUNTY_EASEMENT => query table

I appreciate all your help in anticipation.

Private Sub RunQueryEasement_Type_Click()
On Error GoTo Err_RunQueryEasement_Type_Click

Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim VarItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tbCounty_Easement"

'Build the IN string by looping through the listbox
For i = 0 To EASEMENT_TYPE.ListCount - 1
If EASEMENT_TYPE.Selected(i) Then
If EASEMENT_TYPE.ItemData(i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & EASEMENT_TYPE.ItemData(i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [EASEMENT_TYPE] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qryCOUNTY_EASEMENT"
Set qdef = MyDB.CreateQueryDef("qryCOUNTY_EASEMENT", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCOUNTY_EASEMENT", acViewNormal

'Clear listbox selection after running query
For i = 0 To Me.EASEMENT_TYPE.ListCount - 1
Me.EASEMENT_TYPE.Selected(i) = False
Next i

Exit_RunQueryEasement_Type_Click:
Exit Sub

Err_RunQueryEasement_Type_Click:

If Err.Number = 6 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_RunQueryEasement_Type_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_RunQueryEasement_Type_Click
End If

End Sub