Insert Into multiple fields in VBA SQL string

[Pamela via AccessMonster.com]

Hi.

This is my first time to this forum. I am trying to update an Audit table I
have created with a SQL string in VBA. Here is the code:

SQL1 = "INSERT INTO tblUserAudit ( EmpName, EmpID ) SELECT qryFetchName.
Name, tblUserNames.EmpID FROM qryFetchName, tblUserNames WHERE ((tblUserNames.
EmpIDNum)=GetCurrentUserName())"

SQL2 = "INSERT INTO tblUserAudit ( RecordNum ) VALUES (" & NewRecNum & ")
;"

I can get both statements to work independently, but how the heck can I put
this into one statement? The table contains the following fields:

EmpID, EmpName, TimeStamp, RecordNum, Action

TimeStamp is set to default (Now()), & Action is set to default (Add) since
we are not allowing users to delete from the table, so I don't have to worry
about them. I need to be able to update EmpID, EmpName, & RecordNum with one
SQL statement in VB.

Thanks. I apologize in advance if the solution is simple - I'm used to
Oracle Forms & Reports (which I haven't used in 2 years) & my programming is
a bit rusty (programmed BASIC in the 80s).

Thanks Again!

 

[Pamela via AccessMonster.com]

I am trying to do this in Access 2000.

Thanks!

 

[John Spencer (MVP)]

PERHAPS the following might work if NewRecNum is a constant. If new RecNum is
supposed to change for each record added in the select then I think you might
need to use some VBA and add records that way.


SQL1 = "INSERT INTO tblUserAudit ( EmpName, EmpID, RecordNum ) " & _
" SELECT qryFetchName.Name, tblUserNames.EmpID," & NewRecNum & _
" FROM qryFetchName, tblUserNames " & _
" WHERE ((tblUserNames.EmpIDNum)=GetCurrentUserName())"

 

[Pamela]

Thanks! I obviously need to brush up on my SQL too... The solution was
simpler than I was making it.