Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Bucharest, Romania
    Posts
    16

    Question Unanswered: combo box refresh

    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.

    I've tried with requery but seems not to work.

    Appreciate any ideea.

    Daniel

  2. #2
    Join Date
    Sep 2003
    Posts
    228
    Let's see your code...
    include the name of the form, subform and the combo box...
    There is special syntax for referring to a control on a subform.

    There is a sweet table on how to do it here:
    http://www.mvps.org/access/forms/frm0031.htm

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    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:

    Code:
    Private Sub myComboName_NotInList(NewData As String, Response As Integer)
    DoCmd.OpenForm "myFormName", , , , acFormAdd, acDialog
    myComboName.RowSource = "SELECT blah...blah..blah...whatever it is;"
    End Sub
    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).


  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    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!).

    For instance

    Code:
    Private Sub Form_Unload(Cancel As Integer)
        
        If FormIsLoaded("frmJobs") Then
            Forms!frmJobs.cboCustomer_ID.Requery
        ElseIf FormIsLoaded("frmMaterialsDetails") Then
            Forms!frmMaterialsDetails.cboCompany.Requery
        End If
    End Sub
    
    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
            End If
        End If
    
    End Function
    MTB

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Hmmmmm...

    Code:
    Modal Property (Access97, Access 2K+) 
     
    You can use the Modal property to specify whether a form opens as a modal form. When a form
    opens as a modal form, you must close the form before you can move the focus to another object.
     
    Setting
     
    The Modal property uses the following settings.
     
    Setting Description		 Visual Basic
     
    Yes		 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.
     
    Remarks
     
    When you open a modal form, other windows in Microsoft Access are disabled until you close the
    form (although you can switch to windows in other applications). To disable menus and toolbars
    in 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. You
    can 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 can
    switch 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 from
    Datasheet view to Form view.
     
    Note You can use the Dialog setting of the Window Mode action argument of the OpenForm
    action 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.

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi CyberLynx

    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!

    I just thought someone else might benefit!

    Cheers

    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •