By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,776 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,776 IT Pros & Developers. It's quick & easy.

Dynamically generated form input fields

P: 1
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.
Sep 16 '07 #1
Share this Question
Share on Google+
1 Reply


FishVal
Expert 2.5K+
P: 2,653
Hi, hamstur.
  • create form linked to table [Visits], let us say [frmVisits]
  • create a query
    Expand|Select|Wrap|Line Numbers
    1. SELECT Measurements.[ID], Measurements.Label, Results.[ID], Results.[Visit ID], Results.[MeasurementID], Results.[Value]
    2. 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]
    3. WHERE Not Isnull(Forms!frmVisits![Visit ID]);
    4.  
  • 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
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_AfterInsert()
    2.     Me.sbfResults.Requery
    3. End Sub
    4.  
  • in [sbfResults] module place the following code
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_BeforeUpdate(Cancel As Integer)
    2.     Me.[Visit ID] = Me.Parent.[ID]
    3. End Sub
    4.  
Sep 16 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.