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" wrote:
> 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" wrote:
>
> > 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" wrote:
> >
> > > 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
> > >
> > >
> >
|
Similar Threads
