Main form goes to New empty record after pop up updated data

[lmurgas]

FormA = List of organization records bound to table
FormB = Tabbed form with details of organization and all other related entities, such as orders, contacts, invoices, (all as subforms bound to FormB on primary key)
FormC = Pop-up to add/edit/delete a record from one of the associated entities. For example, there is a pop-up to add a new contact record to an Org.

Current Behavior:
1. Open FormA and remains active
2. Double click on row to select a specific org. Opens FormB as a dialog. FormB is bounded to the main Org table
3. Navigate to the Tab Page label Contacts
The tap page contains a subform with a list of all contacts.
4. Double click on a contact to edit/delete
5. FormC opens as a pop-up form (modal)
6. Delete the record on FormC
7. On FormC.OnClose event there is statement to requery
Forms![FormB]![subformContacts].Form.Requery
8. FormC closes successfully
9. FormB regains focus

Current Results:
1. The subformContacts is not updated. I see deleted row with a labeled of "DELETED".
2. If I Add or Cancel changes on FormC (both are subroutines on the click event for each button), FormB regains focus but the form now shows an empty record. I can use the mouse scroll wheel to go back to the original record (one two are present the previously selected org and the new record).

I read most of the postings on requery that's the reason I added the code but still doesn't work for my database. And why do I get a new empty record? I

I appreciate any assistance.

Luis

[MMcCarthy]

Hi Luis

What is the Record Source of FormB and how is it bound to the main form?

Mary

[lmurgas]

Hi Luis

What is the Record Source of FormB and how is it bound to the main form?

Mary

Thanks for the reply Mary.
Let me give an update.
Problem 1 - Requery
Found the problem. It was related to the proper way to relating to the subform name. I was using the actual form name versus the name in the property field. Now the requery occurs.

Problem 2 - Going to the empty record after regaining focus.
Still a problem.
Form B which contains the tab pages is bound to a query of the Organization table. The query simply limits the list of Organizations to those of type Corporate.

Did I answer your question?

[MMcCarthy]

2. If I Add or Cancel changes on FormC (both are subroutines on the click event for each button), FormB regains focus but the form now shows an empty record. I can use the mouse scroll wheel to go back to the original record (one two are present the previously selected org and the new record).

What is the code behind these two buttons and is there any code in the on close event of Form C.

Mary

[lmurgas]

What is the code behind these two buttons and is there any code in the on close event of Form C.

Mary

Here is the code on Form C:

 
Private Sub Form_Load()
On Error GoTo Err_Form_Load
 
  'Extract the args for a new contact
    If Len(Me.OpenArgs & "") > 0 Then
        Dim args, cname As String
        Dim i, x, d As Integer
        'Two arguments are passed: the ClientID & the ClientName
        args = CStr(Me.OpenArgs)
        i = InStr(1, args, "%")
        cname = Mid$(args, i + 1)
 
        Me.NContactDetailsTitle.Visible = True
        Me.NContactDetailsTitle.Caption = cname & " - Contact"
        Me.EContactDetailsTitle.Visible = False
    Else
        Me.NContactDetailsTitle.Visible = False
        Me.EContactDetailsTitle.Visible = True
    End If
 
Exit_Form_Load:
    Exit Sub
 
Err_Form_Load:
    MsgBox Err.Description
    Resume Exit_Form_Load
 
End Sub
 
Private Sub requeryCallingForm()
On Error GoTo Err_requeryCallingForm
 
    'Refresh the subform client contacts on both calling forms potentially opened
    isCorpOpen = SysCmd(acSysCmdGetObjectState, acForm, "CorporateClientProfile")
    isDivOpen = SysCmd(acSysCmdGetObjectState, acForm, "ClientDivisionProfile")
 
    If isCorpOpen Then Forms![CorporateClientProfile]![subformClientContacts].Requery
    If isDivOpen Then Forms![ClientDivisionProfile]![subformClientContacts].Requery
 
Exit_requeryCallingForm:
    Exit Sub
 
Err_requeryCallingForm:
    MsgBox Err.Description
    Resume Exit_requeryCallingForm
End Sub
 
Private Sub Form_Close()
    requeryCallingForm
End Sub
 
Private Sub btnCancelContact_Click()
    btnCloseContactDetails_Click
End Sub
 
Private Sub btnCloseContactDetails_Click()
On Error GoTo Err_btnCloseContactDetails_Click
 
    If Me.Dirty Then
        DoCmd.RunCommand acCmdUndo
    End If
 
    DoCmd.Close
 
Exit_btnCloseContactDetails_Click:
    Exit Sub
 
Err_btnCloseContactDetails_Click:
    MsgBox Err.Description
    Resume Exit_btnCloseContactDetails_Click
 
End Sub
 
Private Sub btnSaveContact_Click()
On Error GoTo Err_btnSaveContact_Click
 
'Check the client id entered by user if null and updating
'check if client id in open args
    If IsNull(Me.ClientID) Or Me.ClientID = 0 Then
        If Len(Me.OpenArgs & "") > 0 Then
            Dim args, cid As String
            Dim i As Integer
 
            args = CStr(Me.OpenArgs)
            i = InStr(1, args, "%")
            cid = Left(args, i - 1)
            Me.ClientID.Value = cid
        End If
    End If
 
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
 
Exit_btnSaveContact_Click:
    Exit Sub
 
Err_btnSaveContact_Click:
    MsgBox Err.Description
    Resume Exit_btnSaveContact_Click
 
End Sub
 
Private Sub btnDeleteContact_Click()
On Error GoTo Err_btnDeleteContact_Click
 
    Dim delmsg As String
    Dim Response
    delmsg = "Are you sure you want to delete the client contact?"
 
    Response = MsgBox(delmsg, vbYesNo, "Contact Deletion Confirmation")
 
    If Response = vbYes Then
        DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdDeleteRecord
 
        DoCmd.Close
 
    Else
        DoCmd.CancelEvent
 
    End If
 
Exit_btnDeleteContact_Click:
    Exit Sub
 
Err_btnDeleteContact_Click:
    MsgBox Err.Description
    Resume Exit_btnDeleteContact_Click
 
End Sub
 
 

The code behind the button that calls it:

 Private Sub btnNewContact_Click()
On Error GoTo Err_btnNewContact_Click
 
    'defining the client owner of the new contact
    Dim args, cid, cname As String
    cid = CStr(Me.ClientID)
    cname = Me.ClientLegalName
    args = cid & "%" & cname
 
    'open the contact details form
    DoCmd.Close acForm, "ContactDetails", acSaveNo
    DoCmd.OpenForm "ContactDetails", acNormal, , , acFormAdd, acDialog, args
    DoCmd.GoToRecord , , acNewRec
 
Exit_btnNewContact_Click:
    Exit Sub
 
Err_btnNewContact_Click:
    MsgBox Err.Description
    Resume Exit_btnNewContact_Click
 
End Sub 

[MMcCarthy]

The code behind the button that calls it:

 Private Sub btnNewContact_Click()
On Error GoTo Err_btnNewContact_Click
 
    'defining the client owner of the new contact
    Dim args, cid, cname As String
    cid = CStr(Me.ClientID)
    cname = Me.ClientLegalName
    args = cid & "%" & cname
 
    'open the contact details form
    DoCmd.Close acForm, "ContactDetails", acSaveNo
    DoCmd.OpenForm "ContactDetails", acNormal, , , acFormAdd, acDialog, args
    DoCmd.GoToRecord , , acNewRec
 
Exit_btnNewContact_Click:
    Exit Sub
 
Err_btnNewContact_Click:
    MsgBox Err.Description
    Resume Exit_btnNewContact_Click
 
End Sub 

Why are you closing the form without saving and then reopening it with args.

Mary

[lmurgas]

Why are you closing the form without saving and then reopening it with args.

Mary

The arguments are for:
* Identifying which client entity the new contact should be associated with
* Displaying the name of the client on the top of the form

Closing and reopening:
* In case a user has the window open, I want to close it. Why without saving? No reason. I guess I can change that.

I notice that all the buttons to create a new item (Contact, Agreement, Invoicing) behaves the same if I open the various dialog forms (just like C) and then close it (I copied the same approach to create those new items). Form B comes up always as a new record. I am trying MS Access 2007 and the problem is very obvious but I don't know what to do about it.

[lmurgas]

Why are you closing the form without saving and then reopening it with args.

Mary

Mary - Think I found the problem. The line

 DoCmd.GoToRecord , , acNewRec 

after opening the form, is a command for the current active window. Since the control is transfered to the Contact Details in the line

DoCmd.OpenForm "ContactDetails", acNormal, , , acFormAdd, acDialog, args 

and the user closed the "Contact Details" form (Form C), the active form became Form B (the main client profile form). Effectively saying, go to a new record after the user enters the contact information on the "Contact Details" modal dialog.

Am I correct on my understaning? The form now work correctly.
Luis

[MMcCarthy]

Sounds about right Luis.

You always have to be careful about the order in which commands are implemented. Well done on figuring it out.

Mary