Dear zetony-
I don't see anything right off the top. Try adding a Debug.Print of strWhere or
strSQL to see that you're getting what you expect.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of zetony95@ymail.com
Sent: Wednesday, November 30, 2011 5:54 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [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
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar