Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Forms FAQ

How to

How do I update a main form record counter while adding records in a subform by SBendBuckeye
Posted: 14 Oct 02 (Edited 15 Oct 02)

Problem to solve:

I have a textbox on my main form which displays the number of records in the subform but it does not immediately update when I add records into the subform.


This solution uses a technique called WithEvents to enable the Main form to react to events happening in the Subform.

The sample code below is based on the following scenario. I have 2 tables, tblState has 2 fields, an autonumber primary key named StateID and a text field named State. tblCity has 3 fields, an autonumber primary key named CityID, a text field named City and a numeric foreign key into tblState named StateID.

StateID    State
1       IN
2       OH
3       IL
4       MI

CityID    City    StateID
1       Orrville  2
2       Canton    2
3       Dayton    2
4       Albion    1
5       Gary      1
6       Elkhart   1
7       Niles     4
8       Zeeland   4

frmCity is a continuous form with 3 controls on it, txtCityID which is hidden, txtCity and txtStateID which is needed for a link to the main form when this form is used as a subform.

frmState has 4 controls on it, txtStateID which is hidden but needed for the link to the subform, txtState, SubformCity which is a subform container holding frmCity as a subform and txtCityCount which holds the number of cities on the subform for a given state.

frmState has a private sub GetCityCount which populates txtCityCount. If txtState is null it assigns zero to it, otherwise it uses the DCount function to count the number of Cities with a StateID matching the current form txtStateID.

Private Sub GetCityCount()
   If IsNull(txtState) Then 'In case on the new record
      txtCityCount = 0
      txtCityCount = DCount("CityID", "tblCity", _
                            "StateID = " & txtStateID)
   End If
End Sub

The following steps will allow frmState to react to additions made in the subform as I go:

A. The HasModule form property for frmCity MUST be set to true, even if there is no other code associated with this form

B. In the Declarations area of frmSate, insert this code which declares a linkage between the main form and subform
   Private WithEvents frmSubform As Form

C. In the On Load event code for frmState put this code which creates actual link between the mainform and subform
   Set frmSubform = SubformCity.Form
   frmSubform.AfterInsert = "[Event Procedure]"

D. In the On Current event code for frmState put this code which populates txtCityCount for the state currently being processed - see above for procedure GetCityCount code

E. Create following Private Sub in frmState class module
   Private Sub frmSubform_AfterInsert()
   End Sub

The procedure in Step E is the code that actually takes advantage of the Linkage created above.

In step C, I told it to use the form associated with the frmState subform control (the bucket, not the actual subform itself) and also told frmState that it needed to react to the AfterInsert event on the subform by setting it equal to [Event Procedure].

If there is AfterInsert code in the frmCity class module, it will be executed first and then the procedure in Step E will be executed. This allows the city counter on the main form to be updated on the fly as cities are added on the subform.

To make this code work for your situation, simply insert your main form subform container control name for SubformCity in Step C above and replace GetCityCount with whatever procedure name you are using.

In this example, I wanted to change a record counter so I used After Insert.

Any of the events on the actual subform (frmCity, NOT SubformCity the container on the main form frmState) can be accessed by setting it to [Event Procedure] in Step C and then writing a corresponding procedure similar to Step E (frmSubform_FormCurrent, frmSubform_BeforeUpdate, etc).

If you are not familiar with this technique, it is similar (but only in a very general conceptual manner) with the idea of raising your own errors and then reacting to those errors in the code.

My initial exposure to this whole concept was in Access2000 Developers Handbook by Getz, Litwin and Gilber, published by Sybex.

Good Luck!

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close