Using select statements in a SQL insert statement...

[Pamela via AccessMonster.com]

Hello;

I am trying to figure out how to use the results of a select statement as one
of the values in an insert into statement...I am unclear on the syntax...

The VBA statement I have is as follows:

SQL1 = INSERT INTO tblAssignedHist (RecID, NewUserName,,ChangedBy) Values ("
& [RecID] & "," & """" & [cmbAssignedTo] & """" & ")" & " SELECT qryFetchName.
EmpName FROM qryFetchName"

I know this syntax isn't correct, it's to demonstrate what I want to do...the
first 2 values I want to insert are from controls on a form ([RecID &
[cmbAssignedTo]), the third value is the select statement behind one of the
other controls on the form (txtCurrentUser, an unbound control)

How can I accomplish this? I haven't done SQL in a while and am only used to
Oracle SQL...

Thanks for any help.

 

[Tim Ferguson]

SQL1 = "INSERT INTO tblAssignedHist " & _
" (RecID, NewUserName,,ChangedBy) " & _
"VALUES (" & [RecID] & "," & _
"""" & [cmbAssignedTo] & """" & ")" & _
"SELECT qryFetchName. EmpName FROM qryFetchName"

I know this syntax isn't correct, it's to demonstrate what I want to
do...the first 2 values I want to insert are from controls on a form
([RecID & [cmbAssignedTo]), the third value is the select statement
behind one of the other controls on the form (txtCurrentUser, an
unbound control)

The simplest way would be to use the DLookup() function to get the single
value to fill in:

SQL1 = "INSERT INTO tblAssignedHist " & _
" (RecID, NewUserName, ChangedBy) " & _
"VALUES (" & [RecID] & ", " & _
"""" & [cmbAssignedTo] & ", " & _
"""" & DLookup("EmpName", _
"qryFetchName", "Criterion=TRUE") & """)"

' don't forget this line!!
MsgBox SQL1

If you are trying to insert a number of records using different empnames,
then you need the alternative INSERT syntax:

SQL1 = "INSERT INTO tblAssignedHist " & _
" (RecID, NewUserName, ChangedBy) " &
"SELECT " & RecID & ", """ & cmbAssignedTo & """, " & _
" EmpName " _
"FROM qryFetchName "

MsgBox SQL1


but I think having a "static" RecID is likely to break the query. Hope
that helps


Tim F

 

[Pamela via AccessMonster.com]

Thank you.

I will try the DLookup method...I have never used DLookup before, so if it
works, I'd of learned something new too. I'll post back my results.

SQL1 = "INSERT INTO tblAssignedHist " & _
" (RecID, NewUserName,,ChangedBy) " & _

[quoted text clipped - 7 lines]

behind one of the other controls on the form (txtCurrentUser, an
unbound control)

The simplest way would be to use the DLookup() function to get the single
value to fill in:

SQL1 = "INSERT INTO tblAssignedHist " & _
" (RecID, NewUserName, ChangedBy) " & _
"VALUES (" & [RecID] & ", " & _
"""" & [cmbAssignedTo] & ", " & _
"""" & DLookup("EmpName", _
"qryFetchName", "Criterion=TRUE") & """)"

' don't forget this line!!
MsgBox SQL1

If you are trying to insert a number of records using different empnames,
then you need the alternative INSERT syntax:

SQL1 = "INSERT INTO tblAssignedHist " & _
" (RecID, NewUserName, ChangedBy) " &
"SELECT " & RecID & ", """ & cmbAssignedTo & """, " & _
" EmpName " _
"FROM qryFetchName "

MsgBox SQL1

but I think having a "static" RecID is likely to break the query. Hope
that helps

Tim F