Linking subform to main form

[Adam]

Hi All,

I have a questionnaire like form which contains fields from the table
'AuditMaster' like:

AuditID (Autonumber) key
AdvisorID (number)
AuditDate
AuditMonth
CallDate
CallRef
ContactName
ContactNotes

I then have a subform that contains data from two tables via a query:

QuestionID - table 'QuestionMaster'
QuestionDesc - table 'QuestionMaster'
QuestionID - table 'AuditResults'
ResultID - table 'AuditResults'
AuditID - table 'AuditResults'
AuditNotes - table 'AuditResults'

Now these two tables are linked 1 to Many, 1 being QuestionID from
Results table, and Many being QuestionID in QuestionMaster table.

Now when you open the form, it creates a new AuditID in AuditMaster
table as this is a autonumber. As AuditMaster is linked to AuditResults
by the AuditID field I need to find a way to update the AuditID field
in the subform so it updates the AuditID in the AuditResults table for
every question that is scored.

The subform is a continous form so if I do vb code like:

Forms!AuditSubform!AuditID = Me.AuditID

It only updates the first record in the continous subform!

Does anyone know how I can update the AuditID field for each question
that I mark in the subform, to copy what is provided in the
NewAuditfrm.AuditID field?


I hope I've made this clear, its really testing my knowledge!

Many Thanks

Adam

 

[Guest]

Adam:

Set the LinkMasterFields and LinkChildFields properties of the subform
control (i.e. the control in the parent form which houses the subform) to
AuditID. This will not only restrict the rows displayed in the subform to
those where the AuditID matches the parent form's current record, but will
also automatically insert the AuditID value from the parent form, whether its
an existing or a new AuditMaster record, when you add a new row in the
subform. You don't need to show the AuditID in a control on the subform,
just include it in the underlying recordset.

You don't actually need to include the QuestionMaster table in the subform's
RecordSource, just the AuditResults table will do. By using a combo box as
the control bound to the QuestionID field from the AuditResults table you can
simply select from a list of QuestionDesc values. The combo box's
ControlSource would be QuestionID and its RowSource:

SELECT QuestionID, QuestionDesc
FROM Questionmaster
ORDER BY QuestionDesc;

Set other properties of the combo box as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches, but the first
dimension must be zero)

This hides the bound QuestionID column so you see just the QuestionDesc
values. The value of the control is still the unseen QuestionID column,
however.

Ken Sheridan
Stafford, England