Refresh/Requery list box on subform change (Access XP)

Topic

New Reply

It’s getting a little late and I’ve gone round and round on this, can someone please give a weary clueless guy a clue? When I change a value in a field on a subform, I need to requery or refresh the results in a List Box on the main form. Now I assume it would trigger on the “on change event” of the subform field but for the life of me I can’t come up with the proper syntax. i.e Forms!frmPayment!lstContr.Refresh Thanks in advance.

Reply | Quote

Replies

Don’t use OnChange because that would requery or refresh the listbox with every keystroke. If what you’re doing in the subform will actually affect the records populating the listbox, use Requery, not refresh. Use the AfterUpdate event of the subform to requery your listbox. The syntax would be :

Parent!lstContr.Requery

Reply | Quote

Thanks very much for your quick reply. I’m almost there, but the list box still doesn’t refresh. I’m thinking it has something to do with the fact the list is based on a query filtered by a combo box “cboDealer” on the Parent form. The list box won’t refresh until I re-select from the combo box.
Essentially, I allow the user to choose a Dealer with outstanding invoices from “cboDealer”, “lstContr” is a list of unpaid contract balances for that dealer, “On Click” in the list box adds a record to a table and populates the subform. The user has the option to change the “txtPayAmt” on the subform to reflect over and under payments, and I’d like to refresh the “lstContr” to reflect that. Sounds a little convoluted, but I can’t think of another way to do it.

Reply | Quote

Do I understand that you select a dealer from the combobox, which then somehow populates the listbox with unpaid contract balances for that dealer? How is the listbox repopulated at that point? Then the subform gets populated by something that happens in the listbox. What exactly is going on there, or do you mean that the subform is linked to the parent form on the value of the listbox? Then does changing something in the subform mean that a record in the subform should no longer show up in the listbox? Trying to make them interdependent is going to be difficult to make work properly. The normal course of things is for the subform to be dependent on the parent form and its controls, not the other way around, which is what you seem to be trying to do.

Reply | Quote

Yes when the Dealer is selected in the combo box I requery the listbox which populates with contracts and balances filtered from an underlying query containing calculated balances for each contract. Price – Payments + Adjustments. Then when a contract is selected in the combo box, the on click event Adds a record to the payments table and requerys the list box effectively removing that contract. It’s at that point the user “may” change the payment amount. Over and above the problem accomplishing this, all of this requerying makes for a slow form so I’ve pretty much decided to leave the list box as is and give the user the option of a Requery button on the parent form if they wish to see the updated Contract List. Thanks again for your help.

Reply | Quote