Using select statements in a SQL insert statement...

  • Thread starter Thread starter Pamela via AccessMonster.com
  • Start date Start date
P

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.
 
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)
Click to expand...


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
 
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.

:-)

Tim said:
SQL1 = "INSERT INTO tblAssignedHist " & _
" (RecID, NewUserName,,ChangedBy) " & _
Click to expand...
[quoted text clipped - 7 lines]
behind one of the other controls on the form (txtCurrentUser, an
unbound control)
Click to expand...

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
Click to expand...
 
Back
Top