Update Combobox - Subform

[DLF]

I have a form called NewRequisitions that had a combobox in which the users select the Supplier (name, address, shipping details). I created a button to open a subform called NewSupplier so that they can add a new supplier to the Supplier table as needed. My code works okay for the first "NewSupplier" entry - the table and combobox are both updated and the new supplier is available for selection in the dropdown list. However, when there is a 2nd record that needs a NewSupplier added the combobox list doesn't get refreshed. The record is added into the table okay but you can only view it if the NewRequisitions (main) form is closed and then reopened.

Here is the code in my subform:

Private Sub Form_Close()

If CurrentProject.AllForms(NewRequisitions).IsLoaded Then

'save the record if there have been any changes
If Me.Dirty Then Me.Dirty = False

'requery combobox on other form
Forms!NewRequisitions!ComboSupplier.ReQuery

'yield to other events - and put the new list into use
DoEvents

End If

End Sub
-----------------------------------

Any assistance would be greatly appreciated!!!

 

[MStef]

Hello DLF!
Try this;
On Current event
Me.ComboBoxName.Requery

 

[DLF]

MStef,

Help!! I'm a newbie to Access - where would I place this code? Main form? Subform?

Thanks loads!!!

 

[Mile-O]

'save the record if there have been any changes
If Me.Dirty Then Me.Dirty = False

Does that actually work? I'd have thought DoCmd.RunCommand acCmdSaveRecord was the line to use.


Also, with regards to your latest question about which form. Try both; nothing like a bit of trial and error.

 

[DLF]

SJ - I've tried both Me.Dirty and CmdSaveRecord - both act the same. The first change in the subform will update the combobox. The 2nd time the subform is accessed it doesn't update the combobox. The record IS being saved to the table, however...... URGGGGGG........

 

[DLF]

Well, I guess with a little trial and error it's working. I used MStef's requery idea and put the code event in the Combobox "on enter" property. I'm just wondering if the tblSupplier gets large (which is populating the Combobox) if it will slow the form down any??

Thanks for the suggestions.............

Diana

 

[godofhell]

What happens if you click on the Refresh button under the Records menu? If clicking the refresh button makes the values show up then you can add the following to any event on the form or controls.

docmd.Runcommand accmdrefresh

 

[DLF]

godofhell ~

That worked perfectly! Thanks for the assistance - greatly appreciated!!!!!!!

Diana