Access 2003 data not saving

[Pat Randall]

I have an Access 2000 database that's not working well in Access 2003.
I've managed to isolate the problem to this:
If I update the value of a bound text box and close the form using VBA,
the data is not saved.

However, if I update the value of a bound text box through VBA and
manually close the form, the data is saved.

My code works with Office 97, 2000 and XP, but not 2003.
Any ideas?

 

[John Vinson]

My code works with Office 97, 2000 and XP, but not 2003.
Any ideas?

Please post the relevant code.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Pat Randall]

OK here’s the code.
Test this using Access 2003 only.
Create a form
Select any table as it’s recordsource
Now create 1 text box and bind it to any text field from the table.
Create a command button and place the following code in the On Click event
Command1_Click()
Text1 = “X”
DoCmd.Close acForm, "TheFormThatYouCreatedAbove"
End sub

Open the form
Click on the command button


In Access 97, 2000 and XP, the value “X” would replace the value in the
field that text1 was bound to.
In Access 2003, it does not. Nothing gets updated.

Try This:
Remove the DoCmd line
Click the command button
Close the form manually
Now the X will get saved in the field bound to the text1 text box.

 

[Brendan Reynolds]

I can't reproduce that, Pat. On my system (yes, it is Access 2003) the field
is updated.

Do you have the 'Require Variable Declaration' option turned on? (Do you
have a line that says 'Option Explicit' at the top of your form module, just
under the 'Option Compare Database' line?) If not, what happens if you add
it?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Pat Randall]

I can't reproduce that, Pat. On my system (yes, it is Access 2003) the field
is updated.

Do you have the 'Require Variable Declaration' option turned on? (Do you
have a line that says 'Option Explicit' at the top of your form module, just
under the 'Option Compare Database' line?) If not, what happens if you add
it?

Yes I have Option Compare Database and Option Explicit

Thanks for trying to reproduce it. It works for me too 'IF' I design it
using 2003. However, if I follow the same steps but design it with
Access 2000 and run it from 2003, the data is NOT saving as it should.
Do you know why this would happen?

 

[Brendan Reynolds]

The only difference I can think of in that scenario is that *if* you had
changed the default file format in Access 2003 to be the new 2002/2003
format, the MDB created in 2003 would use that format, while the MDB created
in 2000 would use the old format. But I'm using 2000 format with 2003 here,
so is seems unlikely that this is the explanation.

Does it make any difference if you refer to the control using Me!ControlName
or Me.Controls("ControlName") rather than just ControlName? In theory, of
course, it shouldn't, but in practice I find that it does sometimes, though
I cannot say why.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Pat Randall]

I was using the 2000 format. I also tried the Me!ControlName options
but nothing changed. Can you try one more thing? I posted the code in
an earlier post. Can you use a check box instead of a command button.
Place the code in the "Before_Update" event. I tried this with 2003 and
it did NOT save. Here's a quick summary of why this is important to
me. I have a 2000 database that manages our curriculum. When a user is
ready to submit a course for approval, they click a check box on a form.
Everything happens in the Before_Update event of the checkbox. I
check to make sure that the form is completed accurately. If it is, I
set a hidden bound textbox to "Submitted" and close the form through
code. This whole process has been working great for two years with
Access 2000. However, when I use 2003, the record does not get updated
(saved).

 

[Brendan Reynolds]

If I move the code to the BeforeUpdate event procedure of a check box, the
change is not saved. If I move it again to the AfterUpdate event procedure
of the same check box, the change is saved. Closing the form in the
BeforeUpdate procedure seems like a somewhat strange thing to do. I'm a
little surprised that it worked in previous versions. Is moving the code to
the AfterUpdate event procedure an acceptable solution to your problem?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Pat Randall]

Moving the Code to the After_Update event is possible. However, I'm
concerned about why it's not working and the possibility of other areas
of the database not working too.

 

[Brendan Reynolds]

Well, I would certainly check for any other BeforeUpdate event procedures
that are closing the form. Other than that, though, the fact that this code
behaves differently does not necessarily mean that any other code will
behave differently. Changes in behaviour do happen, but not very often. And
the more common the technique, the less likely it is that changes will be
made that would break that technique. It's the unusual stuff that is most
likely to get broken.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Pat Randall]

Thank you for your help.