Triggering Combo Box Refresh

[sproggit]

Solved: Triggering Combo Box Refresh

Hello everyone... First time post [please be gentle...]

I am hoping that someone can guide me to a mechanism that I can use to forcibly refresh a ComboBox content based on a form-related event. Right now I am quite unable to do this, but also 99% certain that my process for priming my ComboBox in the first place is also likely *really* bad practice [so a suggestion on improvement would also be very welcome].

Here's the context... [and in case this is relevant, I am using Access2013].

I am using a "Menu" form to act as a routing mechanism to allow a user to move between different forms within my Access App. One set of functions involves the creation, maintenance and potential deletion of Parameters, which are essentially simple rows in a "Name-Value Pair" table.

I have 2 activity-related Forms associated with these Parameter records [Update] and [Delete]. I could use a single form, but am splitting because my Menu form also allows me to apply role-based access control, so I am being quite atomic with the functionality I put on each form...

On both my "Mod Parameter" and "Delete Parameter" forms, I have some truly horrible code in my "Form_Load()" method:-

Dim strSQL As String
strSQL = "SELECT nvpName, nvpID from tblNVP"
Me!nvpModSelect.RowSource = strSQL
​

OK, so far so good.... [No, not really... I know that's awful and as soon as I figure out how, I will fix that]. I can load the "Mod" Form, select, edit and save a Parameter record and all is good. I can then quit the "Mod" Form, go back via the Menu to the "Delete" Form, and delete the Parameter I've just modified. Everything is still working OK...

Now comes the crunch... If I go back to my "Mod" form and check my Combo box, the relevant item now shows as "#Deleted". I can quickly correct this at run-time by hitting F5, but that seems crude...

One more relevant piece of information.... Each time I move between forms, my VBA logic does something a bit like this:-

Me.Visible = False
DoCmd.OpenForm "frmMenu"

​

Navigating around this application works perfectly, as do all my mechanisms to maintain the contents of this and other tables. I realise that my code is not working because the Form_Load() event only gets called on the initial load - and that using "Me.Visible = False" isn't un-loading the Form, just hiding it. I'm also aware that my initial logic to prime these ComboBoxes absolutely stinks...

I'd be very grateful, therefore, for some advice... I can move the SQL logic for the Combo Boxes into a dedicated query and associate that with the boxes to do the initial data load, but I am struggling to see what Form-related event I can use to enable me to trigger a ".refresh" or ".reload" method... Specifically, does "DoCmd.OpenForm" pass an event handle to the Form in question? Can a Form that is opened like this "know" that it has been opened?

Any advice would be much appreciated. Congratulations on reading all the way to here...

Thanks in advance for any guidance!

 

[arnelgp]

on your "DELETE" form's On Delete event, put this code:

Private Sub Form_Delete(Cancel As Integer)
On Error Goto Err_Handler
Forms!ModParameter!nvpModSelect.Requery
End Sub

replace the blue-colored text with the name of your form.

 

[sproggit]

Thank you for such a prompt response!

Just to clarify your suggestion here... when you write,

"Forms!ModParameter!nvpModSelect.Requery"

does this basically tell the "Delete" form to "reach into" the "Mod" form and refresh the query on the Mod form combo box? [ I *think* that is what this does, would just like to check I understand, rather than following along blindly...]

Thanks again!

 

[arnelgp]

yes it does, on each record you delete on "delete" form it Requeries your combobox. you can do the same if you have form for adding, you do it in the After Update event of that form.

 

[sproggit]

Solved: Re: Triggering Combo Box Refresh

Thank you... I clearly need to tidy up my code a bit, but I think this solves the problem. I should be able to repeat this invocation for any other forms that might have the relevant combo box populated. Of course, I won't be able to guarantee that those other forms have been loaded at that point, but suspect that the "on error" trap will allow me to handle that use case neatly.

Thanks for your kind help, it's much appreciated.

 

[arnelgp]

you may elect to trap or ignore the error:

Private Sub Form_Delete(Cancel As Integer)
' don't care if the form is open or not
On Error Resume Next
Forms!ModParameter!nvpModSelect.Requery

End Sub