Its simply a case of executing the query in code behind the form, e.g. in the
Click event procedure of a button. I'm not clear from the information you've
given exactly how'd you'd want this to be set up, but here's one possible
scenario which allows the user to select one or more pallet numbers from a
list box and then assign them to one location.
1. Create an unbound dialogue from and add three controls, a list box
(lstPallets say), a combo box (cboLocations say) and a command button.
2. Set the List box's RowSource so it lists all distinct pallet number
values from the table:
SELECT DISTINCT [Pallet#]
FROM [YourTable]
ORDER BY [Pallet#];
3. Set the list box's MultiSelect property to Simple or Extended depending
on how you want the user to be able to make multiple selections.
4. Set the combo box's RowSource so it lists all locations:
SELECT DISTINCT Location
FROM [YourTable]
ORDER BY Location;
5. The real work is done in the button's Click event procedure as follows:
Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String
Dim varItem As Variant
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
Set ctrl = Me.lstPallets
If Not IsNull(Me.cboLocations) Then
If ctrl.ItemsSelected.Count > 0 Then
' loop through list of pallet numbers and
' update relevant rows in table for each
For Each varItem In ctrl.ItemsSelected
strSQL = "UPDATE [YourTable] SET Location = """ & _
Me.cboLocations & """ WHERE [Pallet#] = " & _
ctrl.ItemData(varItem)
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If
End If
' clear list box
For Each varItem In ctrl.ItemsSelected
ctrl.Selected(varItem) = False
Next varItem
' clear combo box
Me.cboLocations = Null
Set cmd = Nothing
Ken Sheridan
Stafford, England
Robert said:
Thanks ken for your help, but I need and easier way to do this. The warehouse
manager will be updating this info. Looking for a solution within a form, or
a basic query that list all pallets (once) and allow you to update that way.
Again this procedure will be done many time a week. thanks
Ken Sheridan said:
Execute an update query to update all rows where the pallet# = 1 to "VA", e.g.
UPDATE [YourTable]
SET Location = "VA"
WHERE [Pallet#] = 1;
I'd strongly advise backing up the table before executing any large scale
update operations like this.
Ken Sheridan
Stafford, England
Robert said:
I have a table that is imported weekly from an excel spread sheet. In this
table I track inventory by serial numbers, we have opened a second warehouse
and need to be able to assign the pallets location by pallet #. We import 10
pallets with 2000 records (weekly), I want to be able to say pallet 1 =VA,
and all pallet 1’s change to VA in the location field on all records.
Table
Serial #, Pallet#, Model, location
44455 1 xyz blank
