Use the result of a recordset without moving through it line by line?

[m3atball]

I have a query based on Table1. I wanted to use the result of this query plus certain constants and append them all to Table2. I know how to do it by moving through the recordset from Table1 line by line to create the INSERT INTO statement for Table2.

The question is: is it possible to do it without having to go line by line? i.e. Can I using the recordset from Table1 and apply it as a whole to Table2 without using .MoveFirst/.MoveNext, etc.

Thanks!

[pbaldy]

You can use your query directly:

INSERT INTO TableName(...)
SELECT ...
FROM YourQueryName

If you mean VBA constants, you can create functions that return their values and use the functions in the above query.

[jgelpi16]

pbaldy - I sure would like to know how to reference functions in queries. Can you share your knowledge on this topic?

[m3atball]

Hi Paul -

Thanks for the quick reply! I tried that but because not all the fields in TABLE1 goes into TABLE2, I have to use

INSERT INTO TABLE2() VALUES()

This version does not allow subqueries inside the VALUES() part. Ideally, this is something I'm hoping to get in one step:

INSERT INTO SCHEDULE (USER_ID, PROJECT_ID, PHOURS, RHOURS, NOTES)
VALUES (
SELECT PREPARER FROM PROJECTS WHERE PREPARER = "USER1",
SELECT PROJECT_ID FROM PROJECTS, WHERE PREPARER = "USER1",
NULL, NULL, NULL)

I'm doing it using the line-move with the recordset for now. Thanks again!

[pbaldy]

You just call it:

SELECT TableName.FieldName, FunctionName() AS ValueFromFunction
FROM TableName

[pbaldy]

Whether or not all fields from 1 go into 2, you should be able to use a SELECT clause instead of VALUES. You just include the appropriate fields in both clauses. If those subqueries only return a single value, you might get away with a DLookup() in there. Otherwise, perhaps joins? If you could post some sample data and the expected result, it might be easier.

[m3atball]

Paul -

I got it!

This SQL works. Since the other three fields do not require a default value, I'm just skipping them in the append process.

INSERT INTO SCHEDULE (USER_ID, PROJECT_ID)
SELECT PREPARER, PROJECT_ID FROM PROJECTS WHERE PREPARER = "USER1"

Thanks a lot for waking me up!

[pbaldy]

Ah good, glad you got it sorted out.