Dropdown List Maintenance 1

[MeldrethMan]

I'm using a master list of items to feed the dropdown lists of several fields in a form and its subform. This is so there's just one maintenance form where field names and list item names can be entered.

When a user adds a new item I'm using NotInList to open the maintenance form. When he closes this I want all the dropdown lists to requery, both in the form and its subform.

Using this in the maintenance form's Close event doesn't do the job

If CurrentProject.AllForms("frmClients").IsLoaded=True then
Forms!frmClients.Requery
Forms!sfmClients.Requery
End if

Do I have to add code lines to requery each of the dropdown fields individually or is there a way of doing this in one go, both in the form and the subform?

 

[MazeWorX]

if its alot of forms loop through the open forms and requery if its only a few then code them

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

err that is code the forms requery not the controls

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[TheAceMan1]

How are ya MeldrethMan . . .

Might I suggest a button on the [blue]maintenance form[/blue] that performs the updates then closes the [blue]maintenance form[/blue].

To setup:
In the [blue]Tag[/blue] property of all comboboxes (form & subform), enter [blue]cbx[/blue] ([red]no quotations please![/red]). Then in the [blue]On Click[/blue] event of the button, copy/paste the following. Note: [blue]you![/blue] substitute proper names in [purple]purple[/purple] ...

[blue]   Dim frm As Form, sfrm As Form
   
   Set frm = Forms![purple][B][I]MainFormName[/I][/B][/purple]
   Set sfrm = frm![[purple][B][I]subFormName[/I][/B][/purple]].Form
   
   For Each ctl In frm.Controls
      If ctl.Tag = "cbx" Then ctl.Requery
   Next
   
   For Each ctl In sfrm.Controls
      If ctl.Tag = "cbx" Then ctl.Requery
   Next
   
   DoCmd.Close acForm, "[purple][B][I]MaintenanceFormName[/I][/B][/purple]", acSaveYes
   
   Set sfrm = Nothing
   Set frm = Nothing[/blue]

[green]Be sure to backup the db[/green] so you can come back to square one. Give it a whirl and let me know.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MeldrethMan]

Thanks Ace, perfect give or take a Dim ctl. I should have remembered the tag technique as you showed it to me years ago for locking individual records.

And thanks for responding MazeWorx. It's the individual controls on one form and its subform I'm needing to requery.