Access 2003 - Help with Make-Table and Append queries

[tim.link]

I maintain an equipment inventory database in Access 2003 with just a
handful of tables and several regular queries. Each piece of equipment
has a unique 6-digit asset tag, and that field is the primary key. No
two records can have the same asset tag. Periodically equipment gets
replaced, and I am looking for a way to do the following when
replacing multiple pieces of equipment.

1. Multiple asset tags would be entered into a form or table. Their
corresponding records would be found in TABLE-1
2. Certain fields of the records found would be appended to TABLE-2
showing them as having been replaced
3. The found records would then appear in a table, query, or form
where they could be modified -- (of the twenty total
fields, only four - Make, Model, Asset tag, Serial number - would
be modified with new data)
4. The four modified fields would then be appended to the original
record in TABLE-1 leaving all other fields untouched

I have experimented with Make-Table, Append, and Delete queries as
well as with macros. But, I do not know enough to make this all happen
the way I want it to happen. I have also entered criteria that will
prompt me for the asset tag field in a record. But, this only allows
me to enter one record at a time - which would be okay if there is no
way to do this with multiple records simultaneously. Any help would be
greatly appreciated.

 

[Ken Sheridan]

I can't see why you need a second table at all; why not just open a form
bound to the first table, filtered to show only the rows selected as '
replaced' in an unbound dialogue form? The bound form need only show the
four columns which need amending, though you could also show other columns in
the form and lock them to prevent inadvertent changes being made to their
data.

For the dialogue form I'd suggest using a multi-select list box so that
multiple asset tags could be selected, and a button to open the bound form.
The RowSource for the list box would be along these lines:

SELECT [Asset Tag] FROM [Table-1] ORDER BY [Asset Tag];

The code in the button's Click event procedure would iterate through the
list box's ItemsSelected collection and build a string expression for use as
the WhereCondition of the OpenForm method, so would be along these lines,
where lstAssetTags is the name of the list box:

Dim varItem As Variant
Dim strAssetTagList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstAssetTags

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
' build value list. Numbers assumed; see below for code if text
strAssetTagList = strAssetTagList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strAssetTagList = Mid(strAssetTagList, 2)

strCriteria = "[Asset Tag] In(" & strAssetTagList & ")"

' open bound form
DoCmd.OpenForm "frmRelacements", WhereCondition:=strCriteria

'close dialogue form
DoCmd.Close acForm, Me.Name
Else
MsgBox "No items selected.", vbInformation, "Warning"
End If

This assumes the Asset Tag column in the table is of number data type; if
its text amend the code as follows:

strAssetTagList = strAssetTagList & ",""" & ctrl.ItemData(varItem) &
""""

Ken Sheridan
Stafford, England

 

[tim.link]

I can't see why you need a second table at all; why not just open a form
bound to the first table, filtered to show only the rows selected as '
replaced' in an unbound dialogue form? The bound form need only show the
four columns which need amending, though you could also show other columns in
the form and lock them to prevent inadvertent changes being made to their
data.

Ken, Thank you for your quick response. Regarding the second table,
maybe I
wasn't as clear as I should have been in my original post. The second
table
(TABLE-2) already exists in my database and is basically a list
showing all of
my old equipment surplus.

When the asset in TABLE-1 is replaced, a few fields from that record
need to be
added to TABLE-2 to show which assets were replaced on what date. So,
I just
thought that since I would be entering the old asset tag numbers once
at the
onset, it would be nice for the information to be copied into TABLE-2
during the
process.

What I would be left with is TABLE-1 containing the new, updated
asset
information and TABLE-2 containing the asset information of the
equipment
which was replaced.

 

[Ken Sheridan]

You could expand on what I suggested so that each record in the bound form is
appended to table 2 before you edit it, using a query such as:

INSERT INTO [Table 2]
(Make, Model, [Asset tag], [Serial number])
SELECT Make, Model, [Asset tag], [Serial number]
FROM [Table 1]
WHERE [Asset tag] = Forms![YourForm]![Asset tag];

I'm not sure from your post whether these are the only columns to be
inserted into table 2 or the only columns to be amended, but its simply a
question of adding to the two column lists in the query if there are more.

You can run the query from the Current event procedure of the bound form.
This will insert a row into table 2 as you navigate to each record in the
form. You could make the execution of the query conditional on the response
to a message box if you want user confirmation before inserting each row into
table 2. This would help prevent any accidental insertion of a row into
table 2 if you should find you've inadvertently selected an item not to be
replaced when making the multiple selections in the list box in the dialogue
form.

Ken Sheridan
Stafford, England