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

Save new record to table

P: 56
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
Expand|Select|Wrap|Line Numbers
  1., acForm "fdlgNewAccount" 'to keep the form open
  2. .....
  3. ..... 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
Feb 24 '09 #1
Share this Question
Share on Google+
12 Replies

Expert 100+
P: 1,287
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


If Me.Dirty Then Me.Dirty = False
Feb 24 '09 #2

P: 56
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSaveNewPtAccount_Click()
  2. Dim strMsg As String
  4. If IsNull([txtPT_ACCOUNT#]) Then
  5.     MsgBox "Patient Account number is required!", vbInformation, "REQUIRED FIELD!"
  6.     [txtPT_ACCOUNT#].SetFocus
  7.     Exit Sub
  8. ElseIf IsNull(txtUNIT_ADM_DATE) Then
  9.     MsgBox "Unit Admit Date is required!", vbInformation, "REQUIRED FIELD!"
  10.     [txtUNIT_ADM_DATE].SetFocus
  11.     Exit Sub
  12. ElseIf IsNull(cboFACILITY) Then
  13.     MsgBox "Facility is required!", vbInformation, "REQUIRED FIELD!"
  14.     [cboFACILITY].SetFocus
  15.     Exit Sub
  16. Else
  17.     DoCmd.Save acForm, "fdlgAddNewAccount" 'save new account# first in order to carry over account info to review form
  18.     strMsg = "Account: " & [txtPT_ACCOUNT#] & " " & [txtUNIT_ADM_DATE] & " has been added successuflly!"
  19.     If MsgBox(strMsg, vbInformation + vbOKOnly, "Record Added!") = vbOK Then
  20.         DoCmd.OpenForm "frmReview", , , , acFormAdd 'open reivew form as a new review
  21.         'carry over all demographic info and account # info
  22.         [Forms]![frmReview]![txtMEDREC] = [Forms]![fdlgAddNewAccount]![txtMEDREC]
  23.         [Forms]![frmReview]![txtPATLNM] = [Forms]![fdlgAddNewAccount]![txtPATLNM]
  24.         [Forms]![frmReview]![txtPATFNM] = [Forms]![fdlgAddNewAccount]![txtPATFNM]
  25.         [Forms]![frmReview]![txtPT_ACCOUNT#] = [Forms]![fdlgAddNewAccount]![txtPT_ACCOUNT#]
  26.         [Forms]![frmReview]![txtUNIT_ADM_DATE] = [Forms]![fdlgAddNewAccount]![txtUNIT_ADM_DATE]
  27.         [Forms]![frmReview]![txtFACILITY] = [Forms]![fdlgAddNewAccount]![cboFACILITY]
  28.         [Forms]![frmReview]![txtAGE] = [Forms]![fdlgAddNewAccount]![txtAGE]
  29.         [Forms]![frmReview]![txtPATSEX] = [Forms]![fdlgAddNewAccount]![txtPATSEX]
  30.         [Forms]![frmReview]![txtICD9] = [Forms]![fdlgAddNewAccount]![txtICD9]
  31.         [Forms]![frmReview]![txtUsername] = [Forms]![fdlgAddNewAccount].[txtUsername]
  32.         [Forms]![frmReview]![txtROOM#].SetFocus
  33.         [Forms]![frmReview]![cmdCancel].Enabled = True
  34.         [Forms]![frmReview]![cmdCancel].Visible = True
  35.         [Forms]![frmReview]![cmdSave].Enabled = True
  36.         [Forms]![frmReview]![cmdSave].Visible = True
  37.         [Forms]![frmReview]![lblAddMode].Visible = True
  38.         [Forms]![frmReview]![cmdClearVAPScreen].Enabled = True
  39.         DoCmd.Close acForm, "fdlgAddNewAccount"
  40.         DoCmd.Close acForm, "frmPatient", acSaveNo
  41.     End If
  42. End If
  43. 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
Feb 24 '09 #3

Expert 100+
P: 1,287
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.
Feb 24 '09 #4

P: 56
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.


Feb 24 '09 #5

P: 56
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.

Feb 24 '09 #6

Expert 100+
P: 1,287
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.
Feb 24 '09 #7

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

Feb 24 '09 #8

Expert Mod 15k+
P: 31,419
Chip is definitely on the right lines here BlueMoon.

I did a little digging to find how (best) to save records in a form.
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 24 '09 #9

Expert 2.5K+
P: 3,532
"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


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

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)>
Feb 25 '09 #10

Expert Mod 15k+
P: 31,419
It's less straightforward even than that Linq.
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 25 '09 #11

Expert 2.5K+
P: 3,532
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)>
Feb 25 '09 #12

Expert Mod 15k+
P: 31,419
I was looking at the OP's db in delete record.
Feb 25 '09 #13

Post your reply

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