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

How to force a "new record write"

sueb
100+
P: 379
I have a form with a subform, and this subform allows the addition of a new child record using only a few essential fields. Typically, after entering these few fields, the user goes on to fill in the rest of the fields, and that action happens by clicking on a button that opens a separate form that displays all the fields for the new child record.

The problem is that it doesn't. It displays the separate form, but on a blank record rather than the newly added record. If the user moves the cursor in the abbreviated form into a field for a different child record, and THEN clicks on the button, the separate form displays the newly added record.

How can I force the child record to be written (probably, in the code for the "separate form" button)?
Dec 28 '10 #1

✓ answered by ADezii

Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2. Dim stMainForm As String
  3. Dim stLinkCriteria As String
  4.  
  5. stDocName = "IURs: subform"
  6.  
  7. '****************** The following Line will NOT Save theRecord ******************
  8. 'Forms![Patient_IUR_Overview]![IURs Abbreviated: subform].Form.Dirty = False
  9. '********************************************************************************
  10.  
  11. '******************* The following Lines WILL Save the Record *******************
  12. 'If Me.Dirty Then Me.Dirty = False
  13. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  14. '********************************************************************************
  15.  
  16. stLinkCriteria = "[IURs.IUR Index]=" & Me![IUR Index]
  17.  
  18. DoCmd.OpenForm stDocName, , , stLinkCriteria

Share this Question
Share on Google+
20 Replies


ADezii
Expert 5K+
P: 8,616
You can force the Save of a Record in a Sub-Form by setting the Dirty Property of the Sub-Form Object itself to False. This, of course, will not stop Error Messages if Validation Rules, Input Masks, Required Property of any Control on the Sub-Form is violated prior to the Save. Substitute your Main Form and Sub-Form Names below in the following Code:
Expand|Select|Wrap|Line Numbers
  1. Forms![<Main Form>]![<Sub-Form>].Form.Dirty = False
Dec 28 '10 #2

sueb
100+
P: 379
I don't know what it is about this application's syntax that stumps me so, but I simply could not get this line to do anything other than give an error. Here is the whole module that's attached to the "go to the separate form" button:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Open_IUR_Subform_Click()
  2. On Error GoTo Err_Open_IUR_Subform_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stMainForm As String
  6.     Dim stLinkCriteria As String
  7.  
  8.     stMainForm = "Patient_IUR_Overview"
  9.     stSubForm = "IURs Abbreviated: subform"
  10.     stDocName = "IURs: subform"
  11.  
  12.     'Forms![<stMainForm>]![<stSubForm>].Form.Dirty = False
  13.     'Forms![stMainForm]![stSubForm].Form.Dirty = False
  14.     'Forms![<stSubForm>]![<stDocName>].Form.Dirty = False
  15.     'Forms![stSubForm]![stDocName].Form.Dirty = False
  16.  
  17.     stLinkCriteria = "[IURs.IUR Index]=" & Me![IUR Index]
  18.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  19.  
  20. Exit_Open_IUR_Subform_Click:
  21.     Exit Sub
  22.  
  23. Err_Open_IUR_Subform_Click:
  24.     MsgBox Err.Description
  25.     Resume Exit_Open_IUR_Subform_Click
  26.  
  27. End Sub
  28.  
The "OpenForm" thing works great; I just included it in the interest of completeness.
Jan 6 '11 #3

ADezii
Expert 5K+
P: 8,616
Use the Syntax that I showed you in Post# 2, namely:
Expand|Select|Wrap|Line Numbers
  1. Forms![Patient_IUR_Overview]![IURs Abbreviated: subform].Form.Dirty = False
Jan 6 '11 #4

sueb
100+
P: 379
Whether I use constants in that line, or whether I use the actual form names, I get the following error:

"Pre-Admitting IURs can't find the form 'Patient_IUR_Overview' referred to in a macro expression or Visual Basic code."
Jan 6 '11 #5

ADezii
Expert 5K+
P: 8,616
Where is Pre-Admitting IURs originating from?
Jan 6 '11 #6

sueb
100+
P: 379
That's the name of the database itself. I forget where that's set.
Jan 6 '11 #7

sueb
100+
P: 379
Anyone else have any ideas to help me with this?
Jan 11 '11 #8

ADezii
Expert 5K+
P: 8,616
The only thing that I can tell you, sueb, is that the Syntax below does work. I happen to have a copy of the DB that you sent me, and tested it again. The only thing that I can think of is that [Patient_IUR_Overview] is not Open when the Code is being executed. If the Back End has not changed, you can Upload the Front End and I'll take a look at it again.
Expand|Select|Wrap|Line Numbers
  1. Forms![Patient_IUR_Overview]![IURs Abbreviated: subform].Form.Dirty = False
Jan 11 '11 #9

sueb
100+
P: 379
Oh, thanks, Adezii! Here's the front end.
Attached Files
File Type: zip IURs.zip (472.3 KB, 71 views)
Jan 11 '11 #10

ADezii
Expert 5K+
P: 8,616
Have a Look/See:
Attached Files
File Type: zip IURs_2.zip (214.8 KB, 58 views)
Jan 11 '11 #11

sueb
100+
P: 379
Gosh, I don't know, Adezii. I still get that same error. Even though the line you sent me was exactly the same as one I had tried before, I copied and pasted it out of your version, just to make sure, but to no avail.

This is really weird. Is there some "Access-level" setting that could be involved? I can't imagine what that would be, but this just isn't working in my copy.
Jan 11 '11 #12

ADezii
Expert 5K+
P: 8,616
This is really weird. If you can, send me now the Copy that isn't working even though the Code chaqnges work on my end. Nothing has changed in the Back End, correct?
Jan 11 '11 #13

sueb
100+
P: 379
Okay, here's both the front end and a safe back end. I surely appreciate your help with this!
Attached Files
File Type: zip IURs.zip (462.8 KB, 54 views)
File Type: zip IURSTATUS_BE.zip (90.2 KB, 58 views)
Jan 11 '11 #14

ADezii
Expert 5K+
P: 8,616
Give this a try, sueb.
Attached Files
File Type: zip IURs_3.zip (211.9 KB, 52 views)
Jan 12 '11 #15

sueb
100+
P: 379
That seems to have done the trick! I see that the code is not at all the same:

didn't work:

Expand|Select|Wrap|Line Numbers
  1. Forms![Patient_IUR_Overview]![IURs Abbreviated: subform].Form.Dirty = False
did work:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
and I'd love to understand what's going on. Is there something about my database that was different than you first expected? (And that maybe I should change?) Or what?
Jan 12 '11 #16

ADezii
Expert 5K+
P: 8,616
I'll be perfectly honest and tell you that I am not exactly sure what is going on since it was working perfectly with an older Back End. In any event, I took an alternative approach. Since the Code is being executed within the context of the Sub-Form, an explicitly Save should do the trick in Saving the Current Record there, and Opening the other Form. This will simply have to go down as an Unsolved Mystery until I can find the time to investigate further! (LOL).
Jan 12 '11 #17

sueb
100+
P: 379
Well, thanks, again, ADezii. It's working smoothly. If you ever do get a bead on this, I'll be very interested to hear it.
Jan 12 '11 #18

ADezii
Expert 5K+
P: 8,616
You bet, sueb.
Jan 12 '11 #19

sueb
100+
P: 379
Adezii, would you consider editing your post where you sent me the version that worked to include the line that works? You know, just for the sake of those who come after...
Jan 12 '11 #20

ADezii
Expert 5K+
P: 8,616
Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2. Dim stMainForm As String
  3. Dim stLinkCriteria As String
  4.  
  5. stDocName = "IURs: subform"
  6.  
  7. '****************** The following Line will NOT Save theRecord ******************
  8. 'Forms![Patient_IUR_Overview]![IURs Abbreviated: subform].Form.Dirty = False
  9. '********************************************************************************
  10.  
  11. '******************* The following Lines WILL Save the Record *******************
  12. 'If Me.Dirty Then Me.Dirty = False
  13. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  14. '********************************************************************************
  15.  
  16. stLinkCriteria = "[IURs.IUR Index]=" & Me![IUR Index]
  17.  
  18. DoCmd.OpenForm stDocName, , , stLinkCriteria
Jan 12 '11 #21

Post your reply

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