Update subform with combo box...arg

[ST4RCUTTER]

This is so simple but I can't seem to make it work. I've poured through about 30 posts and tried all kinds of AfterUpdate event strings to no avail. Help!

Here is the simple setup. I have one table that lists employees and their corresponding teamleader. A grouped query provides the record source for a combo box. The idea here is that you select a teamleader from the combo box and a subform updates to show all the corresponding employees. This subform is based on a simple query of all fields on the table.

combo box = cboteamleader
mainform = frmTechMaintenance
subform = frmEmployees

I've finally settled on this...


Private Sub cboteamleader_AfterUpdate()

Me!frmEmployees.Requery

End Sub


...as having the greatest chance of success based on other posts. Not sure why it doesn't work. It just doesn't update.

 

[boblarson]

You have to use the correct syntax to refer to the subform

Forms!frmTechMaintenance.frmEmployees.Form.Requery

 

[pbaldy]

The recordsource of the subform would have to use the combo in its criteria. Does it?

 

[Joebax]

I had trouble with this. The combo box would not move the record pointer of the records in the main form.

I used the wizard that uses a combo box to 'locate a record based on the combo box' that built some code behind the after_update property.

The code generated by the wizard was this.

Dim RS As Recordset

Set RS = Me.Recordset.Clone
RS.FindFirst "[Stock_ID] = " & Str(Nz(Me![cboStocks], 0))
If Not RS.EOF Then Me.Bookmark = RS.Bookmark
RS.Close

Hope this helps

 

[ST4RCUTTER]

Thank you for the replies boblarson, pbaldy, and Joebax. To answer your questions:

1. I tried your code with the additional syntax reference to the main form (Forms!frmTechMaintenance.frmEmployees.Form.Requery) but it failed to update my subform.
2. Yes pbaldy, the subform is based off a query where the combo box value provides the criteria for the teamleader field.
3. Joebax, there are no records in the main form. The main form houses the combo box and the subform. The subform is supposed to update once I select a value from the combo box on the main form. Thanks anyway!

 

[pbaldy]

Can you post a sample db?

 

[ShaneMan]

Hey Cutter,

Try this and see if we have any luck:
Forms!frmTechMaintenance!frmEmployees.Form.Requery

HTH,
Shane