Count Field Change Event

[Guest]

Hi Folks,

Using Access 2003 with XPSP2.

I have designed a form (frmMain) with an embedded sub-form (frmSub).

frmSub contains a number of records that are displayed in continuous form
mode.

frmSub also contains a field (tbxCount) in its form footer that counts up
the number of records displayed in the Detail section (and seems to work ok).

What I want is for the change of the Count field to trigger the Change Event
so that - when the count reaches a given number (say 10) I can change the
AllowAdditions control to stop any additional records being entered.

At the moment all i'm using is a Debug.Print statement in the change event
for the tbxCount field to try and work out how to get the change event to
trigger. (So, it's not a naff piece of VBA stopping this working!!).

I've tried similar Debug.Print "Hello World" in the BeforeUpdate and
AfterUpdate events for this same field.

How do I get this to work? Is there a smarter way of acheiving my result -
to limit the number of records entered in frmSub to a predetermined number?

Thanks for the help.

 

[Brendan Reynolds]

Hi Folks,

Using Access 2003 with XPSP2.

I have designed a form (frmMain) with an embedded sub-form (frmSub).

frmSub contains a number of records that are displayed in continuous form
mode.

frmSub also contains a field (tbxCount) in its form footer that counts up
the number of records displayed in the Detail section (and seems to work
ok).

What I want is for the change of the Count field to trigger the Change
Event
so that - when the count reaches a given number (say 10) I can change the
AllowAdditions control to stop any additional records being entered.

At the moment all i'm using is a Debug.Print statement in the change event
for the tbxCount field to try and work out how to get the change event to
trigger. (So, it's not a naff piece of VBA stopping this working!!).

I've tried similar Debug.Print "Hello World" in the BeforeUpdate and
AfterUpdate events for this same field.

How do I get this to work? Is there a smarter way of acheiving my result -
to limit the number of records entered in frmSub to a predetermined
number?

Thanks for the help.

These events are only fired when data is changed interactively, by the user.
I haven't tested this, but I think you should be able to use the
BeforeInsert event of the subform instead. Something like ...

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.RecordsetClone.Count >= 10 Then
Cancel = True
End If

End Sub

 

[Guest]

Brendan

Thanks for the quick response, I'll give it a try and let you know.

Rgrds

Giles

 

[Guest]

Brendan,

Worked great - thanks for the advice.

Giles

 

[Guest]

That was awesome, Brendan! Thank you very much for posting this answer! It
helped immensely!