Refresh combo

[CEH]

I can't seem to find how to refresh or requery a combo on a subform.... I know I've done this before.. but nothing seems to be working! Have a form "frmTabEntry" a subform container "frmInspDetailSub" and a combo called "cboAreaID" Now how it works is when you want to add a new "Area" you open another form (datasheet) to add it... When you close this form and click on the combo I want the new item to be in the dropdown.... I've tried a requery on the subform..an onclick on the combo to requery...but all seems to throw errors... suggestions for my brain freeze????

 

[CameronM]

Check the link below to make sure you are referencing the combo box in the subform correctly. If you are then you should be able to requery the combo by redefining the Row Source property.

http://support.microsoft.com/kb/209099

Forms![ParentForm]![ChildForm].Form![ComboBox].RowSource = "NameOfQuery"

 

[CEH]

That didn't work.
I used ...Forms!Mainform!Subform.Form!Combobox.Requery ... on the on the afterupdate event of the datasheet form that you open to add an item to the combo. One problem persist... Even thou it may never be used... if you delete an item from the list then return to the combo, it shows as "#Deleted" until you close and reopen the main input form.
The DB is so old...I did it 4 years ago.. I should just start new. (I've learned much in those 4 years)

 

[CameronM]

Without a little more code, I am not sure what is going wrong, as I have used similar logic in a number of databases.

You mention that you used the

afterupdate event of the datasheet form that you open to add an item to the combo

I would normally use the Form_Close event, but you should insert a breakpoint to make sure afterupdate is firing when you expect.

Another option is to use the NotInList event of the combobox to create the new record - the combobox has an option to requery after the new data is created.

If NewData <> "" Then
'...add code here to create a new record with an INSERT query
'then refresh the combo and choose the new value
Response = acDataErrAdded
blnIsNewAddress = True
End If

 

[CEH]

Might try the "not in list" on the combo.... never used that before... But as I say, this one is so old I would probably be better off rebuilding it. On the pop up form....After that form is closed....to move back to the main form and combo... would it matter which event it was on? Wouldn't the "After update" and "on form close" have both fired anyway?

 

[CameronM]

Did you put a breakpoint in your VBA code to see if the AfterUpdate event is firing?

I have just set up a couple of forms to test this and used the syntax previously posted and the combo in the subform is getting the udpated values on AfterUpdate.

The only difference between AfterUpdate and Form_close would be that AfterUpdate gets fired after every row change, so if you add several records at once, or if you change several records, the event will be triggered each time.

 

[shadow9449]

What I usually do is not set the record source of the combo box until the user clicks into it. That makes the form load a bit more efficiently (especially if you have many combo boxes on the form) and the best part is that the combo box's data is always populated with the latest data.

SHADOW