Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2

    Exclamation Unanswered: Update form properties after sub form close - continued

    Further to my previous post on the same issue.

    I have a form (CustomerInformation) that opens a sub form (CustomerInfoPopup) via a command button, where I can either edit the current record or add a new record.

    The main form has command buttons that allow actions based on the contents of a field. (Send an email if there is an email address) I use the OnCurrent event procedure on the main form to make the command buttons either active or inactive;

    [code]
    If IsNull(Me.EmailAddress) Or Me.EmailAddress = "" Then
    Me.SendMail.Enabled = False
    Else
    Me.SendMail.Enabled = True
    End If
    [/endcode]

    The problem I have is that if I edit a record, then close the sub form, the state of the buttons on the main form is not changed. i.e the main form does not re-test the current record to update the form properties after the sub-form is closed.

    Currently I am using requery on the OnClose event procedure of the sub form to update the current record;
    Code:
    Private Sub Form_Close()
    [Forms]![CustomerInformation].Requery
    End Sub
    but what this does is reset the current record to the first record in the db so the main form now shows record 1 of the db, rather than the record that was just edited.

    I have tried using refresh instead of requery, but haven't been able to get it to work.

    How do I update the main form properties after I close the sub form, so that the command buttons active/inactive state is correct, without losing the record position in the db?

    Thanks to all in advance.

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    Try:
    Code:
    [Forms]![CustomerInformation].Form.Refresh

  3. #3
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Thanks for the reply.

    I had tried tried your suggestion (on the OnClose Event Procedure of the sub form) before I posted but it doesn't work fully.

    Quote Originally Posted by billmeye View Post
    Try:
    Code:
    [Forms]![CustomerInformation].Form.Refresh
    When the sub form is closed the current record is still displayed, which is what I want, however the form properties are not updated.

    eg.
    When I go to a record on the main form with no email address the cmd btn to send an email is inactive. (good)

    Then I open the sub form to edit the current record and enter an email address.

    Then I close the sub form, the current record is still displayed in the main form (good) with the new email address (good), but the cmd btn to send an email is still inactive (not good).

    I'm finding this quite frustrating as I would have thought the the refresh cmd would do exactly what I am after.

    Do you have nay other suggestions?

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If you open CustomerInfoPopup in Dialog Mode (which stops your VBA code, until CustomerInfoPopup is closed, you can then Requery your original Form and return to the Record, like this:

    Where [UniqueID] is a Field that is unique to a given Record:

    Code:
    Private Sub OpenCustomerInfoPopup_Click()
     
     Dim UF_Rec As Integer
       
       UF_Rec = Me!UniqueID
       
       DoCmd.OpenForm "CustomerInfoPopup", , , , , acDialog
       Me.Requery
       Me.Recordset.FindFirst "[UniqueID] = " & UF_Rec 
    
    End Sub


    The above assumes that [UniqueID] is defined as a Number Field. If it is defined as a Text Field, replace the line

    Dim UF_Rec As Integer

    with the line

    Dim UF_Rec As String

    and the line

    Me.Recordset.FindFirst "[UniqueID] = " & UF_Rec

    with the line

    Me.Recordset.FindFirst "[UniqueID] = '" & UF_Rec & "'"

    After the Form is Requeried, it will return to the Record in question.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Missinglinq

    Thanks for the reply.

    I created a new cmd btn on the main form to open the sub form in dialog mode and it sort of works using your code.
    Code:
    Private Sub OpenCustomerInfoPopup_Click()
     
       Dim UF_Rec As String
       
       UF_Rec = Me![CustomerID]
       
       DoCmd.OpenForm "New Customer Popup", , , , , acDialog
       Me.Requery
       Me.Recordset.FindFirst "[CustomerID] = '" & UF_Rec & "'"
    End Sub

    Only problem is that when I open the sub form it opens at the first record in the DB, not the record I'm currently on!

    At the moment I open the sub form using this code
    Code:
    Private Sub Edit_AddBtn_Click()
    On Error GoTo Err_Edit_AddBtn_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
       
        stDocName = "New Customer Popup"
        
        stLinkCriteria = "[CustomerID]=" & "'" & Me![CustomerID] & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
       
    Exit_Edit_AddBtn_Click:
        Exit Sub
    
    Err_Edit_AddBtn_Click:
        MsgBox Err.Description
        Resume Exit_Edit_AddBtn_Click
        
    End Sub
    Which opens the sub form at the right record, but doesn't refresh the main form on close as I described earlier.

    I'm thinking I need to pass the current record number on to the sub form from the main form to get it to open at the right record, but don't know how to code it.

    Any suggestions?

  6. #6
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Missinglinq

    Looking at the message I sent you the answer dawned on me. This works exactly as I want.

    Code:
    Private Sub Edit_AddBtn_Click()
    On Error GoTo Err_Edit_AddBtn_Click
    
        Dim stLinkCriteria As String
        Dim UF_Rec As String
       
        UF_Rec = Me![CustomerID]
        stLinkCriteria = "[CustomerID]=" & "'" & Me![CustomerID] & "'"
        DoCmd.OpenForm "New Customer Popup", , , stLinkCriteria, , acDialog
        Me.Requery
        Me.Recordset.FindFirst "[CustomerID] = '" & UF_Rec & "'"
       
    Exit_Edit_AddBtn_Click:
        Exit Sub
    
    Err_Edit_AddBtn_Click:
        MsgBox Err.Description
        Resume Exit_Edit_AddBtn_Click
        
    End Sub
    My thanks to yourself and Weejas for your help.

Posting Permissions

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