This should be simple.
I have a combo box in a subform based on a table. when I enter a value that it's not on the list, a form it's opened, that allow me to add the new value on the table x. I want that after I close the form the combo box list to be refreshed, containing also the new value.
Obviously, you are openning your other form from within the NotInList event of the ComboBox on your SubForm...Try this...
Open your other form as Dialog and then under the Docmd.OpenForm statement (next line in code) reneter the RowSource for your ComboBox. Here is an example:
Private Sub myComboName_NotInList(NewData As String, Response As Integer)
DoCmd.OpenForm "myFormName", , , , acFormAdd, acDialog
myComboName.RowSource = "SELECT blah...blah..blah...whatever it is;"
Now when a form is openned in Dialog Mode, no code is run after the OpenForm statement until the form is closed again. This means then, once the Form has loaded, opened, you have entered the desired data, and then closed the form the [myComboName.RowSource =] line will be fired only once the form is closed. The combobox is automatically updated by issuing the RowSource (even though the query is the same).
Unless they have changed things after Access 2k, I don't think code execution stops when a form opens like in excel !?.
Even if it did why not just requery the combo?
I thing the combo control should be Requried either in the form activate event OR in the unload event of the form that updates the table (you should make sure the form, and control are open before you do this!).
Private Sub Form_Unload(Cancel As Integer)
If FormIsLoaded("frmJobs") Then
ElseIf FormIsLoaded("frmMaterialsDetails") Then
Function FormIsLoaded(ByVal strFormName As String) As Boolean
FormIsLoaded = False
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
FormIsLoaded = True
Modal Property (Access97, Access 2K+)You can use the Modal property to specify whether a form opens as a modal form. When a formopens as a modal form, you must close the form before you can move the focus to another object.SettingThe Modal property uses the following settings.Setting Description Visual BasicYes The form opens as a modal form in Form view. True (–1)No (Default) The form opens as a non-modal form in Form view. False (0)You can set this property by using the form's property sheet, a macro, or Visual Basic.RemarksWhen you open a modal form, other windows in Microsoft Access are disableduntil you close theform (although you can switch to windows in other applications). To disable menus and toolbarsin addition to other windows, set both the form's Modal and PopUp properties to Yes.You can use the BorderStyle property to specify the kind of border a form will have. Typically,modal forms have the BorderStyle property set to Dialog.Tip You can use the Modal, PopUp, and BorderStyle properties to create a custom dialog box. Youcan set Modal to Yes, PopUp to Yes, and BorderStyle to Dialog for custom dialog boxes.Setting the Modal property to Yes makes the form modal only when you:Open it in Form view from the Database window.Open it in Form view by using a macro or Visual Basic.Switch from Design view to Form view.When the form is modal, you can't switch to Datasheet view from Form view, although you canswitch to Design view and then to Datasheet view.The form isn't modal in Design view or Datasheet view, and also isn't modal if you switch fromDatasheet view to Form view.Note You can use the Dialog setting of the Window Mode action argument of the OpenFormaction to open a form with its Modal and PopUp properties set to Yes.
The FormIsLoaded code above is a duplicate of the already long used IsLoaded function.
Thank for info on Modal, Popup etc, but I am aware of this (but not necessarily all its implications because the help file does not explicitly refer to code execution/suspension!). This may be more obvious if you use macros, which I hardly ever do.
On experimenting further with the doCmd.OpenForm, it is true that code execution is suspended when the form is open with the OpenArgs set to acDialog (but not otherwise). As they say, you learn something new every day.
I have always set modal properties etc. in from design view before, and the acDialog argument sets all 3 properties, which I do not normally want, which brings me back to the original scenario!
By the way, I was not claiming the FormIsLoaded code as my own. As you pointed out, this, I imagine, is standard code found in the first Access book I consulted, and have never modified, just keep pasting into new applications!