David:
I think the method I suggested in my first reply is likely to be appropriate
here. What you'd need is an unbound form with a multi-select list box,
lstInterconnects say, which lists rows from the Interconnect table, a combo
box, cboLocations say, which lists the destination locations so the user can
select which the one to which to update the rows selected in the list box and
a button to execute the updates.
The list box's RowSource would be a query which lists the interconnect rows
in whatever order you consider most appropriate. There needs to be a way of
uniquely identifying each row, so I'll assume for now that the table is
called Interconnects and has, amongst others, a numeric primary key
InterconectID, e.g an autonumber, and a numeric column DestinationLocationID
which references the primary key of the Locations table. To keep things
simple for this example lets assume the list box list the InterconnectID and
the Destination Locations, the latter as a text column from the Locations
table, and is ordered alphabetically by that column. The RowSource would be:
SELECT InterConnectID, Location
FROM InterConnects INNER JOIN Locations
ON Location.LocationID = InterConnects.DestinationLocationID
ORDER BY Location;
Its BoundColumn property would be 1, its ColumnCount 2, its ColumnWidths
something like 2cm;4cm (experiment with the dimensions to get the best fit)
and its MultiSelect property would be Extended (this allows for Ctrl-Clicking
and Shift-Clicking). In reality you'd probably want more columns listed, but
that’s just a case of modifying the SQL statement and the control's
properties accordingly. If necessary you could build in a means of changing
the sort order of the list on the fly, which would be done by modifying the
control's RowSource property in code, for instance in the AfterUpdate event
procedure of a combo box from which the column on which to sort the list
could be selected.
Lets assume the combo box lists all locations alphabetically, but its value
will be the unseen LocationID corresponding to the text Location value. It’s
the LocationID which will be used in the code. For this the combo box's
RowSource would be:
SELECT LocationID, Location
FROM Locations
ORDER BY Location;
You might be able to restrict the list to only relevant locations rather
than listing them all by means of a WHERE clause in the query.
So, having a list box to select multiple interconnects from and a combo box
to select which destination location the selected interconnects are to be
updated to all that's now necessary is some code in a button's Click event
procedure to execute the updates. The way this will be done is to iterate
through all the selected items in the list box, which make up its
ItemsSelected collection, and to execute an SQL statement to update each one
to the location selected in the combo box, so the code would go like this:
Dim varItem As Variant
Dim strSQL As String
Dim ctrl As Control
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
Set ctrl = Me.lstInterconnects
' first make sure a location has been selected
If Not IsNull(Me.cboLocations) Then
' if one or more interconnect has been
' selected iterate through selected items
' and build and execute an SQL statement
' to update each selected row
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "UPDATE InterConnects " & _
"SET DestinationLocationID = " & _
Me.cboLocations & _
" WHERE InterConnectID = " & _
ctrl.Itemdate(varItem)
cmd.CommandText = strSQL
cmd.Execute
Next varItem
Else
MsgBox "No interconnects selected", vbInformation, "Warning"
End If
Else
MsgBox "No location selected", vbInformation, "Warning"
End If
Doubtless you'd need to modify this to suit your own scenario, but I hope
I've given you enough to show how it can be done. Let me know if you need
any further clarification. Don't forget that if you've included spaces or
other special characters in any table, column or control names the names need
to be wrapped in brackets [like this] when used in the code.
I've not included any error handling in the above code but that can always
be added later when its been tested. As always when developing routines for
set update operations like this it goes without saying that the table should
be backed up until the routine has been thoroughly tested. Even then regular
backing up of the data is essential of course.
Ken Sheridan
Stafford, England
David Grant said:
Ken,
Thanks for the reply. I understand the method you described (and you're
right about the VBA code... I'm experienced in other languages but not yet
VBA)
Here's the problem in its most basic form.
We have a table which contains, as its primary key, a Location ID. These are
locations within a building where audiovisual (AV) equipment resides. The
majority of IDs refer to peripheral equipment, such as ceiling speakers,
microphones, or projectors. There are typically a few locations where
central equipment resides, which peripheral equipment will connect to.
A second table, the Interconnect Table, connects Location IDs together and
gives cable quantities, cable types, etc.
An AV designer at our company (the database user) will typically browse
through the Interconnect Table and decide "hey, these particular peripheral
locations would be better off being wired to central location A rather than
B" His decision as to which ones, will be based on information not contained
in database.
We need to update destination Location ID fields in a large numbers of
records, which have no apparent common property (hence ruling out a simple
update query). It's technically possible to sequentially, one by one, change
the destination Location IDs but this would be very time consuming (and we'd
be better off sticking with Excel). Due to the nature of things, it is often
possible to sort the records such that large numbers of the records needing
updating are contiguous, hence why I want the SHIFT-click functionality.
I hope I've been clear.
Thanks for your time and help.
David
