'Refreshing' the contents of a combo box list

[Everton]

Hi, I'm not sure if I'll be able to explain this problem clearly, but here goes anyway. I have a combo box in a form, and the combo box has a lookup row source like "Select DISTINCT [fieldX] From [tableY]". I have a button on the form to bring up a popup form to add a new record to tableY. The problem is that when I close this popup form the new record does not show up in the combobox drop down. Only when I close the form and reopen it does the new record appear. I have tried using a macro to close the popup and then repaint the from, and also close the popup and save the form but neither seems to work. Any advice?

Thanks, Paul.

 

[Former]

Hi,

On the Close event of your pop up, you need to requery the combo.

A bit of code like this should do the trick

Private Sub Form_Close()
Forms("Form_Name").[Combo_Name].Requery
End Sub

I hope this helps

 

[simongallop]

***Just noticed that Former beat me to it!!***

Use a command button on the popup form to close it. When you create the button the wizard will kick in and there is an option for the form to be closed. Once you have done that go into the properties of the button and the code created by the wizard will be found in the On Click event. look at the code and I think (haven't got time to test) you need to use the Requery method.

Private Sub cmdExit_Click()
Dim ctlCombo As Control

On Error GoTo Err_cmdExit_Click

' Return Control object pointing to combo box.
Set ctlCombo = Forms!frm1!cmbTest

' Requery source of data for combo box.
ctlCombo.Requery

DoCmd.Close

Exit_cmdExit_Click:
Exit Sub

Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click

End Sub

HTH (and hope it works!)

Simon



[This message has been edited by simongallop (edited 11-02-2000).]

 

[Everton]

Thanks guys, that look like just what I need, I'll let you know if it works.

 

[Everton]

That works fine for one of my combo boxes, but I also have the same situation for a combo box in a subform. I've seen other answers on this site that deal with subform requery, but I can't get it to work. I've a feeling I'm just not getting the syntax right. Any ideas?

 

[Everton]

I've sorted it now, after a chance flick through the Office 97 Bible. I used: Forms![form]![subform].Form![control].Requery

Thanks for your help, Paul.