Limit 'many'Records in 1:many Form/Subform

[Thelma Lubkin]

My form/subform combination that allows user to display 'ColorSet'
records, move to other records via a selector, to add,delete, and
edit them, and to manage the related 'Color' records from the subform
is close to completion, but I still need help...

The parent form's recordset has fields

string integer
COLORNAME CLASSSIZE and a few more that aren't relevant here.

The child form is related by COLORNAME.

I can now add, delete and edit fields of both the one(form) records
and the many(subform) records.

I need to restrict the number of 'many' records to exactly the
CLASSSIZE value of the parent form's 'one' record.

I can easily write code to do this, but I haven't figured out a place
to put it. It can't go into the before_update event of either the form
or subform and I have no other ideas (gotfocus didn't trigger and
seems that if it did it would trigger too often anyway).
I need this protection both when user adds a new ColorSet record:
force addition CLASSSIZE colors, and also force number of colors to
equal the revised CLASSSIZE in any subsequent edit.

How [where?] do I enforce this restriction?

thanks, --thelma

[pietlinden]

you can do something like get the recordcount of the subform's
recordsetclone and then if that count is equal to your maximum, then
you could set the AllowAdditions property to false (you'd need to
update it in the OnCurrent event of the main form. Gets ugly when you
want to enforce this when update and insert queries are running.
AFAIK, it's not gonna be easy... Access doesn't let you control
table-level events the way SQL Server does... where you could use
triggers.

[Allen Browne]

Cancel the Before Insert event of the form if the maximum number of related
items already exists in the table (which DCount() will tell you.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> wrote in message
news:dl**********@uwm.edu...

My form/subform combination that allows user to display 'ColorSet'
records, move to other records via a selector, to add,delete, and
edit them, and to manage the related 'Color' records from the subform
is close to completion, but I still need help...

The parent form's recordset has fields

string integer
COLORNAME CLASSSIZE and a few more that aren't relevant here.

The child form is related by COLORNAME.

I can now add, delete and edit fields of both the one(form) records
and the many(subform) records.

I need to restrict the number of 'many' records to exactly the
CLASSSIZE value of the parent form's 'one' record.

I can easily write code to do this, but I haven't figured out a place
to put it. It can't go into the before_update event of either the form
or subform and I have no other ideas (gotfocus didn't trigger and
seems that if it did it would trigger too often anyway).
I need this protection both when user adds a new ColorSet record:
force addition CLASSSIZE colors, and also force number of colors to
equal the revised CLASSSIZE in any subsequent edit.

How [where?] do I enforce this restriction?

thanks, --thelma

[Thelma Lubkin]

Allen Browne <Al*********@SeeSig.Invalid> wrote:
: Cancel the Before Insert event of the form if the maximum number of related
: items already exists in the table (which DCount() will tell you.)

I am doing this. The problems come

1. where the user finishes with fewer than the number of
related items required.
2. where the user reduces the number of items required [and
therefore allowed] and leaves before making enough
deletions.
--thelma
: --
: Allen Browne - Microsoft MVP. Perth, Western Australia.
: Tips for Access users - http://allenbrowne.com/tips.html
: Reply to group, rather than allenbrowne at mvps dot org.
:> I need to restrict the number of 'many' records to exactly the
:> CLASSSIZE value of the parent form's 'one' record.
:>

[Allen Browne]

These are chicken'n'egg problems.

#1: You cannot create the child records until the main record exists.
Therefore you cannot require a set number of child records before you allow
the main record to exist.

#2: You cannot require that the number of related items matches the
specified number at the time when that number is saved, and also allow the
number to be saved only if the right number of related records exist.

There are ways to handle that, such as programmatically created and deleting
the related records, but the implementation would depend on whatever
business logic dictates what records should be automatically
created/deleted.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> wrote in message
news:dl**********@uwm.edu...

Allen Browne <Al*********@SeeSig.Invalid> wrote:
: Cancel the Before Insert event of the form if the maximum number of
related
: items already exists in the table (which DCount() will tell you.)

I am doing this. The problems come

1. where the user finishes with fewer than the number of
related items required.
2. where the user reduces the number of items required [and
therefore allowed] and leaves before making enough
deletions.
--thelma
: --
: Allen Browne - Microsoft MVP. Perth, Western Australia.
: Tips for Access users - http://allenbrowne.com/tips.html
: Reply to group, rather than allenbrowne at mvps dot org.
:> I need to restrict the number of 'many' records to exactly the
:> CLASSSIZE value of the parent form's 'one' record.

[Terry Kreft]

The following code in the Subform should sort this out
Private Sub Form_AfterUpdate()
Call AllowNew
End Sub

Private Sub Form_Current()
Call AllowNew
End Sub

Private Sub AllowNew()
Dim intAllowed As Integer
Dim intCurrent As Integer
' This assumes the form control is the same name as the field name
' change if necessary
intAllowed = Me.Parent.CLASSSIZE
intCurrent = Me.RecordsetClone.RecordCount

Me.AllowAdditions = (intAllowed > intCurrent)
End Sub
--
Terry Kreft

"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> wrote in message
news:dl**********@uwm.edu...

My form/subform combination that allows user to display 'ColorSet'
records, move to other records via a selector, to add,delete, and
edit them, and to manage the related 'Color' records from the subform
is close to completion, but I still need help...

The parent form's recordset has fields

string integer
COLORNAME CLASSSIZE and a few more that aren't relevant here.

The child form is related by COLORNAME.

I can now add, delete and edit fields of both the one(form) records
and the many(subform) records.

I need to restrict the number of 'many' records to exactly the
CLASSSIZE value of the parent form's 'one' record.

I can easily write code to do this, but I haven't figured out a place
to put it. It can't go into the before_update event of either the form
or subform and I have no other ideas (gotfocus didn't trigger and
seems that if it did it would trigger too often anyway).
I need this protection both when user adds a new ColorSet record:
force addition CLASSSIZE colors, and also force number of colors to
equal the revised CLASSSIZE in any subsequent edit.

How [where?] do I enforce this restriction?

thanks, --thelma

[Thelma Lubkin]

Terry Kreft <te*********@mps.co.uk> wrote:
: The following code in the Subform should sort this out
: Private Sub Form_AfterUpdate()
: Call AllowNew
: End Sub

: Private Sub Form_Current()
: Call AllowNew
: End Sub

: Private Sub AllowNew()
: Dim intAllowed As Integer
: Dim intCurrent As Integer
: ' This assumes the form control is the same name as the field name
: ' change if necessary
: intAllowed = Me.Parent.CLASSSIZE
: intCurrent = Me.RecordsetClone.RecordCount

: Me.AllowAdditions = (intAllowed > intCurrent)
: End Sub
: --
Thanks, but this is the part that I've had working for some time
already, i.e putting a maximum on the number of records in the
'many' table. My problems are

1. Enforcing the *minimum*, in this case the same as the
maximum--I don't want to allow user to define fewer than
CLASSSIZE records
2. Making sure that a reduction of CLASSSIZE in the parent
record goes with the deletion of enough child records to
keept CLASSSIZE and the count of child records equal.

Piet Linden and Allen Browne have indicated that this isn't
easy. I think that I've figured out a baroque method to do this
using a dummy field in the parent record and a lot of tortuous
keeping track of intermediate values of CLASSSIZE.
This field, defined to require a value, would be kept empty in
the parent record's before update event, and the update thereby
canceled, until CLASSSIZE matches the number of records in the
child table. The dance of intermediate values involves remembering
the CLASSSIZE user is trying to get to when the cancel event
returns it to its original value--I doubt that I will do this;
the form is for internal use and users will just have to use a
little care. Anyway, the delete button will reappear when the
record is again accessed if CLASSSIZE<number of child records,
and an empty child record will show up if CLASSSIZE>number of
child records defined.

: Terry Kreft

:> I need to restrict the number of 'many' records to exactly the
:> CLASSSIZE value of the parent form's 'one' record.
:>

[Terry Kreft]

Whoops, sorry I misread your post.
--
Terry Kreft

"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> wrote in message
news:dl**********@uwm.edu...

Terry Kreft <te*********@mps.co.uk> wrote:
: The following code in the Subform should sort this out
: Private Sub Form_AfterUpdate()
: Call AllowNew
: End Sub

: Private Sub Form_Current()
: Call AllowNew
: End Sub

: Private Sub AllowNew()
: Dim intAllowed As Integer
: Dim intCurrent As Integer
: ' This assumes the form control is the same name as the field name
: ' change if necessary
: intAllowed = Me.Parent.CLASSSIZE
: intCurrent = Me.RecordsetClone.RecordCount

: Me.AllowAdditions = (intAllowed > intCurrent)
: End Sub
: --
Thanks, but this is the part that I've had working for some time
already, i.e putting a maximum on the number of records in the
'many' table. My problems are

1. Enforcing the *minimum*, in this case the same as the
maximum--I don't want to allow user to define fewer than
CLASSSIZE records
2. Making sure that a reduction of CLASSSIZE in the parent
record goes with the deletion of enough child records to
keept CLASSSIZE and the count of child records equal.

Piet Linden and Allen Browne have indicated that this isn't
easy. I think that I've figured out a baroque method to do this
using a dummy field in the parent record and a lot of tortuous
keeping track of intermediate values of CLASSSIZE.
This field, defined to require a value, would be kept empty in
the parent record's before update event, and the update thereby
canceled, until CLASSSIZE matches the number of records in the
child table. The dance of intermediate values involves remembering
the CLASSSIZE user is trying to get to when the cancel event
returns it to its original value--I doubt that I will do this;
the form is for internal use and users will just have to use a
little care. Anyway, the delete button will reappear when the
record is again accessed if CLASSSIZE<number of child records,
and an empty child record will show up if CLASSSIZE>number of
child records defined.

: Terry Kreft

:> I need to restrict the number of 'many' records to exactly the
:> CLASSSIZE value of the parent form's 'one' record.
:>

[Terry Kreft]

Rats, hit send too soon.

If you want to control navigation that closely it's probably easier to carry
it out yourself and get rid of the built-in navigation buttons.

The following is some code which approaches the what your looking to do.
In the main form
Where
Command5 navigates forwards
Command6 navigates backwards
The Subform control is called frmCOLORNAME_DETS

' *********************************
Private Enum MOVE_RECORD
mrPrevious = 0
mrNext = 1
End Enum

Private Sub Command5_Click()
If CheckCanMove Then
Call MoveRecord(mrNext)
End If
End Sub

Private Sub Command6_Click()
If CheckCanMove Then
Call MoveRecord(mrPrevious)
End If
End Sub

Private Function CheckCanMove()
With Me
CheckCanMove = (.CLASSSIZE.Value =
..frmCOLORNAME_DETS.Form.RecordsetClone.RecordCoun t)
End With
End Function

Private Sub MoveRecord(RHS As MOVE_RECORD)
Select Case RHS
Case mrPrevious
With Me.RecordsetClone
If Not .BOF Then
.MovePrevious
End If
If .BOF Then
.MoveFirst
End If
End With

With Me
.Bookmark = .RecordsetClone.Bookmark
End With
Case mrNext
With Me.RecordsetClone
If Not .EOF Then
.MoveNext
End If
If .EOF Then
DoCmd.GoToRecord Record:=acNewRec
Else
With Me
.Bookmark = .RecordsetClone.Bookmark
End With
End If
End With
End Select
End Sub

Private Sub Form_Unload(Cancel As Integer)
Cancel = ((Not CheckCanMove) And (Me.NewRecord = False))
End Sub

' *********************************

--
Terry Kreft

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:7p********************@karoo.co.uk...

Whoops, sorry I misread your post.
--
Terry Kreft

"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> wrote in message
news:dl**********@uwm.edu...

Terry Kreft <te*********@mps.co.uk> wrote:
: The following code in the Subform should sort this out
: Private Sub Form_AfterUpdate()
: Call AllowNew
: End Sub

: Private Sub Form_Current()
: Call AllowNew
: End Sub

: Private Sub AllowNew()
: Dim intAllowed As Integer
: Dim intCurrent As Integer
: ' This assumes the form control is the same name as the field name
: ' change if necessary
: intAllowed = Me.Parent.CLASSSIZE
: intCurrent = Me.RecordsetClone.RecordCount

: Me.AllowAdditions = (intAllowed > intCurrent)
: End Sub
: --
Thanks, but this is the part that I've had working for some time
already, i.e putting a maximum on the number of records in the
'many' table. My problems are

1. Enforcing the *minimum*, in this case the same as the
maximum--I don't want to allow user to define fewer than
CLASSSIZE records
2. Making sure that a reduction of CLASSSIZE in the parent
record goes with the deletion of enough child records to
keept CLASSSIZE and the count of child records equal.

Piet Linden and Allen Browne have indicated that this isn't
easy. I think that I've figured out a baroque method to do this
using a dummy field in the parent record and a lot of tortuous
keeping track of intermediate values of CLASSSIZE.
This field, defined to require a value, would be kept empty in
the parent record's before update event, and the update thereby
canceled, until CLASSSIZE matches the number of records in the
child table. The dance of intermediate values involves remembering
the CLASSSIZE user is trying to get to when the cancel event
returns it to its original value--I doubt that I will do this;
the form is for internal use and users will just have to use a
little care. Anyway, the delete button will reappear when the
record is again accessed if CLASSSIZE<number of child records,
and an empty child record will show up if CLASSSIZE>number of
child records defined.

: Terry Kreft

:> I need to restrict the number of 'many' records to exactly the
:> CLASSSIZE value of the parent form's 'one' record.
:>

[Thelma Lubkin]

Terry Kreft <te*********@mps.co.uk> wrote:
: Rats, hit send too soon.

: If you want to control navigation that closely it's probably easier to carry
: it out yourself and get rid of the built-in navigation buttons.

: The following is some code which approaches the what your looking to do.
: In the main form
: Where
: Command5 navigates forwards
: Command6 navigates backwards
: The Subform control is called frmCOLORNAME_DETS

: ' *********************************
: Private Enum MOVE_RECORD
: mrPrevious = 0
: mrNext = 1
: End Enum
Thank you. This looks like it would indeed solve my problem

I don't understand why there isn't a way for you to provide your
own code for the built-in navigation buttons (other than doing
it from scratch.) It seems that I get into trouble whenever I
try to use a wizard. This form/subform was designed [by someone
else] using a wizard. I tried to use a wizard for a command
button--it would work fine until I tried to customize it and
then it wouldn't work at all--after the fourth try I finally noticed
that one of my customizations changed its name, but the code that
drove the command button still referred to it under its old name
sorry, I've wandered off topic...

Can you please explain why it's preferable to use the Enum
instead of simple integer for the parameter passed to MoveRecord
thanks, --thelma

: Private Sub MoveRecord(RHS As MOVE_RECORD)
: Select Case RHS
: Case mrPrevious
: With Me.RecordsetClone
: If Not .BOF Then
: .MovePrevious
: End If
<snip>
: --
: Terry Kreft

[Terry Kreft]

Using an enumeration for the parameter data type
====================================
1) The major advantage is you get intellisense kicking in.
So when you type
Call MoveRecord(

.... at that point intellisense drops a list of the enumerated values so you
can pick one.
2) Second advantage is it's self documenting.
You look at the line
Call MoveRecord(mrPrevious)

.... and you know that you are invoking the method to move to the previous
record whereas
Call MoveRecord(0)

.... achieves the same result but you have to go into the MoveRecord
procedure to see what effect the 0 parameter has.

3) Thirdly it's easy to change the values.
So the enum is
Private Enum MOVE_RECORD
mrPrevious = 0
mrNext = 1
End Enum

Say we want to change the procedure to allow us to move to the first or the
last record then we might change the enumeration to

Private Enum MOVE_RECORD
mrFirst = 0
mrPrevious 1
mrNext = 2
mrLast = 3
End Enum

and the code still works, because, for example, the call
Call MoveRecord(mrPrevious)

.... is now passing a 1 (instead of a 0) to the procedure but as the
procedure is referencing the enumeration it still works as it sees
mrPrevious as the value to look at.
The one disadvantage of an enumeration is that unlike a constant if you type
the enumerated value in and get the case wrong it doesn't autocorrect (and
changes the case in the enumeration). Which is a bit strange but a minor
problem when compared with the advantages.

--
Terry Kreft

"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> wrote in message
news:dl**********@uwm.edu...

Terry Kreft <te*********@mps.co.uk> wrote:
: Rats, hit send too soon.

: If you want to control navigation that closely it's probably easier to
carry
: it out yourself and get rid of the built-in navigation buttons.

: The following is some code which approaches the what your looking to do.
: In the main form
: Where
: Command5 navigates forwards
: Command6 navigates backwards
: The Subform control is called frmCOLORNAME_DETS

: ' *********************************
: Private Enum MOVE_RECORD
: mrPrevious = 0
: mrNext = 1
: End Enum
Thank you. This looks like it would indeed solve my problem

I don't understand why there isn't a way for you to provide your
own code for the built-in navigation buttons (other than doing
it from scratch.) It seems that I get into trouble whenever I
try to use a wizard. This form/subform was designed [by someone
else] using a wizard. I tried to use a wizard for a command
button--it would work fine until I tried to customize it and
then it wouldn't work at all--after the fourth try I finally noticed
that one of my customizations changed its name, but the code that
drove the command button still referred to it under its old name
sorry, I've wandered off topic...

Can you please explain why it's preferable to use the Enum
instead of simple integer for the parameter passed to MoveRecord
thanks, --thelma

: Private Sub MoveRecord(RHS As MOVE_RECORD)
: Select Case RHS
: Case mrPrevious
: With Me.RecordsetClone
: If Not .BOF Then
: .MovePrevious
: End If
<snip>
: --
: Terry Kreft