I created an append query that will work when run from only the subform.  I
do not know how to get this to work when the subform is embedded in the main
form.  I tried a button to run the query on the main form but nothing
happened.  I cannot see the button if I place it on the subform, since the
subform must be viewed in data sheet view.  If I need to call the query from
an event I do not know how to do that and would really appreciate help.  The
main form creates a record with an ID that is copied into the subform for
linking purposes.

creating question records for a survey
~~~

Hi ridgerunner,

thanks for the additional information

since you did not specify fieldnames, I will use what I consider good,
generic names and you will have to change them

I am assuming your database includes tables with a structure similar to
the following:

Participants
- ParticID, autonumber, PK
- Lastname, text
- firstname, text

Questions
- QuestionID, autonumber, PK
- Question, text

Surveys
- SurveyID, autonumber, PK
- ParticID, long, FK to Participants
- SurvDate, date/time

SurveyAnswers
- SurvAnsID, autonumber
- SurveyID, long, fk to Surveys
- QuestionID, long, FK to Questions
- Answer

PK is Primary Key
FK is Foreign Key

this is a simplified example.  It does not take into account that you
may have multiple types of surveys with different sets of questions

In SurveyAnswers, make a multi-field unique index on the combination of
SurveyID
QuestionID

this will protect you in case questions are created twice so you do not
get duplicates.

(multi-field unique indexes are covered in Access Basics, link in my siggy)

I am assuming that you have a main form/subform situation where the main
form is based on Surveys and the subform based on SurveyAnswers, which
is the table you wish to automatically fill records in

create a command button on the main form to create the questions

'~~~~~~~~~~~~~~~~~
   'save record if changes have been made
   if me.dirty then me.dirty = false

   'if we are on a new record, give user a message
   if me.newrecord then
      msgbox "You are not on a current record" _
         ,, "Cannot create questions"
      exit sub
   end if

   if isnull(me.ParticID) then
      msgbox "You must fill out who you are" _
         ,, "Cannot create questions"
      me.ParticID.setFocus
      exit sub
   end if

   dim strSQL as string

   strSQL = "INSERT INTO SurveyAnswers (SurveyID, QuestionID) " _
      & " SELECT " & me.surveyID _
      & ", QuestionID " _
      & " FROM Questions;"

'remove this line once everything works ok
debug.print strSQL

   currentdb.execute strSQL

   'make the new records show up on the subform
   me.subform_controlname.requery
'~~~~~~~~~~~~~~~~~

substitute the Name property of your subform control for subform_controlname

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~~~~~~~~~~~~~~
** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run !  from the SQL window
-- Access will tell you where the problem is in the SQL

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

 *
   (: have an awesome day :)
 *