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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Blueice13085Author 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.