473,320 Members | 2,052 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,320 software developers and data experts.

Field and record updates

I’m having problems understand how and when Access 2003 saves data.

I have a simple form where a user can view or modify data in a table. I have disabled the X on the menu bar to stop them exiting and provide two VBA buttons, one for save/exit and another for abandon/exit.

On exit, there is a routine which needs to check if a field was modified and update a change log:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CloseFS_Click()
  2.     If Me.EA <> Me.EA.OldValue Then UpdateHistory Me.EA
  3.         End If
  4. End Sub
  5.  
While testing we found that after changing the EA field, UpdateHistory is not always called as Me.EA.OldValue does not always contain the old value on exit. If the user activates/ modifies another field, Me.EA and Me.EA.OldValue can have the same value on exit.

I have also tried DLookup but it appears that the field change is updated in the database on modifying another field. Can this behaviour be changed/controlled?

Is there a more reliable way to check if a field has been changed since the form was opened as you close it? (without simply creating a change variable)

Also, can I be sure no field will be modified on closing with the following abandon code?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Abandon_Click()
  2.     Me.Undo
  3.     DoCmd.Close , , acSaveNo
  4. End Sub
  5.  
Many thanks!
Mar 9 '10 #1

✓ answered by TheSmileyCoder

You could use the Dirty property of the form. The only "problem" is that if you say add a HELLO to a textbox, and then delete the HELLO, the form will still consider itself dirty, even when it is actually not (anymore)

Expand|Select|Wrap|Line Numbers
  1. If Me.Dirty then UpdateHistory Me.EA
I would also put code like that into the forms BeforeUpdate. Imagine someone presses Ctrl-S instead of using your button. Putting the code in the BeforeUpdate would be sure to catch cases such as that.


The Me.Undo should do fine in preventing the record update.
The Docmd.Save ,,acSaveNo just means your not saving DESIGN changes to the FORM. This has nothing to do with RECORD changes.

1 1659
TheSmileyCoder
2,322 Expert Mod 2GB
You could use the Dirty property of the form. The only "problem" is that if you say add a HELLO to a textbox, and then delete the HELLO, the form will still consider itself dirty, even when it is actually not (anymore)

Expand|Select|Wrap|Line Numbers
  1. If Me.Dirty then UpdateHistory Me.EA
I would also put code like that into the forms BeforeUpdate. Imagine someone presses Ctrl-S instead of using your button. Putting the code in the BeforeUpdate would be sure to catch cases such as that.


The Me.Undo should do fine in preventing the record update.
The Docmd.Save ,,acSaveNo just means your not saving DESIGN changes to the FORM. This has nothing to do with RECORD changes.
Mar 9 '10 #2

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

Similar topics

3
by: Alan Pocklington | last post by:
Hi, I've created a Java Applet that allows the user to select a record. As the user makes the selection, the applet uses LiveConnect (JSObject) to write the record id to a html field (the name...
1
by: BLUE WATER | last post by:
Does anyone know how I can see a value in my form field text box ? I tried to add a text box, but the record source doesn't list the variable I want to see for each record. The variable is the...
4
by: Mal | last post by:
I have an ACC 2000 database that has a strange behaviour I have a small table, with just a few fields... My report has very simple grouping and sorting, no code bar a NODATA event. I have a...
2
by: Rosy | last post by:
I have a sub-form(company name & address) that is connected to my contact listing. When I change the contact, it doesn't update the subform until I close the main form or refresh. Is there a way...
0
by: Jawahar | last post by:
All, I have a form view that allows edit (updates) and Inserts (Add a new detail row) . THe formview is populated via a SqlDatasource that is fitered by value that is passed to the SqlDatasource...
9
by: Ecohouse | last post by:
I have a main form with two subforms. The first subform has the child link to the main form identity key. subform1 - Master Field: SK Child Field: TrainingMasterSK The second subform has a...
5
by: rdemyan via AccessMonster.com | last post by:
I have a need to add another field to all of my tables (over 150). Not data, but an actual field. Can I code this somehow. So the code presumabley would loop through all the tables, open each...
0
by: Melanie | last post by:
Hi - my problem is as follows: I'm writing an ASP.Net 2.0 app with C#. From the app, I call a stored procedure that updates some data in the app's database (SQL Server 2000). This stored procedure...
11
bhcob1
by: bhcob1 | last post by:
Hi, Whenever I delete a record my command button, the record deletes, a list displaying all records is updated and then a message box appears: Microsoft Access can't find the field 'I' referred to...
15
by: sara | last post by:
I have a Memo field in a table to hold notes from a conversation a social worker has had with a client (this is for a non-profit). If the user needs to update the memo field, I need to find the...
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: 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)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.