By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

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

P: 5
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
Mar 15 '07 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Luis

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

Mary
Mar 17 '07 #2

P: 5
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?
Mar 20 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 22 '07 #4

P: 5
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Load()
  3. On Error GoTo Err_Form_Load
  4.  
  5.   'Extract the args for a new contact
  6.     If Len(Me.OpenArgs & "") > 0 Then
  7.         Dim args, cname As String
  8.         Dim i, x, d As Integer
  9.         'Two arguments are passed: the ClientID & the ClientName
  10.         args = CStr(Me.OpenArgs)
  11.         i = InStr(1, args, "%")
  12.         cname = Mid$(args, i + 1)
  13.  
  14.         Me.NContactDetailsTitle.Visible = True
  15.         Me.NContactDetailsTitle.Caption = cname & " - Contact"
  16.         Me.EContactDetailsTitle.Visible = False
  17.     Else
  18.         Me.NContactDetailsTitle.Visible = False
  19.         Me.EContactDetailsTitle.Visible = True
  20.     End If
  21.  
  22. Exit_Form_Load:
  23.     Exit Sub
  24.  
  25. Err_Form_Load:
  26.     MsgBox Err.Description
  27.     Resume Exit_Form_Load
  28.  
  29. End Sub
  30.  
  31. Private Sub requeryCallingForm()
  32. On Error GoTo Err_requeryCallingForm
  33.  
  34.     'Refresh the subform client contacts on both calling forms potentially opened
  35.     isCorpOpen = SysCmd(acSysCmdGetObjectState, acForm, "CorporateClientProfile")
  36.     isDivOpen = SysCmd(acSysCmdGetObjectState, acForm, "ClientDivisionProfile")
  37.  
  38.     If isCorpOpen Then Forms![CorporateClientProfile]![subformClientContacts].Requery
  39.     If isDivOpen Then Forms![ClientDivisionProfile]![subformClientContacts].Requery
  40.  
  41. Exit_requeryCallingForm:
  42.     Exit Sub
  43.  
  44. Err_requeryCallingForm:
  45.     MsgBox Err.Description
  46.     Resume Exit_requeryCallingForm
  47. End Sub
  48.  
  49. Private Sub Form_Close()
  50.     requeryCallingForm
  51. End Sub
  52.  
  53. Private Sub btnCancelContact_Click()
  54.     btnCloseContactDetails_Click
  55. End Sub
  56.  
  57. Private Sub btnCloseContactDetails_Click()
  58. On Error GoTo Err_btnCloseContactDetails_Click
  59.  
  60.     If Me.Dirty Then
  61.         DoCmd.RunCommand acCmdUndo
  62.     End If
  63.  
  64.     DoCmd.Close
  65.  
  66. Exit_btnCloseContactDetails_Click:
  67.     Exit Sub
  68.  
  69. Err_btnCloseContactDetails_Click:
  70.     MsgBox Err.Description
  71.     Resume Exit_btnCloseContactDetails_Click
  72.  
  73. End Sub
  74.  
  75. Private Sub btnSaveContact_Click()
  76. On Error GoTo Err_btnSaveContact_Click
  77.  
  78. 'Check the client id entered by user if null and updating
  79. 'check if client id in open args
  80.     If IsNull(Me.ClientID) Or Me.ClientID = 0 Then
  81.         If Len(Me.OpenArgs & "") > 0 Then
  82.             Dim args, cid As String
  83.             Dim i As Integer
  84.  
  85.             args = CStr(Me.OpenArgs)
  86.             i = InStr(1, args, "%")
  87.             cid = Left(args, i - 1)
  88.             Me.ClientID.Value = cid
  89.         End If
  90.     End If
  91.  
  92.     DoCmd.RunCommand acCmdSaveRecord
  93.     DoCmd.Close
  94.  
  95. Exit_btnSaveContact_Click:
  96.     Exit Sub
  97.  
  98. Err_btnSaveContact_Click:
  99.     MsgBox Err.Description
  100.     Resume Exit_btnSaveContact_Click
  101.  
  102. End Sub
  103.  
  104. Private Sub btnDeleteContact_Click()
  105. On Error GoTo Err_btnDeleteContact_Click
  106.  
  107.     Dim delmsg As String
  108.     Dim Response
  109.     delmsg = "Are you sure you want to delete the client contact?"
  110.  
  111.     Response = MsgBox(delmsg, vbYesNo, "Contact Deletion Confirmation")
  112.  
  113.     If Response = vbYes Then
  114.         DoCmd.RunCommand acCmdSelectRecord
  115.         DoCmd.RunCommand acCmdDeleteRecord
  116.  
  117.         DoCmd.Close
  118.  
  119.     Else
  120.         DoCmd.CancelEvent
  121.  
  122.     End If
  123.  
  124. Exit_btnDeleteContact_Click:
  125.     Exit Sub
  126.  
  127. Err_btnDeleteContact_Click:
  128.     MsgBox Err.Description
  129.     Resume Exit_btnDeleteContact_Click
  130.  
  131. End Sub
  132.  
  133.  
The code behind the button that calls it:
Expand|Select|Wrap|Line Numbers
  1.  Private Sub btnNewContact_Click()
  2. On Error GoTo Err_btnNewContact_Click
  3.  
  4.     'defining the client owner of the new contact
  5.     Dim args, cid, cname As String
  6.     cid = CStr(Me.ClientID)
  7.     cname = Me.ClientLegalName
  8.     args = cid & "%" & cname
  9.  
  10.     'open the contact details form
  11.     DoCmd.Close acForm, "ContactDetails", acSaveNo
  12.     DoCmd.OpenForm "ContactDetails", acNormal, , , acFormAdd, acDialog, args
  13.     DoCmd.GoToRecord , , acNewRec
  14.  
  15. Exit_btnNewContact_Click:
  16.     Exit Sub
  17.  
  18. Err_btnNewContact_Click:
  19.     MsgBox Err.Description
  20.     Resume Exit_btnNewContact_Click
  21.  
  22. End Sub 
Mar 27 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
The code behind the button that calls it:
Expand|Select|Wrap|Line Numbers
  1.  Private Sub btnNewContact_Click()
  2. On Error GoTo Err_btnNewContact_Click
  3.  
  4.     'defining the client owner of the new contact
  5.     Dim args, cid, cname As String
  6.     cid = CStr(Me.ClientID)
  7.     cname = Me.ClientLegalName
  8.     args = cid & "%" & cname
  9.  
  10.     'open the contact details form
  11.     DoCmd.Close acForm, "ContactDetails", acSaveNo
  12.     DoCmd.OpenForm "ContactDetails", acNormal, , , acFormAdd, acDialog, args
  13.     DoCmd.GoToRecord , , acNewRec
  14.  
  15. Exit_btnNewContact_Click:
  16.     Exit Sub
  17.  
  18. Err_btnNewContact_Click:
  19.     MsgBox Err.Description
  20.     Resume Exit_btnNewContact_Click
  21.  
  22. End Sub 
Why are you closing the form without saving and then reopening it with args.

Mary
Mar 29 '07 #6

P: 5
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.
Mar 29 '07 #7

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

Mary
Mary - Think I found the problem. The line
Expand|Select|Wrap|Line Numbers
  1.  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
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 29 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 29 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.