Save new record to table

[bluemoon9]

hi,
I've written this code to save the record without closing the form
so instead of write the code as
docmd.close, acform "fdlgNewAccount",,,acSaveYes

docmd.save, acForm "fdlgNewAccount" 'to keep the form open
.....
..... code to do other things.
 

Does it cause problem with multiple users using the database at the same time?

is there a better way to save a new record into the table but still have the form open?

can someone please help

[ChipR]

The DoCmd.Save is saving the FORM, not the record.

I didn't test this, but an Access MVP said elsewhere:

If you really need to do this, you can write

RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

[bluemoon9]

Hi,
Actually I was able to save record into the table just fine. It's just that when my users use the database, sometime, after they have entered all data into the form, click on OK button, then nothing happen. But sometimes they were able to add without any problem.

Here is my complete code.

Private Sub cmdSaveNewPtAccount_Click()
Dim strMsg As String
 
If IsNull([txtPT_ACCOUNT#]) Then
    MsgBox "Patient Account number is required!", vbInformation, "REQUIRED FIELD!"
    [txtPT_ACCOUNT#].SetFocus
    Exit Sub
ElseIf IsNull(txtUNIT_ADM_DATE) Then
    MsgBox "Unit Admit Date is required!", vbInformation, "REQUIRED FIELD!"
    [txtUNIT_ADM_DATE].SetFocus
    Exit Sub
ElseIf IsNull(cboFACILITY) Then
    MsgBox "Facility is required!", vbInformation, "REQUIRED FIELD!"
    [cboFACILITY].SetFocus
    Exit Sub
Else
    DoCmd.Save acForm, "fdlgAddNewAccount" 'save new account# first in order to carry over account info to review form
    strMsg = "Account: " & [txtPT_ACCOUNT#] & " " & [txtUNIT_ADM_DATE] & " has been added successuflly!"
    If MsgBox(strMsg, vbInformation + vbOKOnly, "Record Added!") = vbOK Then
        DoCmd.OpenForm "frmReview", , , , acFormAdd 'open reivew form as a new review
        'carry over all demographic info and account # info
        [Forms]![frmReview]![txtMEDREC] = [Forms]![fdlgAddNewAccount]![txtMEDREC]
        [Forms]![frmReview]![txtPATLNM] = [Forms]![fdlgAddNewAccount]![txtPATLNM]
        [Forms]![frmReview]![txtPATFNM] = [Forms]![fdlgAddNewAccount]![txtPATFNM]
        [Forms]![frmReview]![txtPT_ACCOUNT#] = [Forms]![fdlgAddNewAccount]![txtPT_ACCOUNT#]
        [Forms]![frmReview]![txtUNIT_ADM_DATE] = [Forms]![fdlgAddNewAccount]![txtUNIT_ADM_DATE]
        [Forms]![frmReview]![txtFACILITY] = [Forms]![fdlgAddNewAccount]![cboFACILITY]
        [Forms]![frmReview]![txtAGE] = [Forms]![fdlgAddNewAccount]![txtAGE]
        [Forms]![frmReview]![txtPATSEX] = [Forms]![fdlgAddNewAccount]![txtPATSEX]
        [Forms]![frmReview]![txtICD9] = [Forms]![fdlgAddNewAccount]![txtICD9]
        [Forms]![frmReview]![txtUsername] = [Forms]![fdlgAddNewAccount].[txtUsername]
        [Forms]![frmReview]![txtROOM#].SetFocus
        [Forms]![frmReview]![cmdCancel].Enabled = True
        [Forms]![frmReview]![cmdCancel].Visible = True
        [Forms]![frmReview]![cmdSave].Enabled = True
        [Forms]![frmReview]![cmdSave].Visible = True
        [Forms]![frmReview]![lblAddMode].Visible = True
        [Forms]![frmReview]![cmdClearVAPScreen].Enabled = True
        DoCmd.Close acForm, "fdlgAddNewAccount"
        DoCmd.Close acForm, "frmPatient", acSaveNo
    End If
End If
End Sub
 

I tried to leave the form "Patient Account" open when I save the record because I want to carry some demographic fields over to the new form (frmReview)

Any idea?

Thanks for your help
bluemoon

[ChipR]

I'm not quite sure what you mean. What goes wrong when you take out the "DoCmd.Close acForm, "frmPatient", acSaveNo"?
Also "If MsgBox(strMsg, vbInformation + vbOKOnly, "Record Added!") = vbOK Then" doesn't really make sense, since there are no other options. You can get rid of the If.

[bluemoon9]

Hi,
I need to have "DoCmd.Close acForm, "frmPatient", acSaveNo ", because there is another form behind the fdldNewPatientAccount form.
What happen here is user will enter a new pt, then prompt user to enter new account, then prompt uer to enter new review. One to many relationship.

The reason I have the If vbOK is because
DoCmd.Save acForm, "fdlgAddNewAccount" 'save new account#'
then "save recorded " cofirm message, with vbOK button,
user will click on OK, then
...execute my code which is transfer all the fields (demograhpic data, account#, etc) from the frmPatient and fdlgPtAccount to the frmReview

My question here is I am not sure if I save the NEW PT ACCOUNT correctly, since it's where user has problem sometimes. When confirm message poped up, they click on OK, but nothing happen, instead of moving to the frmReview, it did nothing.

Thanks!

bluemoon

[bluemoon9]

Hi,
In addition, I did try to get rid of the If since there is only option, but it did not solve the problem, so I just left it there.


thanks!

[ChipR]

@bluemoon9
Like I said in Post #2, the DoCmd.Save does not save the new record. Fix that and see if you still have problems. If so, hopefully I can help tomorrow if some one else doesn't first.

[bluemoon9]

I'll try and will let you know if it works.

bluemoon

[NeoPa]

Chip is definitely on the right lines here BlueMoon.

I did a little digging to find how (best) to save records in a form.

If Me.Dirty Then Me.Dirty = False

I fuller reference can be found on Allen Browne's site at Losing data when you close a form. It's not as straightforward as I expected, and certainly not as it should be.

[missinglinq]

"It's not as straightforward as I expected, and certainly not as it should be."

And it just keeps on keeping on, NeoPa!

You definitely have to explicitly force a record save with either

RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

before closing the form with DoCmd.Save or records that fail validation will simply be dumped without any warning messages!

And DoCmd.Save is, of course, the code the Command Button Wizard has generated for a Close button up thru version 2003!

After knowing about this problem for years they decided to fix it for version 2007. The Wizard now generates the code

If Me.Dirty Then Me.Dirty = False
DoCmd.Save

The problem with this is that if you now use it on a form in 2007 that has no records, i.e. an unbound form, such as a menu/switchboard form, the line

If Me.Dirty Then Me.Dirty = False


generates an error, because an unbound form doesn't have a Dirty Property! If they had used RunCommand acCmdSaveRecord there wouldn't have been a problem, no error would have resulted!

As I said, the problem keeps on keeping on!

Linq ;0)>

[NeoPa]

It's less straightforward even than that Linq.

RunCommand acCmdSaveRecord

Would have been my recommendation had it not failed spectacularly when I tried it in normal use. I understand that the form itself must be selected (not the case after a button is clicked) if it's to work.

It seems that setting Dirty = False (horribly crappy and unobvious - actually worse as it's obviously doing something else - except it's not) is the only reliable way to do it in most circumstances.

[missinglinq]

I've never had a problem with RunCommand acCmdSaveRecord either in 2000 or 2003, although I confess, I generally use If Me.Dirty Then Me.Dirty = False simply because it's the first way I learned to force a save.

I have no problem placing RunCommand acCmdSaveRecord behind a button and having it work appropriately, although I would not use it in this manner, myself, never using "Save" buttons, as so many people feel compelled to do!

What exact scenario were you using when you ran into problems?

Linq ;0)>

[NeoPa]

I was looking at the OP's db in delete record.