Using select statements in a SQL insert statement...

  • Thread starter Pamela via AccessMonster.com
  • 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.
 
T

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
 
P

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.

:)

Tim said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top