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

Saving Records with VB

P: n/a
I'm having a problem with an entry form on one of my applications. It
would appear that the save action is sometimes not working, and is
generating no error when it fails. I'm hoping one of you may have some
insight into what is causing the problem.

I'll describe only the general outline since the actual code is several
pages long.

1. Entry form opened in Data Entry mode
2. Global variable SaveRec set to False on form load.
3. User displayed 2 bound controls (ID [AutoNumber], and Title [Text])
and an unbound control (Description [memo])
4. User fills in controls and AutoNumber field is generated
5. User clicks Save button
6. Validation run to insure all fields filled in and that AutoNumber
field contains a value
7. Value in AutoNumber field saved to GlobalVariable IDHold
8. Global variable SaveRec set to True
9. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
10. Open recordset on table where the above record was created using
criteria stored in IDHold
11. If recordcount = 0, display error and exit Save Button module Else
continue
12. *Many other steps beyond this, but they appear to work just fine.

*The Form's Before Update event occurs immediatly after the save record
command.
Form Before Update
If SaveRec = False
Me.undo
End if
End sub

My problem is on step 11. 95% of the time the record is saved
correctly. However, sometimes the record isn't found because it wasn't
saved. No error is being generated so I have no idea why the save did
not go through. Some users have this problem occur more than others and
the ones that have it happen most often are users located offsite
(WAN). If this problem does occur, the user has to click the Finish
button once or twice more before the record is finally out there and
available for the recordset to find.

My question is this: does the save record command wait to complete
before advancing to the next line of code, or will it only initiate the
save action before moving on? By what I can tell, it would appear to be
the latter. If it only initiates the save, is there anyway to delay the
code until the save action is complete?

Thanks in advance for any advice you might have!

P.S. I could just do the whole thing with a recordset and use the
..update method, but the users want to be given the record number while
they are entering it rather than after.

Dec 15 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
To explicitly save, replace the DoMenuItem (your #9) with:
Me.Dirty = False

A better solution is to move all the record validation code (your step 6)
into Form_BeforeUpdate. Just doing that avoids all the other steps. There is
no longer any need to mess with form level variables (SaveRec) or try to
force the user into a particular sequence.

I have not worked through all your steps (since they are probably
unnecessary), but I didn't see anywhere where SaveRec was set back to False
after a save, so if it were possible for the user to move record after an
edit, it would not be re-initialized.

You may also need to be aware that Access can silently lose your entry if
you use the Close action in a macro, or the Close method in code (as
distinct from the close button on the right end of the form's Title bar.)
More info:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<An***********@bcbsmn.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...
I'm having a problem with an entry form on one of my applications. It
would appear that the save action is sometimes not working, and is
generating no error when it fails. I'm hoping one of you may have some
insight into what is causing the problem.

I'll describe only the general outline since the actual code is several
pages long.

1. Entry form opened in Data Entry mode
2. Global variable SaveRec set to False on form load.
3. User displayed 2 bound controls (ID [AutoNumber], and Title [Text])
and an unbound control (Description [memo])
4. User fills in controls and AutoNumber field is generated
5. User clicks Save button
6. Validation run to insure all fields filled in and that AutoNumber
field contains a value
7. Value in AutoNumber field saved to GlobalVariable IDHold
8. Global variable SaveRec set to True
9. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
10. Open recordset on table where the above record was created using
criteria stored in IDHold
11. If recordcount = 0, display error and exit Save Button module Else
continue
12. *Many other steps beyond this, but they appear to work just fine.

*The Form's Before Update event occurs immediatly after the save record
command.
Form Before Update
If SaveRec = False
Me.undo
End if
End sub

My problem is on step 11. 95% of the time the record is saved
correctly. However, sometimes the record isn't found because it wasn't
saved. No error is being generated so I have no idea why the save did
not go through. Some users have this problem occur more than others and
the ones that have it happen most often are users located offsite
(WAN). If this problem does occur, the user has to click the Finish
button once or twice more before the record is finally out there and
available for the recordset to find.

My question is this: does the save record command wait to complete
before advancing to the next line of code, or will it only initiate the
save action before moving on? By what I can tell, it would appear to be
the latter. If it only initiates the save, is there anyway to delay the
code until the save action is complete?

Thanks in advance for any advice you might have!

P.S. I could just do the whole thing with a recordset and use the
.update method, but the users want to be given the record number while
they are entering it rather than after.

Dec 16 '06 #2

P: n/a
Allen,

Thanks for the feedback, but I had some questions regarding your
comments.

I don't understand how the dirty property explicitly saves the record.
As far as I understand it only determines if the form has been
modified. (And I'm not sure if it would even do that for unbound
controls?).

The tip about the save action silently failing in some cases is very
useful to know. I think I have some places that were causing me
problems due to that and I could never figure out what it was.

I think I fugered out what was going on in my above scenario. The
problem was caused by the mouse wheel being scrolled by the user at
some point, which then caused the record to move to the next without
saving. Step 6 in the validation process takes several seconds to
complete, and the users that were having trouble apparently liked to
fiddle with the mouse while they waited.

Dec 18 '06 #3

P: n/a
Only bound forms have a Dirty property.
The property indicates if there are unsaved edits in progress.
It was read-only in Access 1, 1.1, and 2.

From Access 95 onwards, you could set it to False to force Access to save
the record. This is my preferred approach to saving a record, since you can
specify which form's record is to be saved, even if it is not the active
form (unlike RunCommand acCmdSaveRecord, DoMenuItem, etc.)

If a control is unbound, none of those approaches will save the field value
anywhere.

Access 2007 solves this problem with the mouse wheel. Scrolling does not
change records in Form view. Scollling still works in datasheet and
continuous form view. There is a MouseWheel event you can use if you want
the scrolling like previous versions.

If you want to suppress the mouse wheel scrolling in previous versions, see:
http://www.lebans.com/mousewheelonoff.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<An***********@bcbsmn.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...
Allen,

Thanks for the feedback, but I had some questions regarding your
comments.

I don't understand how the dirty property explicitly saves the record.
As far as I understand it only determines if the form has been
modified. (And I'm not sure if it would even do that for unbound
controls?).

The tip about the save action silently failing in some cases is very
useful to know. I think I have some places that were causing me
problems due to that and I could never figure out what it was.

I think I fugered out what was going on in my above scenario. The
problem was caused by the mouse wheel being scrolled by the user at
some point, which then caused the record to move to the next without
saving. Step 6 in the validation process takes several seconds to
complete, and the users that were having trouble apparently liked to
fiddle with the mouse while they waited.

Dec 19 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.