Checking multiple values in textbox/list

[Vivirtruvian]

[SOLVED] Checking multiple values in textbox/list

Hi all,

First a bit of context: The data I am using relates to pallets with individual bags on them. Each pallet is identified by a unique number (like a SSCC number for people who may work in a relevant field). A pallet may be broken down into smaller parts and split across multiple orders such that:

x bags from pallet 12345 go on order A
y bags from pallet 12345 go on order B

When a pallet is split the unique pallet code cannot change for the sake of traceability (however in the database when a pallet is split, a new RECORD is created and that has a unique ID).

The abbreviated table data essentially looks like this:

[U]ID          SSCC         QTY          salesOrder[/U]
1           12345         10            00001
2           12345         30            00002

When a pallet (or part thereof) is assigned to a sales order, our admin staff attach the order number, a pick slip is created and the warehouse picks the stock. What I want to do is have a form for the warehouse where they will scan a pallet ID to confirm they have physically picked the correct pallet.

What I did was set up a form where the warehouse operator would enter a sales order, which would bring up a subquery with all the pallets for that order on it. When they scanned the pallet, if it is on the order it would be removed from the list, and if they had the incorrect pallet, a warning message would appear. I achieved this by having the following fields:

1. txtSalesOrder - user enters order to bring up pallets
2. txtPalletID - user scans the pallet ID into this field
3. txtCheckSO

Text box 'txtCheckSO' is not visible, and when a pallet is scanned into txtPalletID, an after update code performs a DLookUp function to see if that pallet has a sales order against it. If the sales orders in txtSalesOrder and txtCheckSO match, the pallet is confirmed and the operator moves on; if it does not match or is Null (ie. the pallet is not on ANY order) we get the warning message.

ONTO THE PROBLEM:
This all works fine however I realised that if a pallet has been split across TWO orders, this (simple, I know) method will not work as DLookUp will only return the first sales order attached to the pallet. What I need is for the form to check if any of the sales orders attached to the pallet match txtSalesOrder.

Any suggestions welcome. I have thought about simply swapping txtCheckSO to a list box and checking if any value in this list matches txtSalesOrder, but I am not to sure how to code that.

I also wondered if using RecordSets is a thing, but I also am completely self-taught with Access / VBA and I'm not sure if that is even applicable!

As always all help is greatly appreciated

 

[arnelgp]

ill go with the listbox thing.
after the txtSalesOrder has been entered, fill the listbox with related pallet records.

me.listPallet.rowsource = "select [palletID], [palletName], [qty], SONumber from " & _
"yourTable where SONumber = '" & me.txtSalesOrder & "'"

after scanning into txtPalletID, use the form's BeforeUpdate to validate if the pallet id is in the listbox:

Private Sub txtPalletID_BeforeUpdate(Cancel As Integer)
'assign the txtPalletID to listbox
Me.lstPallet = Me.txtPalletID
'If the ListIndex = -1 then the pallet is not in the sales order
'also if it is in the list, it will be highlighted on the list.
If Me.listPallet.ListIndex = -1 Then
    Cancel = True
    Msgbox "Pallet not in this Sales Order"
End If
End Sub

 

[Vivirtruvian]

Thank you arnelgp.

I added a little bit extra to the code to remove any compatible pallets from the list:

Private Sub Text4_BeforeUpdate(Cancel As Integer)
'assign the txtPalletID to listbox
Me.List13 = Me.Text4
'If the ListIndex = -1 then the pallet is not in the sales order
'also if it is in the list, it will be highlighted on the list.
If Me.List13.ListIndex >= 0 Then
DoCmd.OpenQuery "qryPickConfirm"
Me.List13.Requery
ElseIf Me.List13.ListIndex = -1 Then
    Cancel = True
Playsound ("C:\Program Files\Chronicle One Online\assets\media\error.wav")
MsgBox "Pallet does not exist on this outbound delivery.", vbCritical + vbOKOnly, "Invalid Pallet"
End If
End Sub

Thanks again!