asked on

Multi Select List Box - Validate Users Selected Values before Processing

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

peter57r

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

MFlaig

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.

creativefusion

ASKER

@ 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

creativefusion

ASKER

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

ASKER CERTIFIED SOLUTION

creativefusion

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

creativefusion

ASKER

I had to find a solution on my own as the responses were not helpful.