We help IT Professionals succeed at work.

Multi Select List Box - Validate Users Selected Values before Processing

creativefusion
on
428 Views
Last Modified: 2012-06-05
All,

I have a multi-select listbox containing records from a table/query.

My user can select any number of lines and mark them as processed. However, I need to perform a validation of the selected lines to ensure none of them contain a status of 9.

If 1 or more lines selected contain a status of 9, I want to stop the code from running and ask the user to review the lines they have selected again before trying again.

Here is my current code:

Private Sub Command3_Click()
On Error GoTo Err_Handler
'PROCESSING ALL THE LINES SELECTED BY THE USER

Dim uCode As Integer
Dim Status9Test As Boolean

'Column where status resides is column(8)

'Checking to see if user selected something
    With Me.lstReconBillerLoads
   
        If .ItemsSelected.Count = 0 Then
            'No record found prompt
            msg = "Could not find any lines for processing." & vbCrLf & "" & vbCrLf & _
            "Please select which lines you wish to process from the list."
            title = "No lines selected"
            style = vbOKOnly + vbInformation
            response = MsgBox(msg, style, title)
            Me.lstReconBillerLoads.SetFocus
        Else
            'check to see if the user has selected any lines with a status 9
            If Status9Test = True Then
                msg = "Either one or more of the lines you have selected contain a status code of 9 and cannot be processed." & vbCrLf & "" & vbCrLf & _
                "Please review which lines you have selected and try again."
                title = "Status 9 found"
                style = vbOKOnly + vbInformation
                response = MsgBox(msg, style, title)
                Me.lstReconBillerLoads.SetFocus
            Else
                'okay to proceed
                msg = "All of the lines you selected can be processed."
                title = "Status 9 not found"
                style = vbOKOnly + vbInformation
                response = MsgBox(msg, style, title)
                Me.lstReconBillerLoads.SetFocus
            End If
        End If
    End With

ErrorHandlerExit:
   Exit Sub

Err_Handler:
   If Err.Number = 0 Then
      Resume ErrorHandlerExit
   Else
    msg = "An unexpected error has been detected" & Chr(13) & _
    "Description is: " & Err.Number & ", " & Err.Description & Chr(13) & _
    "Please note the above details before contacting DLD support."
    title = "Support error messaging"
    style = vbOKOnly + vbInformation
    response = MsgBox(msg, style, title)
      Resume ErrorHandlerExit
   End If
End Sub



Can anyone help me with this?

CF
Database1.accdb
Comment
Watch Question

CERTIFIED EXPERT

Commented:
I suppose the obvious question is why you are including 'invalid' items in the list?

Commented:
If Status9Test = True Then


you need a function call which gives the line to a bvalidaiton function which returns if the selected line has an errorcode, if you do this like above it won't work.

Author

Commented:
@ peter57r

The lines that are available for processing come from multiple locations and are sales transactions that are flagged as unpaid. Within these transactions, there are some items that are flagged as status 9, which are new product codes that need to have their routes  updated before they can be processed for payment.

For efficiency, rather than create separate forms, I allow them to process status 9's on the fly using the same list box control and a combination of command buttons.

However, I need to stop the user from processing any payments (that are combined with status 0's).

@ MFlaig

Can you provide a sample peice of code that would help? I am a newbie at vb and still getting my feet wet :)

Thanks
CF

Author

Commented:
Is it at least possible to build a string with the values in the ItemsSelected collection? For example, I select 5 lines in total, 3 of them with 0's and 2 of them with 9's. Once I have the string, I can use the InStr function to look for 9's and then fail on that? Does that seem like a good idea?

I've been doing a bit of digging to find a solution and here is what I have found that sort of meets my needs.

Dim lngRow As Long
Dim strMsg As String
 
With Me.lstReconBillerLoads
    For lngRow = 0 To .ItemsSelected.Count
        If .selected(lngRow) Then
            If .Column(8) = 9 Then
            strMsg = strMsg & ", " & .Column(8, lngRow)
            End If
        End If
    Next lngRow
End With
 
' strip off leading comma and space '
If Len(strMsg) > 2 Then
    strMsg = Mid(strMsg, 3)
End If

Open in new window

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I had to find a solution on my own as the responses were not helpful.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.