SQL Query Problems

[slaterino]

Hi,


I am trying to do what I thought would be a simple SQL query but am having
some problems and can't find the answer to this on the web.

Put simply I want to add three values into a table using an append query. One
of those values I want to retrieve from another table and which is dependent
on a value on a form.

Okay, this is the start of my query:

INSERT INTO tbl_course_attendees_new ( URN, current, session_ID )

This first bit is fine. I then want to add a value from the form as well as
set 'current' which is a yes/no field to 'no.' So I have:

SELECT Forms!frm_search!URN AS URN, -1 AS current,

My problem is with the next bit which is that I want to find every value from
a table that has the same course_ID that is selected on the form in a drop-
down box. How can I add this to the query? The SQL statement for this alone
would be:

SELECT session_ID FROM sessions WHERE course_ID = Forms!frm_search!CourseName

How can I integrate that into the rest of the SQL query?

Thanks!
Russ

[weekend00]

currentdb.execute "INSERT INTO tbl_course_attendees_new ( URN, current, session_ID ) SELECT '"+Forms!frm_search!URN+"' AS URN, -1 AS current, session_ID FROM sessions WHERE course_ID = '"+Forms!frm_search!CourseName+"'"

[NTC]

Here's my general advice:
1. "want to retrieve from another table and which is dependent
on a value on a form." In this type scenario you can consider have that table's data appear in a subform of the form - with that value being the parent/child link. Ultimately you can make that subform not visible if it offers nothing to the user - but it allows you to have that value easily callable. Plus for trouble shooting you can make it visible. Whether or not this makes sense in your case I am not totally sure as I don't completely follow your post, but just thought I would mention this.

2. Any AppendQuery (or UpdateQuery) should start as, and completely work correctly in terms of pulling the correct records - as a regular Select Query. Once it always results in the correct records/fields - then redefine it as an AppendQuery.

hope this helps a little.