Using select statements in a SQL insert statement...

Using select statements in a SQL insert statement...

Post by Pamela via » Sat, 24 Sep 2005 02:12:55


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.


--
Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

Using select statements in a SQL insert statement...

Post by Tim Fergus » Sat, 24 Sep 2005 03:19:17

"Pamela via AccessMonster.com" < XXXX@XXXXX.COM > wrote in




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

 
 
 

Using select statements in a SQL insert statement...

Post by Pamela via » Sat, 24 Sep 2005 04:27:13

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.

:-)




--
Message posted via AccessMonster.com
http://www.yqcomputer.com/