Insert Into & Select statements as values...syntax?

[Pamela via AccessMonster.com]

Hello;

I wasn't sure where to post this so I'll try here too...

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 have only used
Oracle SQL in the past...I know how to do this with JUST a select statement,
but can you use control values & a select statement as another value?

Thanks for any help.

 

[Douglas J Steele]

Try:

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

 

[Guest]

Also try:
sql1 = "INSERT INTO tblAssignedHist " & _
"(RecID, NewUserName,ChangedBy) " & _
"SELECT " & [RecID] & ", ' " & [cmbAssignedTo] & " ', EmpName FROM
qryFetchName"

You put single quotes ' ' around text and pounds # # around dates. If the
tables fields are text and date data types.

Also try to debug the syntax in stages, start with inserting just recID and
then if it works try the next one.