473,288 Members | 1,771 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Saving Records with VB

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
3 2535
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Jack | last post by:
Hi, I need to build a asp page where it would serve as a data entry record page as well as display page for records saved. This page should also allow editing of records that has been saved. ...
3
by: CSDunn | last post by:
Hello, I have an Access 2000 Project in which the data comes from a SQL Server 2000 database, and multiple users need to be able to see new records as each user adds records. The users also need...
1
by: Cillies | last post by:
Hello all, I was recently browsing the forum and remembered seeing a message relating to MS Access Bugs/Flaws. The thing is I cannot find that thread anymore, and so was wondering does anyone know...
13
by: Stuart McGraw | last post by:
I haven't been able to figure this out and would appreciate some help... I have two tables, both with autonumber primary keys, and linked in a conventional master-child relationship. I've...
1
by: G Uljee | last post by:
Hi, I've an application with an Access database (max 10000 records). I want to save per 1000 records at once into the Access database, witch option do you prefer (example)? Someone...
11
by: kaosyeti | last post by:
i have a form that records 9 fields into a table. on that form i have a 'done' button to close the form. right now, if the form is fully filled in, but you don't press 'enter' before you click...
4
by: John Kandell | last post by:
Hi, I posted this in the asp.net group, but didn't get a response. Maybe someone here can help me with this... --- Would someone be able to shed some light on what is the cost of saving a...
1
by: google | last post by:
I have a form with several subforms. Users enter the data, then on the parent there is a command button that runs code to generate a .pdf document from a report based on the data they are working...
2
by: darrel | last post by:
Hi there vb masters i have a problem regrading the saving procedure in my program: Here my code: Private Sub cmdSave_Click() Dim rsShowRec As New ADODB.Recordset Dim cnn As New...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.