Passing the output of one append query to another

[Alpana]

Suppose I have two tables: Table1 and TableLog. Table1 has some fields like ID(autonumber),Field1,Field2, etc. For this table, there is an append query "AddDetails" which takes the values of Field1,Field2,etc as parameters, and appends a record accordingly.

Now, I am required to store information about the update in the table TableLog. So I have a separate append query AddLog, which takes in the ID of the record (which has been added to Table1 by AddDetails) as a parameter, and stores the corresponding information in TableLog.



I want to combine these two queries ( along with some other independent actions) . For this I'll need to pass the ID of the record created by AddDetails to AddLog. How can I do it? I cant use the "On insert" feature as Table1 is a part of another database, and I have imported a link to it.

[June7]

Show code and/or attach project for analysis.

What do you mean by 'combine' - nesting, run sequentially? I don't think multiple SQL actions can be run in the same SQL statement.

You have the record ID in a variable?

[Alpana]

Well, yes, I do mean nesting: running sequentially... But the ID of the recently appended record has to be passed to the next query.

I dont know if this is understandable, but these are the codes for the two queries:

Data Entry query:

INSERT INTO [Arrival List] ( ADate, Party, Type, KGs )
SELECT [Date of Arrival] AS Expr1, [Party] AS Expr2, [Type] AS Expr3, [Quantity] AS Expr4;

Log Query:

INSERT INTO [Alert log] ( Code, Description )
SELECT Query2.Code, [Sentence1] & " " & [Sentence2] & " " & [Sentence3] & " " & [Sentence4] AS Expr1
FROM Query2, Query3, Query4, Query5;


'Arrival List' is the table I want to add the details to, and 'Alert log' is the table I want to add the log information to. Though not quite clear here, but the queries Query2,Query3,Quer4 and Query5 all of them accept a common parameter 'ArrivalNumber', which should be the ID of the record which has been appended to Arrival list.

Oh, and Arrival list is the linked table.

[Alpana]

I would prefer combining these two using macros...

[June7]

Are these both saved Access query objects?

The first query has a nested SELECT that doesn't have tablename or WHERE clause. I don't see an ID value so presume the ID is an autonumber field. You can get the ID by a DMax function.

Not sure I understand the FROM clause of the second SQL. Once you get the ID by the DMax, set a form textbox to that value and have the 4 queries refer to the textbox as parameter input.

I don't use macros, only VBA. Are coding behind form or use data macros?

[Alpana]

Yes, they're all Access query objects.
As far as I understand VBA, Dmax returns the maximum value. It would be effective only as long as my ID field remains an autonumber field. That could be a solution, but that would hard-code my design. Is there any other way in which I can set the query output to variables, which can be passed to other queries?

[June7]

If you use a custom unique ID of you own design, then you must have code to generate the ID. This generated value could easily be populated to a form textbox. Then the rest of my suggestion applies.