Post reply

Refreshing a Control

  • June 15, 2005 at 5:27 pm

    #164973

    If I have a form that opens another form, kind of like a popup, and the 2nd form does something that changes the value of the RowSource of a control on the main form, how do I refresh the control on the main form after I make the 2nd form go away?

    Here's the specifics:

    A form called Proposals has a combobox on it called fkClientID.  It's rowsource is "SELECT pkClientID, ClientName from tbl_Clients".  I have it set to hide the key, and display the name using column widths of 0";1.5".

    Next the the combo box is a button that says "add new client"

    The button pops up a dialog box where the user can enter the name, address, phone etc. of the new client.  Then, when they click Submit, my code checks to see that its not already in the DB, and if its not, it adds a row to tbl_Clients using an insert command. 

    I then fetch the ID of the row I just inserted using a "select top 1..." query.  It goes into a variable called NewClientLocationID.

    Now!  I want to set the value of the ComboBox on the parent form to the record I just inserted, so I've been trying things like:

    Forms!Proposals.fkClientID.Value = NewClientLocationID

    Forms!Proposals.Refresh

    Forms!Proposals.Repaint

    DoCmd.Close

    But to no avail!  The best I get is an inconsistent update. 

    Any ideas?

    Thanks -Aaron

  • June 16, 2005 at 1:58 am

    #566000

    Try REQUERYing the combobox directly:

    Forms!Proposals.fkClientID.Requery

    (Assuming the newly added item is not appearing in the combo box)

    Forms!Proposals.Requery   should work  too, but that would be doing more work than you need to (requerying the data behind the whole form)

     

    Refresh doesn't work because it won't bring any new records in, it just updates the records already there

    the Repaint isn't necessary either for this

  • June 17, 2005 at 2:15 am

    #566333

    You could try this, this is how I always handle this problem.

    'Code for your button

    Private Sub cmdAdd_Click()

    On Error GoTo Err_cmdAdd_Click

        Dim sDocName As String

       

        sDocName = <your form>

       

        Me.cmdAdd.Tag = ""

       

        DoCmd.OpenForm sDocName, , , , acFormAdd, acDialog

        'When you open your form dialog the code stops until the form is closed

        If Len(Me.cmdAdd.Tag) > 0 Then

            Me.fkClientID.Requery

     Me.fkClientID = CLng(Me.cmdAdd.Tag)

        End If

       

       

    Exit_cmdAdd_Click:

        Exit Sub

       

    Err_cmdAdd_Click:

        MsgBox Err.Description, vbInformation

        Resume Exit_cmdAdd_Click

    End Sub

    'Code for your "Add new" form

    Private privCtrl As Control 'Local variable for the form

    Private Sub Form_Load()

    On Error GoTo Err_Form_Load

               

        Set privCtrl = Screen.ActiveControl

    Exit_Form_Load:

        Exit Sub

    Err_Form_Load:

        MsgBox Err.Description, vbCritical

        Resume Exit_Form_Load

    End Sub

    Private Sub cmdOk_Click()

    On Error GoTo Err_cmdOk_Click

        If Me.Dirty Then DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

        privCtrl.Tag = Nz(Me.<The control that stores pkClientID>, "")

       

        DoCmd.Close

    Exit_cmdOk_Click:

        Exit Sub

    Err_cmdOk_Click:

        MsgBox Err.Description

        Resume Exit_cmdLukk_Click

       

    End Sub

    Private Sub Form_Unload(Cancel As Integer)

        Set privCtrl = Nothing

    End Sub

    Regards,

    Anders Dæmroen
    epsilon.no

  • June 17, 2005 at 9:12 am

    #566450

    Or, try the following syntax:

    Forms!Proposals.Controls!fkClientID.Value = NewClientLocationID

    in the popped-up form.

  • June 17, 2005 at 11:02 am

    #566510

    Cool. Thanks guys.  I'll try these and let you know how they work.

    Aaron

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply