Specifying number of enteries in a subform

[JonnyRotten]

I am having a struggle learning about relational table structure, and I
am coming up with some projects to try to better learn it.
Well, right now I am stuck on a subform problem.

Say I have two tables

Table1
--------------
ID (key field, autonumber)
Name (text)

Table2
-------------
ID (Key Field, number)
Task

The ID fields have a relationship built with One-to-many structure.
I want to make a form and related subform to fill this out. But what I
want, is when the form loads, it has 5 enteries for the user to fill
out for table2. No more, no less.
How can I force the subform to have a certain number of enteries?

 

[JK]

Hi Jonny

For clarity lets call the main form "MainForm" and the subform "SubMain" and
the id's Table1_ID and Table2_ID and assuming the main form is based on
Table1 and the subform is based on table2:

In he SUBFORM create a an "OnCurrent" Event Procedure as follows:

+++++
Dim numRecs as Long, idnMain as Long

idnMain=Nz(Forms![MainForm]![Table1_ID] ,0)
numRecs=Dcount("[Table2_ID]","Table2","[Table2_ID]=" & idnMain)
Me.Form.AllowAddtions=IIf(numRecs<=5,True,False)
+++++

If you actually using the field names as in your example, it is not a good
idea to give both key fields the same name because it will confuse you (not
he program) as later you will confuse one "ID" from another, if you did I
suggest that change them

Also the word "Name" is a reserved word, suggest that you change it to
something else (like "Customer Name" or whatever is appropriate) otherwise
that will cause errors as you progress.

Regards/JK

 

[JK]

Oops I confused myself

Table2 must have Table1_ID, There is a one-to-many realation beteen Table1
and Table2 on Table1_ID

+++++
Dim numRecs as Long, idnMain as Long

idnMain=Nz(Forms![MainForm]![Table1_ID] ,0)
numRecs=Dcount("[Table2_ID]","Table2","[Table1_ID]=" & idnMain)
Me.Form.AllowAddtions=IIf(numRecs<=5,True,False)
+++++
(2nd line changed)

Rgds/JK

Hi Jonny

For clarity lets call the main form "MainForm" and the subform "SubMain"
and the id's Table1_ID and Table2_ID and assuming the main form is based
on Table1 and the subform is based on table2:

In he SUBFORM create a an "OnCurrent" Event Procedure as follows:

+++++
Dim numRecs as Long, idnMain as Long

idnMain=Nz(Forms![MainForm]![Table1_ID] ,0)
numRecs=Dcount("[Table2_ID]","Table2","[Table2_ID]=" & idnMain)
Me.Form.AllowAddtions=IIf(numRecs<=5,True,False)
+++++

If you actually using the field names as in your example, it is not a good
idea to give both key fields the same name because it will confuse you
(not he program) as later you will confuse one "ID" from another, if you
did I suggest that change them

Also the word "Name" is a reserved word, suggest that you change it to
something else (like "Customer Name" or whatever is appropriate) otherwise
that will cause errors as you progress.

Regards/JK

I am having a struggle learning about relational table structure, and I
am coming up with some projects to try to better learn it.
Well, right now I am stuck on a subform problem.

Say I have two tables

Table1
--------------
ID (key field, autonumber)
Name (text)

Table2
-------------
ID (Key Field, number)
Task

The ID fields have a relationship built with One-to-many structure.
I want to make a form and related subform to fill this out. But what I
want, is when the form loads, it has 5 enteries for the user to fill
out for table2. No more, no less.
How can I force the subform to have a certain number of enteries?