How to collect information from a form and subform

[NFL]

I have a main form where the data source points to a table with 7 questions.
All I have in that main form is a combo box which will select one question.
The relationship is one question (no duplicates). I also added an unbound
combo box to select a person from a different table.

Now, the subform's source object has a relationship of many questions tied
to the main form and will give me what I need. The Child Fields and Master
Fields both agree. I also included a checkbox in that subform which I think
might help.

What I would like to do is add a command button to the main form that will
search records has a checkmark set to true. If true, then I would get
information from that specific record and also the person's name from the
main form and place both of those on a separate table.

Would the subform need to be setup as a single form so I could include the
person for each record separately?

Thank you,

 

[AccessVandal via AccessMonster.com]

If you expect a single record from the mainform filtering, Yes.

If there are more than one record, you might prefer a continuous form or
datasheet view.

Or you still can use a form and scroll the records by the record navigation
buttons.

 

[NFL]

How would the code look like? This is an abbreviated scetch as what I have
and like to do.

One ...... Many Many ............. One
Table1 Table2 Table3 Table4
Question Question EmployeeID EmployeeID
ID Answer
Yes/No

The main form has a dropdown menu that will select any question. Table1 is
the source object forTable1. An unbound combobox is also added to the main
form which select one EmployeeID from Table4

The subform comes from Table2 and works like a query and will reveal many
Answers for Table1. There's no connection between Table2 and Table3.
However I was looking for a way to gather the data from the form itself and
place all of it in Table3.

Hope that makes sense and if it does, how can it be done? I included the
yes/no to limit my choices in the subform.

Thank you for your time and help!

 

[NFL]

Table 3 would have many EmployeeID and many Answers
Table2 has many Questions and many Answers.

 

[AccessVandal via AccessMonster.com]

IMO, three tables would just do.

One ...... Many One
Table1 Table2 Table3
ID Question EmployeeID
Answer
Yes/No
ID
EmployeeID

In your subform, the recordsource query would be something like

SELECT EmployeeID, ID, Question, Answer, Yes/No FROM Table2 WHERE EmployeeID
= [Forms]![YourMainFormName]![YourComboBoxName] AND Yes/No = [Forms]!
[YourMainFormName]![YourCheckBoxName]
Note: assume EmployeeID is number and Yes/No is Boolean datatype.

You to need correct the syntax as neccessary. And avoid name like "Yes/No" in
your tables and avoid reserved names and characters.

 

[AccessVandal via AccessMonster.com]

Some mistake on table

One ...... Many One
Table1 Table2 Table4
Question ID EmployeeID
ID Answer
Yes/No
EmployeeID

SELECT Table2.ID, Table2.EmployeeID, Table1.Question, Table2.Answer, Table2.
booYesNo
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE (((Table2.EmployeeID)=[Forms]![YourMainFormName]![YourComboboxName])
AND ((Table2.booYesNo)=[Forms]![YourMainFormName]![YourCheckBoxName]))

Table 3 would have many EmployeeID and many Answers
Table2 has many Questions and many Answers.

How would the code look like? This is an abbreviated scetch as what I have
and like to do.

[quoted text clipped - 31 lines]

 

[NFL]

Once the query is pulled, is there a way to insert a command button on the
main form and pass certain data from the query which is in the subform to
another table?

IMO, three tables would just do.

One ...... Many One
Table1 Table2 Table3
ID Question EmployeeID
Answer
Yes/No
ID
EmployeeID

In your subform, the recordsource query would be something like

SELECT EmployeeID, ID, Question, Answer, Yes/No FROM Table2 WHERE EmployeeID
= [Forms]![YourMainFormName]![YourComboBoxName] AND Yes/No = [Forms]!
[YourMainFormName]![YourCheckBoxName]
Note: assume EmployeeID is number and Yes/No is Boolean datatype.

You to need correct the syntax as neccessary. And avoid name like "Yes/No" in
your tables and avoid reserved names and characters.

How would the code look like? This is an abbreviated scetch as what I have
and like to do.

One ...... Many Many ............. One
Table1 Table2 Table3 Table4
Question Question EmployeeID EmployeeID
ID Answer
Yes/No

The main form has a dropdown menu that will select any question. Table1 is
the source object forTable1. An unbound combobox is also added to the main
form which select one EmployeeID from Table4

The subform comes from Table2 and works like a query and will reveal many
Answers for Table1. There's no connection between Table2 and Table3.
However I was looking for a way to gather the data from the form itself and
place all of it in Table3.

Hope that makes sense and if it does, how can it be done? I included the
yes/no to limit my choices in the subform.

Thank you for your time and help!

 

[AccessVandal via AccessMonster.com]

Yes, you can. But why do you need to do that?

If it's for a report, just use the query as in the form.

You can use something like

currentdb.execute "insert into Table where (select from as in your form's
sql string)......"

Once the query is pulled, is there a way to insert a command button on the
main form and pass certain data from the query which is in the subform to
another table?

IMO, three tables would just do.

[quoted text clipped - 38 lines]