Access Returns different values if code is run from "close" event

[BrianDP]

Regarding the piece of code below, if I run it from a button on a
form, it writes the correct value into the "Lcontrol" table, however
if I run the code from the "close" event, it writes an incorrect value
to the "Lcontrol" table.

Private Sub Form_Close()

Dim cc As Integer
Dim db As Database
Dim rst As Recordset

cc = Forms![customer definition]!cust_code

Set db = CurrentDb()
Set rst = db.OpenRecordset("lcontrol")

rst.MoveFirst
rst.Edit
rst!lastcust = cc
rst.Update
rst.Close

End Sub

 

[BrianDP]

While we're at it, the second part of this exercise (nightmare?) is
where I use the value I stored in the previous piece of code to open
the form back up with that record..

However, like the piece of code form the last msg, it returns an
incorrect result. If I run it this way, it gets CLOSE to the record,
but always one record back from the one it was SUPPOSED to open. Now,
if at the end of the code below, I put any kind of statement that
deals with user input, say, a msgbox, or inputbox, then for some
reason it opens the from to the correct record number (Cue twilight
zone music)

Help! Thanks!

-BrianDP
Best Data Processing


Dim cc As Integer
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("lcontrol")

rst.MoveFirst
cc = rst!lastcust
rst.Close

DoCmd.OpenForm "customer definition"
If Nz(cc) <> 0 Then
Forms("Customer definition").Recordset.FindFirst "cust_code = " & cc
End If

 

[John W. Vinson]

Regarding the piece of code below, if I run it from a button on a
form, it writes the correct value into the "Lcontrol" table, however
if I run the code from the "close" event, it writes an incorrect value
to the "Lcontrol" table.

Private Sub Form_Close()

Dim cc As Integer
Dim db As Database
Dim rst As Recordset

cc = Forms![customer definition]!cust_code

Set db = CurrentDb()
Set rst = db.OpenRecordset("lcontrol")

rst.MoveFirst
rst.Edit
rst!lastcust = cc
rst.Update
rst.Close

End Sub

What does it write? Where? What would be "correct"? If "lcontrol" is a Table,
then the MoveFirst is sort of meaningless, as tables have no defined order;
does it matter into which record you write the value?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[BrianDP]

Regarding the piece of code below, if I run it from a button on a
form, it writes the correct value into the "Lcontrol" table, however
if I run the code from the "close" event, it writes an incorrect value
to the "Lcontrol" table.

Private Sub Form_Close()

Dim cc As Integer
Dim db As Database
Dim rst As Recordset

cc = Forms![customer definition]!cust_code

Set db = CurrentDb()
Set rst = db.OpenRecordset("lcontrol")

rst.MoveFirst
rst.Edit
rst!lastcust = cc
rst.Update
rst.Close

End Sub

What does it write? Where? What would be "correct"? If "lcontrol" is a Table,
then the MoveFirst is sort of meaningless, as tables have no defined order;
does it matter into which record you write the value?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Sorry if I was unclear. That Lcontrol is just a local table, two
columns, first one is autonumber, second one is a text field. When
you close the form, the script runs that writes the account number of
the current record to lcontrol!lastcust. Then next time you go to
open that form, it'll open the form to the customer you were on last
time you were on the form. Make any sense?

Still, why would the value of that field be reported differently
depending on where the code was called from? (being called from a
button, as opposed to being stuck to the 'close' event.)

Thanks Doctor V!

-BrianDP

 

[John W. Vinson]

Sorry if I was unclear. That Lcontrol is just a local table, two
columns, first one is autonumber, second one is a text field. When
you close the form, the script runs that writes the account number of
the current record to lcontrol!lastcust. Then next time you go to
open that form, it'll open the form to the customer you were on last
time you were on the form. Make any sense?

Still, why would the value of that field be reported differently
depending on where the code was called from? (being called from a
button, as opposed to being stuck to the 'close' event.)

Again... a table HAS NO ORDER.

If there are 612 records in lcontrol, Access will move to whatever record it
finds convenient on MoveFirst. This might well be a different record if you
open it from code than from the UI; it might be a different record if you open
it from two bits of code.

MoveFirst *in a Table* is not terribly useful. I'd really suggest opening a
Query that retrieves a single record.

If lcontrol has only one record... that's different.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Mike Painter]

You still need to tell us what it writes and how they differ.

 

[BrianDP]

Again... a table HAS NO ORDER.

If there are 612 records in lcontrol, Access will move to whatever recordit
finds convenient on MoveFirst. This might well be a different record if you
open it from code than from the UI; it might be a different record if youopen
it from two bits of code.

MoveFirst *in a Table* is not terribly useful. I'd really suggest openinga
Query that retrieves a single record.

If lcontrol has only one record... that's different.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Sorry for being unclear. YES. Lcontrol has only one record. Again,
because I have this straight in MY mind, I just assume it is crystal
clear to everyone else. So Yes, only one line, so, no reason to tell
it grab only one, because there IS only one. And, when you leave a
form, it moves to the first record of Lcontrol, and writes the account
number there on the first line of Lcontrol.


Okay, for the next mesage from Mike Painter there.. If you run the
routine from a button, it works correctly. It write the account
number of the current record into that first line of Lcontrol.
However if you have the code setup to run in the Close event, then it
doesn't write the correct number- It appears to be writing the correct
field to the file. However if you run the code triggered from the
Close event, somehow the current record gets changed, so what it shows
in code that is going to be written, is NOT the current Account
number.

Thanks for your help guys!

-Brian

 

[Bob Quintal]

m:

Sorry for being unclear. YES. Lcontrol has only one record.
Again, because I have this straight in MY mind, I just assume it
is crystal clear to everyone else. So Yes, only one line, so, no
reason to tell it grab only one, because there IS only one. And,
when you leave a form, it moves to the first record of Lcontrol,
and writes the account number there on the first line of Lcontrol.


Okay, for the next mesage from Mike Painter there.. If you run
the routine from a button, it works correctly. It write the
account number of the current record into that first line of
Lcontrol. However if you have the code setup to run in the Close
event, then it doesn't write the correct number- It appears to be
writing the correct field to the file. However if you run the
code triggered from the Close event, somehow the current record
gets changed, so what it shows in code that is going to be
written, is NOT the current Account number.

Thanks for your help guys!

-Brian

Move your code to the form's unload event

***********
From VBA Help for Access 2003:

When you close a form, the following events occur in this order:

Unload Þ Deactivate Þ Close

The Unload event occurs before the Close event.

************

What may be happening is that your code is executing after the
recordset is closed, so that what you think is the account number is
just garbage in memory.

 

[Mike Painter]

Move your code to the form's unload event

***********
From VBA Help for Access 2003:

When you close a form, the following events occur in this order:

Unload Þ Deactivate Þ Close

The Unload event occurs before the Close event.

************

What may be happening is that your code is executing after the
recordset is closed, so that what you think is the account number is
just garbage in memory.

And the focus is lost with deactivate.

Try running DoEvents. after your code