Access 2007 bug? Focus does not stay on new record in code

[google]

In a continuous form the following code is under a button in the form
header. In Access 2003 and earlier, this goes to a new record, then
adds
relevant data to that new record.

DoCmd.GoToRecord , , A_NEWREC

If <some codeThen
[AItemNumber] = "CA002"
Else
'Some other code
End If

Call InsertItemDetails 'This procedure adds other data to
the new
record by looking up data that matches [AItemNumber]=CA002

The InsertItemDetails procedure contains lots of code including
statements such as

If [AItemNumber] = "CA002" Then
'Insert some data into the record
End If
In Access 2007, this process creates the new record, and adds the
CA002
value to the field [AItemNumber]. However the focus in code does not
go
to the new record, but rather stays on the existing record. If the
current record (before clicking the NEw Item button) has e.g. CB100 in
the [AItemNumber] field, incorrect data is put into the new record.
When
stepping through the code, although a new record has been created on
the
form with [AItemNumber]=CA002, hovering over the [AItemNumber] text in
the code shows the value to be CB100. In other words, the form has
gone
to a new record but the code hasn't. Later when I use the If
[AitemNumber] = statement, the value CB100 is used instead of CA002.
In
Access 2003, the same code uses the CA002 value.

Has anyone experienced this? Is it a 2007 bug? Can you suggest any
workarounds? Maybe I need to save the new record and specifically
navigate to it before adding any extra data from code?

Thanks
Owen Jenkins

[Tom van Stiphout]

On 30 May 2007 19:24:56 -0700, go****@healthbase.com.au wrote:

I just tried these 2 lines:
RunCommand acCmdRecordsGoToNew
Me.SomeTextField = "HELLO WINDOWS"
in a continuous form, and it worked as expected: the cursor went to a
new record, the field now has the value, and focus is on that row, on
the (first) ID field.

You may want to use an elimination strategy to see where your code
goes south.

-Tom.

>In a continuous form the following code is under a button in the form
header. In Access 2003 and earlier, this goes to a new record, then
adds
relevant data to that new record.

DoCmd.GoToRecord , , A_NEWREC

If <some codeThen
[AItemNumber] = "CA002"
Else
'Some other code
End If

Call InsertItemDetails 'This procedure adds other data to
the new
record by looking up data that matches [AItemNumber]=CA002

The InsertItemDetails procedure contains lots of code including
statements such as

If [AItemNumber] = "CA002" Then
'Insert some data into the record
End If
In Access 2007, this process creates the new record, and adds the
CA002
value to the field [AItemNumber]. However the focus in code does not
go
to the new record, but rather stays on the existing record. If the
current record (before clicking the NEw Item button) has e.g. CB100 in
the [AItemNumber] field, incorrect data is put into the new record.
When
stepping through the code, although a new record has been created on
the
form with [AItemNumber]=CA002, hovering over the [AItemNumber] text in
the code shows the value to be CB100. In other words, the form has
gone
to a new record but the code hasn't. Later when I use the If
[AitemNumber] = statement, the value CB100 is used instead of CA002.
In
Access 2003, the same code uses the CA002 value.

Has anyone experienced this? Is it a 2007 bug? Can you suggest any
workarounds? Maybe I need to save the new record and specifically
navigate to it before adding any extra data from code?

Thanks
Owen Jenkins

[Owen]

I just tried these 2 lines:

RunCommand acCmdRecordsGoToNew
Me.SomeTextField = "HELLO WINDOWS"
in a continuous form, and it worked as expected: the cursor went to a
new record, the field now has the value, and focus is on that row, on
the (first) ID field.

You may want to use an elimination strategy to see where your code
goes south.

Well for me, the cursor goes to the new record and inserts the
specified text, but if the code then says If Me.SomeTextField="HELLO
WINDOWS" Then, this evaluates as false.

Owen

[Tom van Stiphout]

On 30 May 2007 21:59:42 -0700, Owen <go****@healthbase.com.auwrote:

Ah, I think you're onto something. I can confirm this behavior to be
different between A2000 and A2007.
My code:
RunCommand acCmdRecordsGoToNew
Me.CategoryName = "Hello"
Me.CategoryName.SetFocus
Debug.Print Me.CategoryName = "Hello", Me.CategoryName.Value,
Me.CategoryName.Text

In A2000 it prints the data about the new record, whereas in A2007 it
prints the data about the record you came from. Even when I added the
SetFocus line.

Weird. Will have to look at that some more.

-Tom.

>I just tried these 2 lines:
RunCommand acCmdRecordsGoToNew
Me.SomeTextField = "HELLO WINDOWS"
in a continuous form, and it worked as expected: the cursor went to a
new record, the field now has the value, and focus is on that row, on
the (first) ID field.

You may want to use an elimination strategy to see where your code
goes south.

Well for me, the cursor goes to the new record and inserts the
specified text, but if the code then says If Me.SomeTextField="HELLO
WINDOWS" Then, this evaluates as false.

Owen

[Owen]

Ahhh, thank you! It's not just me then. This could affect a lot of
applications I expect. The failure of the SetFocus instruction is a
little alarming. It looks like I need to save the record then go to
the specific record. That could be a lot of fiddling for my fairly
code intensive app. Any suggestions for the moment? It's a bit
reminicient of the famous bookmark bug.

Owen

[Tom van Stiphout]

On Thu, 31 May 2007 20:45:29 -0700, Owen <go****@healthbase.com.au>
wrote:

I did a bit more research and the bug persists. It occurs in ADP as
well as in ACCDB. It occurs with 3 different ways of going to a new
record (see code below). I don't have a good workaround yet. I would
report this one to Microsoft.
http://support.microsoft.com/gp/contactbug
RunCommand acCmdRecordsGoToNew
Me.CategoryName = "Hello"
Me.CategoryName.SetFocus
Debug.Print Me.CategoryID, Me.CategoryName = "Hello",
Me.CategoryName.Value, Me.CategoryName.Text

With Me.RecordsetClone
.AddNew
Me.Bookmark = .Bookmark
Me.CategoryName = "Hello"
Me.CategoryName.SetFocus
Debug.Print Me.CategoryID, Me.CategoryName = "Hello",
Me.CategoryName.Value, Me.CategoryName.Text
End With

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.CategoryName = "Hello"
Me.CategoryName.SetFocus
Debug.Print Me.CategoryID, Me.CategoryName = "Hello",
Me.CategoryName.Value, Me.CategoryName.Text

-Tom.

>Ahhh, thank you! It's not just me then. This could affect a lot of
applications I expect. The failure of the SetFocus instruction is a
little alarming. It looks like I need to save the record then go to
the specific record. That could be a lot of fiddling for my fairly
code intensive app. Any suggestions for the moment? It's a bit
reminicient of the famous bookmark bug.

Owen

[Tom van Stiphout]

On Sat, 02 Jun 2007 12:44:33 -0700, Tom van Stiphout
<no*************@cox.netwrote:

Also, this bug does not depend on which form view is selected: it
happens with Single Form and Datasheet as well.

-Tom.

<clip>

[Allen Browne]

Tom, I can't reproduce this problem.

Using A2007 on Vista, your first block gives the expected results:
RunCommand acCmdRecordsGoToNew
Me.CategoryName = "Hello"
Me.CategoryName.SetFocus
Debug.Print Me.CategoryID, Me.CategoryName = "Hello", _
Me.CategoryName.Value, Me.CategoryName.Text
That is:
a) the form moves to the new record.
b) the correct field displays the value Hello.
c) the correct field gets focus.
d) the debug window displays the new ID, True, Hello, Hello.
Your second block will not work reliably. The AddNew causes the clone set to
open its own buffer. The bookmark ought to fail, since the new record in the
clone set is only a buffer; it is not yet a saved record and should not have
any bookmark. However *all* versions of Access (at least as far back as
Access 2) return spurious information when you refer to the bookmark of the
new record where an edit has already begun. And, it is generally the last
record that was current before moving to the new record. So, the line:
Me.Bookmark = .Bookmark
is invalid and should fail, but actually gives wrong results in all versions
of Access.

--
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.

"Tom van Stiphout" <no*************@cox.netwrote in message
news:bq********************************@4ax.com...

On Thu, 31 May 2007 20:45:29 -0700, Owen <go****@healthbase.com.au>
wrote:

I did a bit more research and the bug persists. It occurs in ADP as
well as in ACCDB. It occurs with 3 different ways of going to a new
record (see code below). I don't have a good workaround yet. I would
report this one to Microsoft.
http://support.microsoft.com/gp/contactbug
RunCommand acCmdRecordsGoToNew
Me.CategoryName = "Hello"
Me.CategoryName.SetFocus
Debug.Print Me.CategoryID, Me.CategoryName = "Hello",
Me.CategoryName.Value, Me.CategoryName.Text

With Me.RecordsetClone
.AddNew
Me.Bookmark = .Bookmark
Me.CategoryName = "Hello"
Me.CategoryName.SetFocus
Debug.Print Me.CategoryID, Me.CategoryName = "Hello",
Me.CategoryName.Value, Me.CategoryName.Text
End With

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.CategoryName = "Hello"
Me.CategoryName.SetFocus
Debug.Print Me.CategoryID, Me.CategoryName = "Hello",
Me.CategoryName.Value, Me.CategoryName.Text

-Tom.

>>Ahhh, thank you! It's not just me then. This could affect a lot of
applications I expect. The failure of the SetFocus instruction is a
little alarming. It looks like I need to save the record then go to
the specific record. That could be a lot of fiddling for my fairly
code intensive app. Any suggestions for the moment? It's a bit
reminicient of the famous bookmark bug.

Owen

[Tom van Stiphout]

On Sun, 3 Jun 2007 23:54:55 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

Strange. First block prints the old record for me (and I'm guessing
for the OP as well). Tried it with both an ADP as well as an ACCDB.
I'm on WinXP, fully patched. My machine also has several other
versions of Access on it. Will try it on a few machines at work
tomorrow.

I agree I should have left out code block #2.

-Tom.

>Tom, I can't reproduce this problem.

Using A2007 on Vista, your first block gives the expected results:
RunCommand acCmdRecordsGoToNew
Me.CategoryName = "Hello"
Me.CategoryName.SetFocus
Debug.Print Me.CategoryID, Me.CategoryName = "Hello", _
Me.CategoryName.Value, Me.CategoryName.Text
That is:
a) the form moves to the new record.
b) the correct field displays the value Hello.
c) the correct field gets focus.
d) the debug window displays the new ID, True, Hello, Hello.
Your second block will not work reliably. The AddNew causes the clone set to
open its own buffer. The bookmark ought to fail, since the new record in the
clone set is only a buffer; it is not yet a saved record and should not have
any bookmark. However *all* versions of Access (at least as far back as
Access 2) return spurious information when you refer to the bookmark of the
new record where an edit has already begun. And, it is generally the last
record that was current before moving to the new record. So, the line:
Me.Bookmark = .Bookmark
is invalid and should fail, but actually gives wrong results in all versions
of Access.

[Owen]

I appreciate your following up this issue.
The problem is certainly reproducible. For me, block 1 always returns
data from the previously current record not the new record. This
happens in A2007 (mdb) on Vista, but not in A2003 on vista, or A2000
or A2003 on XPPro. I haven't tried A2007 on XP.

Owen

[Allen Browne]

Owen, after some communication with Tom van Stiphout, I can confirm the
problem in A2007 also.

The problem seems to be that if you change record in the Click event, the
code continues to report the old record until the Click event terminates.

This is true regardless of how the record is changed, including RunCommand
acCmdGoto..., DoCmd.GotoRecord, FindFirst on the form's RecordsetClone, etc,
and even a Requery.

It is not a timing issue: you can even add a STOP to the event procedure.
Even after resuming from a long pause, it still reports the old record and
not the current one.

The problem occurs in the Click event of an image control, label, text box,
etc, but in my tests the problem did not show up in the Click event of a
command button.

It does occur in an MDB as well as an ACCDB. Earlier versions of Access did
not have this wrong behavior.

--
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.

"Owen" <go****@healthbase.com.auwrote in message
news:11*********************@q19g2000prn.googlegro ups.com...

>I appreciate your following up this issue.
The problem is certainly reproducible. For me, block 1 always returns
data from the previously current record not the new record. This
happens in A2007 (mdb) on Vista, but not in A2003 on vista, or A2000
or A2003 on XPPro. I haven't tried A2007 on XP.

Owen

[Owen]

Allen,

Thanks, you've obviously spent some time on this. So what to do next?
I sent a message to Microsoft using their generic contact form and
providing a link to this discussion but have no idea if this will get
to the relevant people. Any idea whether we could expect this to be
fixed sometime soon, or even at all?

Owen

[Allen Browne]

Okay, Owen, I have logged the bug with Microsoft, so they are aware of it.
They have a little sample db I created to demonstrate the bug.

MS will then decide whether this bug will affect enough people to be worth
fixing or not. Since the previous versions worked correctly, they might, but
don't hold your breath. Unless some very large customers (or a very large
number of customers) complain, I doubt you will see a special hotfix.

In the mean time, I was not able to produce the problem with a command
button, so you may be able to use that as a workaround. Not as pretty as the
image control, but it at least it gives you a workaround.

--
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.

"Owen" <go****@healthbase.com.auwrote in message
news:11*********************@q75g2000hsh.googlegro ups.com...

Allen,

Thanks, you've obviously spent some time on this. So what to do next?
I sent a message to Microsoft using their generic contact form and
providing a link to this discussion but have no idea if this will get
to the relevant people. Any idea whether we could expect this to be
fixed sometime soon, or even at all?

Owen

[Owen]

Thanks Allen.
I'll try the command button. I'm currently using a combobox, but will
have to check my code for other instances of the problem.
Is there an easy way to find out when/if a fix becomes available?
Owen

[Allen Browne]

Hmm. If a hotfix is released, there will be a KB article. If it's fixed as
part of a service pack, the issues may be listed in a KB as well.

If you want to stay up with them all, Jeff Conrad (alias the Access Junkie)
maintains a list here:
http://accessjunkie.com/kb.aspx
or you can search for yourself at:
http://support.microsoft.com/search/?adv=1

I have added your bug to the list at:
http://allenbrowne.com/Access2007.html#Bugs
Ultimately, I plan to update that if Microsoft fixes it.

--
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.

"Owen" <go****@healthbase.com.auwrote in message
news:11**********************@p47g2000hsd.googlegr oups.com...

Thanks Allen.
I'll try the command button. I'm currently using a combobox, but will
have to check my code for other instances of the problem.
Is there an easy way to find out when/if a fix becomes available?
Owen

[Owen]

Thanks!

[Tom van Stiphout]

On Mon, 04 Jun 2007 19:35:02 -0700, Owen <go****@healthbase.com.au>
wrote:

The workaround I would propose is not to rely on the new record at
all. Remember where this started: you programmatically enter data in a
new row, and while that's happening want to read back some of what has
been entered.
Certainly the code could be restructured so that isn't necessary. For
example you could create a UDT corresponding to the table, do all your
magic filling it, and once you're completely done, write it in its
entirety to a new record. If in the meantime you wanted to know the
value of one of the field, read it from the UDT.

-Tom.

>Thanks Allen.
I'll try the command button. I'm currently using a combobox, but will
have to check my code for other instances of the problem.
Is there an easy way to find out when/if a fix becomes available?
Owen