Please put me out of my misery

[andygp40]

I have a form that displays a Club's Teams, e.g. each of the football teams or badminton teams. A sub-form then shows all the members of the team - the squad. The first field of the sub-form is a combo box that attempts to only show the members of a club.

The combo box populates fine initially, but as I scroll through the teams, the change of teams is not reflected in the combo box's contents.

What do I need to do the refresh the combo box? Please help.

Many thanks
Andy

 

[casey]

You could put the following in the Form's OnCurrent Event....

Me.YourComboBoxName.Requery

HTH

 

[andygp40]

Still struggling, I'm afraid. Tried Requery on the mainform, but the Combo box isn't known. Tried on the subform and no success. Have I missed the obvious?

 

[casey]

you need to replace YourComboBoxName w/ the actual name of the combo(click the combo...view properties...Name)

if that doesn't work use a full reference...

Forms!YourFormName.YourComboBoxName.Requery

Try that....

 

[andygp40]

Please confirm - "YourFormName" is the name of the subform.

Thanks

 

[casey]

I'm sorry....

I didn't see you had a subform....

Reference the subform control by....

Forms!YourMainFormName.YourSubFormName.Form.YourComboBoxName.Requery

 

[andygp40]

Tried Forms!MainForm.MemberNo.Requery and Forms!SubForm.MemberNo.Requery without success. Please help a fool who is slowly going mad with this!

 

[andygp40]

Progress! Oh deep joy! Sanity almost restored.

As I scroll through the mainform the combo box changes. Fantastic!!

A couple of little additions. If I change the club or add a new team, the combo box doesn't work yet. I guess I should repeat this on some other events. Would you please point me in the right direction.

Thanks
Andy

 

[casey]

When you reference a subform, you must include the name of the MainForm that houses your SubForm. I know it's confusing, but it's logical. Make sure that the names are the actual names you have given your forms.

See prior post

 

[casey]

Combo requery after change

You can put the following code in the Combo's OnChange Event...

Me.Requery

Since it refers to itself, you should only need the above...

If it doesn't work, then try the full name again.

 

[andygp40]

I'm afraid you lost me there. I added the Forms!YourMainFormName.YourSubFormName.Form.YourComboBoxName.Requery reference and the Combo Box now requeries. Thank you.

I think though I need to go a step further and identify the other events when the Club Id changes as when I update or insert a new team the combo box isn't there yet.

As I say, making progress. Thanks for your help. Any more guidance would be appreciated.

 

[andygp40]

I've added Forms!YourMainFormName.YourSubFormName.Form.YourComboBoxName.Requery to both the event "On Current" on the form and to the "On Change" event on the Club Id field. It now seems to work as expected. Thank you ever so much! I shall sleep now.

Regards
Andy