How do I update a main form record counter while adding records in a subform

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.

Solution:

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.

tblState
StateID State
1 IN
2 OH
3 IL
4 MI

tblCity
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
Else
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
GetCityCount

E. Create following Private Sub in frmState class module
Private Sub frmSubform_AfterInsert()
GetCityCount
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!