By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,282 Members | 1,713 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,282 IT Pros & Developers. It's quick & easy.

Access 2003 vs Access 2007

P: n/a
The is a simple example of code that worked in access 2003 but does
not in access 2007. I have checked it with the debugger and the
reason is the value of formField1 does not change in ver. 2007 in the
code as the docmd.gotorecord,,acnext moves down the records. But it
does in ver 2003. Any Suggestions?

Example of Table displayed in a continous form with 5 records.
1
1
1
2
3

Example Code:

When double Clicking on the 1st record the cursor should move to
record 4.

Private Sub formField1_DblClick(Cancel As Integer)
dim vTest as Byte

vTest = 1

Do While [formField1] = vTest
DoCmd.GoToRecord , , acNext
Loop

End Sub

Thanks.
Aug 21 '08 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Microsoft spent big bucks developing and streamlining the Access/
Windows GUI, including scrollbars and default navigation buttons. They
are fast, slick, and, TTBOMK, foolproof. Use them!
Forms are for editing data, not silly buttons.

On Aug 20, 10:12*pm, pballou <pbal...@nctc.comwrote:
>*Any Suggestions?
Aug 21 '08 #2

P: n/a
On Aug 20, 9:28*pm, lyle fairfield <lyle.fairfi...@gmail.comwrote:
Microsoft spent big bucks developing and streamlining the Access/
Windows GUI, including scrollbars and default navigation buttons. They
are fast, slick, and, TTBOMK, foolproof. Use them!
Forms are for editing data, not silly buttons.

On Aug 20, 10:12*pm, pballou <pbal...@nctc.comwrote:
*Any Suggestions?- Hide quoted text -

- Show quoted text -
I just simplified the code to make it easy to understand the problem.
I am doing alot more than just trying to move down the page. It is
part of scheduling program that moves through dates and times to find
open time slots. With that being said do you have any ideas why the
values from the form are not changing??
Aug 21 '08 #3

P: n/a
I don't see why the code would not work in 07.

I would do a few things:

first, do a debug->compile while looking at the code. Does it compile?

And, you could try:

try using me.formField1 in place of [formField]. Or even me!FormField1
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Aug 21 '08 #4

P: n/a
I don't see why the code would not work in 07.

I would do a few things:

first, do a debug->compile while looking at the code. Does it compile?

And, you could try:

try using me.formField1 in place of [formField]. Or even me!FormField1

And, if it has spaces, then perhaps try:

me![FormField]

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

Aug 21 '08 #5

P: n/a
pballou wrote:
The is a simple example of code that worked in access 2003 but does
not in access 2007. I have checked it with the debugger and the
reason is the value of formField1 does not change in ver. 2007 in the
code as the docmd.gotorecord,,acnext moves down the records. But it
does in ver 2003. Any Suggestions?

Example of Table displayed in a continous form with 5 records.
1
1
1
2
3

Example Code:

When double Clicking on the 1st record the cursor should move to
record 4.

Private Sub formField1_DblClick(Cancel As Integer)
dim vTest as Byte

vTest = 1

Do While [formField1] = vTest
DoCmd.GoToRecord , , acNext
Loop

End Sub

Thanks.
Why is vTest set to 1? Why not set vTest to the value of formField1? Ex:

dim vTest as Variant 'have no idea what formField1 is

vTest = Me![formField1]

Do While Me![formField1] = vTest
DoCmd.GoToRecord , , acNext
Loop

BTW, are you updating formField1 as you loop thru the code? Your
description of your problem, what is occurring, what should occur could
use some fleshing out. Why should the value change? Until you provide
a better detailing I believe you'll get some stabs and guesses at what
you are attempting to do as a fix, not a real solution.

Aug 21 '08 #6

P: n/a
On Aug 20, 9:49*pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
I don't see why the code would not work in 07.

I would do a few things:

first, do a debug->compile while looking at the code. Does it compile?

And, you could try:

* *try using me.formField1 in place of [formField]. Or even me!FormField1

--
Albert D. Kallal * *(Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKal...@msn.com
Thanks for your suggestions.

Sorry I replied to Author instead of reply.

I don't submit very many questions to the groups.
Normally I can find my answers just by reading other people's
questions.

This one has stumped me.

It will compile and I tried the me. and me! reference. Still no
luck.
I've been trying everything for a couple of days.

This has been working fine at about 7 customers for several years.
One of my customers had their computers stolen and upgraded to 2007
and I tested the program and found this problem. In my scheduling
program the docmd.gotorecord,,acnext moves down and back up the form
to record the dates and times already scheduled.

Any other ways to move up and down a form other than
"Docmd.GotoRecord,,acNext"?
I've already tried sendkeys with the down arrow and the tab key. It
will move but it will still no change in the value.

I've put in a msgbox after each move statement to see if it is moving
and it does but no value changed. It's like it gets the first
record's value but never updates as it moves.

Any suggestions would be greatly appreciated.

Thanks
Aug 21 '08 #7

P: n/a
You certainly can writet he code differt. I would perahps change the vTest
as byte to

dim vTest as Varient and see if that works.

I assume that other code in the application runs fine??

I am perplexed why your code don't work. There nothing inhenerent in 2007
that changed that should cause a problem.

You could rewrite the code as:

Dim rst As DAO.Recordset
Dim vTest As Variant

vTest = 1
Set rst = Me.Recordset
Do While rst!FormField1 = vTest
rst.MoveNext
Loop

Note that you need to replace "formField1" with the actual name of the
underlying field in the table, NOT the actual name of the contorl used on
the form (often they are the SAME name, but often they are not).

Does most other code run just fine in the application?

Try the above..it should work

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Aug 21 '08 #8

P: n/a
pballou wrote:
On Aug 20, 9:49 pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
>>I don't see why the code would not work in 07.

I would do a few things:

first, do a debug->compile while looking at the code. Does it compile?

And, you could try:

try using me.formField1 in place of [formField]. Or even me!FormField1

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKal...@msn.com


Thanks for your suggestions.

Sorry I replied to Author instead of reply.

I don't submit very many questions to the groups.
Normally I can find my answers just by reading other people's
questions.

This one has stumped me.

It will compile and I tried the me. and me! reference. Still no
luck.
I've been trying everything for a couple of days.

This has been working fine at about 7 customers for several years.
One of my customers had their computers stolen and upgraded to 2007
and I tested the program and found this problem. In my scheduling
program the docmd.gotorecord,,acnext moves down and back up the form
to record the dates and times already scheduled.

Any other ways to move up and down a form other than
"Docmd.GotoRecord,,acNext"?
Dim rst = Me.Recordsetclone
rst.bookmark = me.bookmark
Do while not rst.eof
rst.movenext
If field <var then exit do
Loop
if rst.eof then rst.moveprevious
me.bookmark = rst.bookmark
I've already tried sendkeys with the down arrow and the tab key. It
will move but it will still no change in the value.
I've put in a msgbox after each move statement to see if it is moving
and it does but no value changed. It's like it gets the first
record's value but never updates as it moves.

Any suggestions would be greatly appreciated.
Yeah. Tell us what you want to do. What's all this about "change in
the value" stuff mean.

>
Thanks
Aug 21 '08 #9

P: n/a
Checking this it !!!SEEMS!!! that Access 2007 code does !!!NOT!!!
return the value of a textbox in the record to which we have moved
with DoCmd.GoToRecord from !!!WITHIN!!! the procedure where the
DoCmd.GoToRecord lives (after the DoCmd.GoToRecord is called). It
continues with the original value of the text box in the record before
DoCmd.GoToRecord is called. This is as the OP reported.

I was successful in retrieving this value only in the form's OnCurrent
event code as follows:
(Northwinds 2007 - Form:Purchase_Order_List

Dim SupplierID&

Private Sub Form_Current()
SupplierID = Supplier_ID.Value
End Sub

Private Sub Purchase_Order_ID_Click()
Dim TestSupplierID&
TestSupplierID = Supplier_ID.Value
Do
DoCmd.GoToRecord
If SupplierID <TestSupplierID Then Exit Do
Loop
End Sub

(What does this do? It moves down the continuous form while the
supplier is the same supplier as in the record where we started).

Have I tested this notion thoroughly? Nope!
Is the behaviour different than in earlier versions of Access? TTBOMR,
yes, but I didn't test that.
Could the OP solve his problem with my code modified? Maybe; my guess
is "Yes".
Could my code be streamlined. Sure. Go ahead!

On Aug 20, 10:41*pm, pballou <pbal...@nctc.comwrote:
On Aug 20, 9:28*pm, lyle fairfield <lyle.fairfi...@gmail.comwrote:
Microsoft spent big bucks developing and streamlining the Access/
Windows GUI, including scrollbars and default navigation buttons. They
are fast, slick, and, TTBOMK, foolproof. Use them!
Forms are for editing data, not silly buttons.
On Aug 20, 10:12*pm, pballou <pbal...@nctc.comwrote:
>*Any Suggestions?- Hide quoted text -
- Show quoted text -

I just simplified the code to make it easy to understand the problem.
I am doing alot more than just trying to move down the page. *It is
part of scheduling program that moves through dates and times to find
open time slots. *With that being said do you have any ideas why the
values from the form are not changing??
Aug 21 '08 #10

P: n/a
On Aug 21, 4:35*pm, lyle fairfield <lyle.fairfi...@gmail.comwrote:
Checking this it !!!SEEMS!!! that Access 2007 code does !!!NOT!!!
return the value of a textbox in the record to which we have moved
with DoCmd.GoToRecord from !!!WITHIN!!! the procedure where the
DoCmd.GoToRecord lives (after the DoCmd.GoToRecord is called). It
continues with the original value of the text box in the record before
DoCmd.GoToRecord is called. This is as the OP reported.

I was successful in retrieving this value only in the form's OnCurrent
event code as follows:
(Northwinds 2007 - Form:Purchase_Order_List

Dim SupplierID&

Private Sub Form_Current()
* * SupplierID = Supplier_ID.Value
End Sub

Private Sub Purchase_Order_ID_Click()
* * Dim TestSupplierID&
* * TestSupplierID = Supplier_ID.Value
* * Do
* * * * DoCmd.GoToRecord
* * * * If SupplierID <TestSupplierID Then Exit Do
* * Loop
End Sub

(What does this do? It moves down the continuous form while the
supplier is the same supplier as in the record where we started).

Have I tested this notion thoroughly? Nope!
Is the behaviour different than in earlier versions of Access? TTBOMR,
yes, but I didn't test that.
Could the OP solve his problem with my code modified? Maybe; my guess
is "Yes".
Could my code be streamlined. Sure. Go ahead!

On Aug 20, 10:41*pm, pballou <pbal...@nctc.comwrote:
On Aug 20, 9:28*pm, lyle fairfield <lyle.fairfi...@gmail.comwrote:
Microsoft spent big bucks developing and streamlining the Access/
Windows GUI, including scrollbars and default navigation buttons. They
are fast, slick, and, TTBOMK, foolproof. Use them!
Forms are for editing data, not silly buttons.
On Aug 20, 10:12*pm, pballou <pbal...@nctc.comwrote:
*Any Suggestions?- Hide quoted text -
- Show quoted text -
I just simplified the code to make it easy to understand the problem.
I am doing alot more than just trying to move down the page. *It is
part of scheduling program that moves through dates and times to find
open time slots. *With that being said do you have any ideas why the
values from the form are not changing??- Hide quoted text -

- Show quoted text -
Lyle, You are the MAN!

This is exactly what I needed and I would never have found it.

Thanks.
Aug 22 '08 #11

P: n/a
Salad <oi*@vinegar.comwrote in
news:4Y******************************@earthlink.co m:
Dim rst = Me.Recordsetclone
rst.bookmark = me.bookmark
Do while not rst.eof
rst.movenext
If field <var then exit do
Loop
if rst.eof then rst.moveprevious
me.bookmark = rst.bookmark
Please don't recommend that people write code like that. Instead:

With Me.RecordsetClone
.Bookmark = Me.Bookmark
Do While Not .EOF
.Movenext
If field <var then exit do
Loop
If .EOF then .MovePrevious
Me.Bookmark = .Bookmark
End With

But if you *do* recommend unnecessarily initializing a recordset
variable, be sure that you clean up by ending your code with:

Set rst = Nothing

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 22 '08 #12

P: n/a
pballou <pb*****@nctc.comwrote in
news:67**********************************@f63g2000 hsf.googlegroups.co
m:
I am doing alot more than just trying to move down the page. It
is part of scheduling program that moves through dates and times
to find open time slots.
It is likely that you should be able to get the answer with SQL
instead of jumping around in a form's recordset (or RecordsetClone).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 22 '08 #13

This discussion thread is closed

Replies have been disabled for this discussion.