I can see no reason why a row needs to pre-exist in the Questionnaires table.
The Questionnaires table presumably includes a Ref column as a foreign key
referencing the primary key of Inquiries (if the relationship is one-to-one,
i.e. there is only one Questionnaire row for each row in Inquiries, then its
both a primary key and a foreign key of Questionnaires of course). To
complete a questionnaire in relation to a particular ref, therefore, you
simply need to insert a new row into Questionnaires via a form bound to that
table, with a control bound to the ref Column. This control would usually
be a combo box in which you would select a ref from a list, or possibly
select a value from another uniquely valued column in Enquiries if ref is an
arbitrary value whose actual value is irrelevant to the user.
For the first scenario the RowSource of the combo box would be:
SELECT Ref FROM Inquiries ORDER BY Ref;
For the second scenario you'd need to set the combo box up differently, to
hide the bound ref column and show whatever column is meaningful to the user,
as follows:
ControlSource: Ref
RowSource: SELECT Ref, TheColumnYouWantToSee FROM Inquiries ORDER BY
TheColumnYouWantToSee;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
An alternative method of data entry would be to have a form based on
Inquiries and within it a subform based on Questionnaires, the parent form
and subform being linked on the Ref columns by virtue of these being the
LinkMasterFields and LinkChildFields properties of the subform control. That
way the correct Ref value would automatically be entered into a new row in
Questionnaires when its inserted via the subform. To avoid undue clutter in
the form one approach would be to have the Inquiries data on one page of a
tab control and the Questionnaires subform on another. The user can then tab
between them. Controls bound to the principal columns from inquiries,
including Ref, would normally be placed in the main parent form above the tab
control so that these are visible whatever page of the tab control is
selected; the user can then see what Inquiry record the form is at when they
have selected the Questionnaires tab.
Where a query which LEFT OUTER JOINs Inquiries to Questionnaires (which is
what the 2nd join type option is) would come into play would be if you wanted
to return rows from Inquiries along with data from Questionnaires where
matches exist, but including the Inquiries data even where there is no match
in Questionnaires, in which case the columns from Questionnaires would be
Null for the unmatched rows. This is more likely to be as the RecordSource
for a report rather than a form for data entry.
One thing to be aware of with this type of query is that you can only
restrict it on columns on the left side of the join, i.e. you can impose
criteria on columns from Inquiries, but not on columns from Questionnaires.
To do the latter you'd have to adopt a different approach, using a subquery,
but that's not something we need to go into in at present.
Ken Sheridan
Stafford, England
