Ms Access Listbox

I have a Multi select list box I use to pull data for a report, what I need now is to make a button that will open a form based on the selection from the list box using VehicleID, but on click it needs to check that at least one is selected and no more the one is selected
Blueice13085Asked:
Who is Participating?
 
Blueice13085Connect With a Mentor Author Commented:
Think I got it


Private Sub cmdEditVehicles_Click()
    If Me.lstVehicles.ItemsSelected.Count = 0 Then
        MsgBox "You must select at least 1 item from the listbox", vbInformation
        Me.lstVehicles.SetFocus
        Exit Sub
     ElseIf Me.lstVehicles.ItemsSelected.Count > 1 Then
            MsgBox "You must ONLY select  1 item from the listbox", vbInformation
        Me.lstVehicles.SetFocus
        Exit Sub
    Else
On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the list box.
   
    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
   
    'strDelim = """"            'Delimiter appropriate to field type. See note 1.
    strDoc = "frmAddEditVehicles"

    'Loop through the ItemsSelected in the list box.
    With Me.lstVehicles
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
   
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "[VehicleID] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "Categories: " & Left$(strDescrip, lngLen)
        End If
    End If
   
    'Report will not filter if open, so close it. For Access 97, see note 3.
    If CurrentProject.AllForms(strDoc).IsLoaded Then
        DoCmd.Close acForm, strDoc
    End If
   
    'Omit the last argument for Access 2000 and earlier. See note 4.
    DoCmd.OpenForm strDoc, acNormal, WhereCondition:=strWhere, OpenArgs:=strDescrip

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdEditVehicles_Click"
    End If
    Resume Exit_Handler
    End If
End Sub
0
 
IrogSintaCommented:
Just curious, why is your listbox set to multi-select if you only want the user to select one item?
0
 
Blueice13085Author Commented:
cuz I have another button that opens a report and I want to select more then one for the report
0
 
Blueice13085Author Commented:
Figured out on my own
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.