How do I add data to a row in MS Access without using update

I have a temp table (in MS Access) with 7 columns. ID, projName, projInfo, enggFname, enggLname,devFname, devLname.
Using an " insert into select" statement, I insert a row with ID, projName and projInfo from tblProjects into tblTemp.
 tblProjects also contains enggID and devID as foreign keys. Using these foreign keys and other filter criteria, I need to add the enggFName, enggLname to the already existing row in tblTemp. Then I have to repeat the process to add devFname and devLname to the row in tblTemp to complete the insert.
Can I do this in a single query? I have tried using union and append without success. I need to have only a single row with the data inserted in parts in the temp table.
Any suggestions as to how I can build the query, or write multiple queries but execute them after a single event?

Thanks in advance.
annapolistechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
No, in order to modify data in a table, you must make an update query for each table to be updated.

What do you mean by "but execute them after a single event" ?

you can execute multiple update statements in the Event Handler for a single button, by generating an SQL string, then executing that SQL, then genrate the next query, and executing it:

Dim strSQL as string
Dim cn as Connection

set cn = CurrentProject.Connection
strSQL = "UPDATE Table1 set Field1 = " & Value1
cn.Execute strSQL
strSQL = "UPDATE TABLE2 set Field2 = '" & Value2 & "'"
cn.Execute strSQL

like that.

Is that what you mean?

AW
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mfsamuelCommented:
Link it to a Macro and execute the macro to run all insert or updates you need.

Macro > New

And add these lines:

1) Action: SetWarnings
  -Warnings On: No
2) Action: OpenQuery
  -Query Name: [your first insert]
3) Action: OpenQuery
  -Query Name: [your second insert]
...
n) Action: SetWarnings
  -Warnings On: Yes

This macro will run each update/insert in order.
0
annapolistechAuthor Commented:
Thanks for the replies, I will try the solutions and let you know.
0
Arthur_WoodCommented:
You should be aware that Macros in Access are not a good idea.  Microsoft strongly advises against using them (they are only supported in 2000+ for compatability with earlier versions of Access).  

It is IMPOSSIBLE to debug a Macro.

AW
0
mfsamuelCommented:
Not that you are wrong, but can you provide a reference to support that statement?  I have used macros for years, and have found that automating many things is only posible with VBA code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.