Form and SubForm Help

J

Jesse

ok well let me do my best here to epxlain what I am trying to do.

I need to create a form that has a dropdown box with company names that is
pulled from the company table. I then need the subform to have a list of
questions from the topic table and then a response filed for the response
table. Now here is what I thought I needed. The response table stores the
CompanyID and the TopicID and then a numeric response field. What I am
looking to do is to Select the company and have all the Topics listed even if
there is no response yet. Then if a response is entered into the response
field the CompanyID, TopicID and Response be stored in the Response table so
I can then do some data manipilation and reporting. My problem is that I can
not seem to generate the list of Topics in the sub form because there are no
records in the response table matchin the company. I want the sub form to
pull up all topics and if there is a response show it. I hope this make sence
and would appriciate any help.
 
W

Wolfgang Kais

Hello Jesse.

Jesse said:
I need to create a form that has a dropdown box with company names
that is pulled from the company table. I then need the subform to have
a list of questions from the topic table and then a response filed for
the response table. Now here is what I thought I needed. The response
table stores the CompanyID and the TopicID and then a numeric
response field. What I am looking to do is to Select the company and
have all the Topics listed even if there is no response yet. Then if a
response is entered into the response field the CompanyID, TopicID
and Response be stored in the Response table so I can then do some
data manipilation and reporting. My problem is that I can not seem to
generate the list of Topics in the sub form because there are no
records in the response table matchin the company.
I want the sub form to pull up all topics and if there is a response
show it. I hope this make sence and would appriciate any help.
Click to expand...

In fact I have needed quite the same some years ago, and I indeed found
a sloution. Let me first make some assumptions about the names of
tables and their fields in the database. You have 3 tables:

Companies: CompanyID (PK) and CompanyName
Topics: TopicID (PK) and TopicName
Responses: CompanyID (PK), TopicID (PK) and Response.

First, create and save the main form (frmCompanies) that has a combo
box (cboCompany) that stores CompanyID (as bound column) and that
displays the CompanyName. As I understood the problem, your form will be
unbound, so you can disable the RecordSelectors and NavigationButtons.

Then, create a query (qryResponsesCurrentCompany) that returns all
responses for the company that is (later) displayed in the combo box:
SELECT Responses.CompanyID, Responses.TopicID, Responses.Response
FROM Responses
WHERE (((Responses.CompanyID)=[Forms]![Companies]![cboCompany]));

(Add the criterion for CompanyID using the "Expression Builder")

Next, create the query (qryResponsesAllTopicsCurrentCustomer) that will
be used for the subform and that returns the combinations of the previous
query with all Topic-Records and additionally the CompanyID of the combo
in the main form (used to link the subform) as CompID:
SELECT Forms!Companies!cboCompany AS CompID, Topics.TopicName,
qryResponsesCurrentCompany.CompanyID,
qryResponsesCurrentCompany.Response
FROM Topics LEFT JOIN qryResponsesCurrentCompany
ON Topics.TopicID = qryResponsesCurrentCompany.TopicID;

The trick is the "left join", which assures that all topics are listed
(with empty fields from the Responses table where no record exists)
and also that when a blank Responses field (from a nonexistent record)
is filled, the TopicID is filled in automatically.

Use this query to build a tabular form (frmSubCustomersResponses) that
displays the fields TopicName and Response.
I suggest to set some properties of this form:
RecordSelectors = No, NavigationButtons = No, AllowAdditions = No.
And for the TopicName-Textbox: Locked = Yes.

Then we need a small event procedure for this form that sets the correct
CompanyID before a record is saved (Before Update):
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.CompanyID = Me.Parent.cboCompany
End Sub

At last, add this second form as a subform to the main form and set the
properties
of the subform control to link the subform to the main form:
LinkChildFields: CompID
LinkMasterFields: cboCompany

That's all.
 
Top