List Box Refresh? Frustrated!! PLEASE HELP!!

[SmartestIdiot]

I've been working on this issue for hours with no solution.
I have a Navigation form with a combobox in the mainform which auto populates the subforms.
In one of the subforms is a list box that updates values based of the mainform's combobox.


I then can click on the subform's listbox values to populate the fields in the same subform.

My problem is the listbox does not populate when the combox in the mainform is updated.
Instead it holds onto a blank list, or the values from the combox selection on entry into the subform.
I need to click on the actual tab of the subform to refresh the listbox options, so it does refresh. Just not when the mainform's combobox changes.

I'd like NOT to use any complex VBA, because I am not that savy with it. Is it possible to use the access 2010 macro builder?

PLEASE HELP

[ItsMe]

You could probably place VBA code in the subform's VBA module that has the tab control. This would simplify things greatly. You will first need to identify the subform in your navigation pane and then the name of your tab that contains the ListBox. Knowing the name of the tab and the ListBox controls will help to create the code and create an event handler for clicking the tab.

When you click a tab in your tab control, you change the value of the tab control. For example, the third tab in your control will have an index of 2. Your VBA will need to use this index to determine if the desired tab is currently being viewed.

Some example code to place in your tab controls On Change event. Not too complex.

If me.TabControlName.Value = 2 then
Me.ListBoxName.Requery
End If

[SmartestIdiot]

I'll give that a shot, but what about when I change the combo box in the main for?

[ItsMe]

I'll give that a shot, but what about when I change the combo box in the main for?

You can post the rowsource of your listbox here. Either the listbox is dependent on the combo or it is not.

[SmartestIdiot]

It is in the row source, it just won't update when the change is made. It will populate when I press the tab control, but I'd like it to automatically populate like the rest of the text boxes.

[SmartestIdiot]

Is there a code in the macro for the combo box to make the list box request on change? Or something like that

[ItsMe]

Is there a code in the macro for the combo box to make the list box request on change? Or something like that

There is. I have been trying to avoid an event handler for controls in the main form. YOu would need to reference many object names to requery the listbox and it could get confusing, especialy with the use of the Navigation Control.

Perhaps using the On Enter event for the listbox would be best in your case. One simple line may do the trick.
Me.ListBoxName.Requery


By going directly to the subforms class module you do not need to include the names of the other forms and controls in your code.

[SmartestIdiot]

I'll be more specific in explaining the problem. When the combobox in the mainform selects a name, I want the listbox in the subform to display a list of the RecordIDs in a related table. From that list box I can select the RecordID and the same subform will populate. I'm almost there except that when I change the name in the mainforms combobox, the list does not requery. I can select the tabcontrol and this will refresh the listbox, however I would like it to refresh when the combobox name changes.

I have the row source of the listbox query the combox, and the subform query the listbox. Any thoughts

[ItsMe]

.... Any thoughts

Add Me.ListBoxName.Requery to the ListBox's On Enter event.

I am suggesting this route because it is my understanding the list box resides within a tab control, that is within a subform, that is within a navigation control.

If you want the requery code to go in the afterupdate event of the combo that is within the Navigation Control, you will need to reference the names of all the containers/controls. These names are found in the "Other" tab of the properties window. You have to carefully select each control and then document the names found in the name property field.

[SmartestIdiot]

So I finally got the listbox to requery by placing some vba in the update to of the mainform On Update(). However now the other subforms are getting a debug. I guess since the list isn't in all the subforms its a problem.

[SmartestIdiot]

Your understanding is correct.

I tried the me.listboxname.requery, and it does work if you click inside the listbox.

I did get some VBA placed in the Mainforms combobox On Update event that does what I need, but then it looks at the entire subform and not the tab. So it works in the tab I need to work, but gives me a debug in the other tabs which do not contain the listbox.

The VBA is forms![navigation form]![navigationsubform].form!
[list370].requery

Is there a way to direct the reference to the relating tab only without including the rest of the subform?

[ItsMe]

If you would like to upload your DB I will see if I can get it to work for you. Remove any private data and zip the database before uploading.

[SmartestIdiot]

since most of the data is sensitive info, It will take some time to put it together. My apologize for a delayed response but I'm on Japan time now.

[SmartestIdiot]

Gave up on the whole idea and decided not to use the Navigation Control Tabs.