Dynamically generated form input fields

[hamstur]

I have the following three tables in a ms access database:
VISITS
ID | CLIENT ID | DATE ADDED

MEASUREMENTS
ID | LABEL

RESULTS
ID | VISIT ID | MEASUREMENT ID | VALUE

[VISIT ID] and [MEASUREMENT ID] are foreign keys referencing VISITS and MEASUREMENTS respectively.
I need to create a form where the user can enter values into the results table based on the records stored in the
MEASUREMENTS table. So basically if the MEASUREMENTS table had the following records:

ID | LABEL
1 | Measure1
2 | Measure2
3 | Measure3
The final form should look something like:
---------------------------
Client : [select box]
Date Added : [text box]

Measure1 : [user enters result 1]
Measure2 : [user enters result 2]
Measure3 : [user enters result 3]
---------------------------

I'm not experienced with MS Access and don't really know how to create such a form.
What do you recommend? Thanks in advance.

[FishVal]

Hi, hamstur.

• create form linked to table [Visits], let us say [frmVisits]
• create a query
  

  SELECT Measurements.[ID], Measurements.Label, Results.[ID], Results.[Visit ID], Results.[MeasurementID], Results.[Value]
  FROM Measurements LEFT JOIN (SELECT * FROM Results WHERE Results.[Visit ID]=nz(Forms!frmVisits![Visit ID],0)) AS Results ON Measurements.[ID]=Results.[Measurement ID]
  WHERE Not Isnull(Forms!frmVisits![Visit ID]);
   

• create a form based on the query above, let us say [sbfResults]
• set it AllowAdditions property to "No", set it DefaultView property to "Datasheet"
• place [sbfResults] form to [frmVisits] as subform not linking it via Master/Child fields
• in [frmVisits] module place the following code
  

  Private Sub Form_AfterInsert()
      Me.sbfResults.Requery
  End Sub
   

• in [sbfResults] module place the following code
  

  Private Sub Form_BeforeUpdate(Cancel As Integer)
      Me.[Visit ID] = Me.Parent.[ID]
  End Sub