Add Values to Lookup table INSERT INTO Values/SELECT

[GoCoogs]

I have a form where the user can create an activity that staff are
required to complete. I have created a lookup table called
ActivityStaff that will contain which staff members are responsible
for which activities.

After the activity has been created it has an ActivityID in the
Activity table. The staff are listed in the Staff table. I want to
Insert this activityID and the staffid of the staff members
responsible for the activity into the ActivityStaff column. I tried
the following but it did not work.

strActivityID = DMax("ActivityID", "Activity")
strSQL = "INSERT INTO ActivityStaff(ActivityID, StaffID) Values
(strActivityID, (SELECT StaffID from Staff where Active = 1))"
CurrentDb.Execute (strSQL)

Any suggestions? Would a temporary table be better?

Thanks,
Blake

 

[Ken Sheridan]

Blake:

You need to concatenate the value of the strActivity variable into the SQL
statement, not reference the variable. As you want to insert multiple rows
you should not use a VALUES clause, but include the value of the strActivity
variable as a constant in the SELECT clause. The variable appears to be of
text data type, so I'm assuming that the ActivityID column is also text data
type, and its value should therefore be wrapped in quotes characters.
However, the fact that you are identifying the relevant row by virtue of it
being the MAX value makes me wonder whether it is in fact a long integer
number data type, e.g. an autonumber. So I'm covering both possibilities

Firstly for text data type:

Dim dbs AS DAO.Database
Dim strActivityID AS String
Dim strSQL As String

Set dbs = CurrentDb

strActivityID = """" & DMax("ActivityID", "Activity") & """"

strSQL = "INSERT INTO ActivityStaff(ActivityID, StaffID) " & _
"SELECT " & strActivityID & ", StaffID " & _
"FROM Staff " & _
"WHERE Active = 1"

dbs.Execute strSQL

If ActivityID is, as I suspect, a number data type amend the above to:

Dim dbs AS DAO.Database
Dim lngActivityID AS Long
Dim strrSQL As String

Set dbs = CurrentDb

lngActivityID = DMax("ActivityID", "Activity")

strSQL = "INSERT INTO ActivityStaff(ActivityID, StaffID) " & _
"SELECT " & lngActivityID & ", StaffID " & _
"FROM Staff " & _
"WHERE Active = 1"

dbs.Execute strSQL

If you are relying on the MAX value of an autonumber ActivityID column to
identify the latest row to be inserted into the Activity table you should be
aware that it cannot be absolutely guaranteed that this will be the case. An
autonumber guarantees uniqueness only and its value is arbitrary. It will
work most of the time, but there is always a risk.

To provide a value which can be guaranteed to identify the latest row to be
inserted into a table either a non-autonumber column should be used and its
value computed when a row is inserted, or a DateTimeStamp column of Date/Time
data type with a DefaultValue property of Now() can be used.

Ken Sheridan
Stafford, England