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

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

On Tue, 21 Sep 2010 09:18:33 -0700 (PDT), BrianDP <(E-Mail Removed)> wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Sep 21, 1:55*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Tue, 21 Sep 2010 09:18:33 -0700 (PDT), BrianDP <bdp...@gmail.com> wrote:
> >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/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../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

On Tue, 21 Sep 2010 13:46:50 -0700 (PDT), BrianDP <(E-Mail Removed)> wrote:


>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

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

On Sep 21, 6:47*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Tue, 21 Sep 2010 13:46:50 -0700 (PDT), BrianDP <bdp...@gmail.com> wrote:
> >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 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/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../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

BrianDP <(E-Mail Removed)> wrote in
news:99ca6f25-d096-4803-af89-(E-Mail Removed)
m:

> On Sep 21, 6:47*pm, John W. Vinson
> <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
>> On Tue, 21 Sep 2010 13:46:50 -0700 (PDT), BrianDP
>> <bdp...@gmail.com> wrot
> e:
>> >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 y
> ou
>> 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/For...-US/accessdev/
>> *http://social.answers.microsoft.com/.../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
>
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.


--
Bob Q.
PA is y I've altered my address.

Bob Quintal wrote:
> 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