Can you clarify your goal? Your code is not designed to store anything in a table. A picture of what John suggested:
http://www.baldyweb.com/ImmediateWindow.htm
If you're trying to save values:
http://www.baldyweb.com/MultiselectAppend.htm
Paul
--- In MS_Access_Professionals@yahoogroups.com, "zetony95@..." <zetony95@...> wrote:
>
> 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
>
Tidak ada komentar:
Posting Komentar