How to force a "new record write"

[sueb]

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)?

[ADezii]

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:

Forms![<Main Form>]![<Sub-Form>].Form.Dirty = False

[sueb]

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:

Private Sub Open_IUR_Subform_Click()
On Error GoTo Err_Open_IUR_Subform_Click
 
    Dim stDocName As String
    Dim stMainForm As String
    Dim stLinkCriteria As String
 
    stMainForm = "Patient_IUR_Overview"
    stSubForm = "IURs Abbreviated: subform"
    stDocName = "IURs: subform"
 
    'Forms![<stMainForm>]![<stSubForm>].Form.Dirty = False
    'Forms![stMainForm]![stSubForm].Form.Dirty = False
    'Forms![<stSubForm>]![<stDocName>].Form.Dirty = False
    'Forms![stSubForm]![stDocName].Form.Dirty = False
 
    stLinkCriteria = "[IURs.IUR Index]=" & Me![IUR Index]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Open_IUR_Subform_Click:
    Exit Sub
 
Err_Open_IUR_Subform_Click:
    MsgBox Err.Description
    Resume Exit_Open_IUR_Subform_Click
 
End Sub
 

The "OpenForm" thing works great; I just included it in the interest of completeness.

[ADezii]

Use the Syntax that I showed you in Post# 2, namely:

Forms![Patient_IUR_Overview]![IURs Abbreviated: subform].Form.Dirty = False

[sueb]

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."

[ADezii]

Where is Pre-Admitting IURs originating from?

[sueb]

That's the name of the database itself. I forget where that's set.

[sueb]

Anyone else have any ideas to help me with this?

[ADezii]

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.

Forms![Patient_IUR_Overview]![IURs Abbreviated: subform].Form.Dirty = False

[sueb]

Oh, thanks, Adezii! Here's the front end.

[ADezii]

Have a Look/See:

[sueb]

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.

[ADezii]

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?

[sueb]

Okay, here's both the front end and a safe back end. I surely appreciate your help with this!

[ADezii]

Give this a try, sueb.

[sueb]

That seems to have done the trick! I see that the code is not at all the same:

didn't work:

Forms![Patient_IUR_Overview]![IURs Abbreviated: subform].Form.Dirty = False

did work:

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?

[ADezii]

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).

[sueb]

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.

[ADezii]

You bet, sueb.

[sueb]

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...

[ADezii]

Dim stDocName As String
Dim stMainForm As String
Dim stLinkCriteria As String
 
stDocName = "IURs: subform"
 
'****************** The following Line will NOT Save theRecord ******************
'Forms![Patient_IUR_Overview]![IURs Abbreviated: subform].Form.Dirty = False
'********************************************************************************
 
'******************* The following Lines WILL Save the Record *******************
'If Me.Dirty Then Me.Dirty = False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'********************************************************************************
 
stLinkCriteria = "[IURs.IUR Index]=" & Me![IUR Index]
 
DoCmd.OpenForm stDocName, , , stLinkCriteria