dynamically updating combobox

[snowman]

sorry if this q has already been asked, but i couldnt find what im looking for after searching..

i have a main form. there is a combo box that is that is bound to tblSupplierDetails. this combox displays the records stored in tblSupplierDetails fine. now beside this combobox i have a hyperlink that opens a subform called 'subfrmAddNewSuppliers'. this subform works correctly - ie. when details are entered here, they are stored in tblSupplierDetails

my prob is that if the user adds new supplier details via the subform, these details do not appear in the combo box for the user to select when they are returned to the main form.. they appear if the form is restarted

Ive inserted the statesment Me.ComboBoxName.Requery (with ComboBoxName ammended) into the event procedure for After Update for the combobox.

however, with this the new details which are added via the subform are replacing the previously added record - so theres only ever one record in tblSupplierDetails.. can someone pls help me with this?

 

[RuralGuy]

Please post the RowSource of the ComboBox? It sounds like you could use the NotInList event of the ComboBox to add the new supplier. Mastering the Combo Box NotInList Event

 

[snowman]

the row source is:

SELECT tblSupplierDetails.SupplierID, tblSupplierDetails.CompanyName FROM tblSupplierDetails;

 

[RuralGuy]

One trick is to put your Me.ComboBoxName.Requery in the OnEnter event of the ComboBox!

 

[snowman]

nope no good

same problem - the previously entered supplier detail is getting replaced with the newly entered details, leaving me with only 1 record in my supplierdetails table... any other ideas?

 

[RuralGuy]

How many records are in the tblSupplierDetails table? Do they all show when you first open the DropDown for the ComboBox? The suggestion I gave you *will* work unless something else is going on that we don't know yet. Do you care to post your zipped up db with any sensitive data removed? It might be easier to troubleshoot that way.

 

[snowman]

thanks for this...

ive tried adding the following code to the 'Close' button on my subform:


Forms![frmShippigNotice]![Combo66].Requery
DoCmd.Close

but still no good - same problem occuring. ive attached the db - i really appreciate this

 

[RuralGuy]

Put:

Private Sub Combo66_GotFocus()

Me.Combo66.Requery

End Sub

In the GotFocus event of Combo66. Remove the code in the AfterUpdate event of Combo66.