programmatically get to the little number in the VCR controls at the bottom of a form

[Phlip]

Accessories:

Here's a quick and stupid question that anyone can pick up by using Access
for a while, but which resists automated searching.

How can Automation reach out to the little number in the VCR controls at the
bottom of a form? Is it the Form.Recordset.AbsolutePosition? The
Form.Recordset.Index, or what?

The VCR controls say "Record: |< < [ 1] > >| >* of 4", for example.

Before you answer, please understand I am aware that DAO and ADO cursors
float their current index concept over indefinite numbers of records. I am
not a newbie learning to manipulate a recordset by itself. I need to answer
the question, "What would the user see if it looked at the [ 1] in the VCR
controls?"

 

[Arvin Meyer [MVP]]

Using the GoToRecord Method it is the Offset ffrom the beginning of the
recordset. Here's a listing from the helpfiles:

The following example uses the GoToRecord method to make the seventh record
in the form Employees current:

DoCmd.GoToRecord acDataForm, "Employees", acGoTo, 7
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Phlip]

Arvin Meyer [MVP] wrote;

Using the GoToRecord Method it is the Offset ffrom the beginning of the
recordset. Here's a listing from the helpfiles:

The following example uses the GoToRecord method to make the seventh record
in the form Employees current:

DoCmd.GoToRecord acDataForm, "Employees", acGoTo, 7

How do I query what record has been gone too? A query - not an event or a
method.

Please focus carefully on the question because in many people's experiences
it will be so obvious they won't realize I'm asking it!

I need to answer
the question, "What would the user see if it looked at the [ 1] in the
VCR controls?"

 

[Arvin Meyer [MVP]]

Arvin Meyer [MVP] wrote;


How do I query what record has been gone too? A query - not an event or a
method.

I am not sure that you can. There is no offset or row number that the JET
engine recognizes. However, once you''ve gone to the record on the form, you
can identify it by the Primary Key value and use that value to query
anything based upon it. You still need to use code along with SQL.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Phlip]

I am not sure that you can. There is no offset or row number that the JET
engine recognizes. However, once you''ve gone to the record on the form, you
can identify it by the Primary Key value and use that value to query
anything based upon it. You still need to use code along with SQL.

Suppose I wanted to write the VCR control from scratch. (Maybe my boss wants
the text in Sanskrit, not English.)

I'm fairly certain that < and > call MoveLast and MoveNext. The "of 4" is
probably the rs.RecordCount.

What's the [ 1] from? The rs.Index? The rs.AbsolutePosition?

 

[Phlip]

Phlip wrote;

Suppose I wanted to write the VCR control from scratch. (Maybe my boss wants
the text in Sanskrit, not English.)

I'm fairly certain that < and > call MoveLast and MoveNext. The "of 4" is
probably the rs.RecordCount.

What's the [ 1] from? The rs.Index? The rs.AbsolutePosition?

I could go on messing with y'all but I just found it. It's the
Form.CurrentRecord.

(Notice that's Google-proof, because "Form Current Record" will return half
the Googlebase...)

Putting the property on the form, not the recordset, suggests an
architecture where the CurrentRecord property interprets the Recordset's
native properties and concocts a GUI-ready number, not a true aspect of the
underlying cursor.

No question is implied.

 

[Dirk Goldgar]

Phlip wrote;

Suppose I wanted to write the VCR control from scratch. (Maybe my
boss wants the text in Sanskrit, not English.)

I'm fairly certain that < and > call MoveLast and MoveNext. The "of
4" is probably the rs.RecordCount.

What's the [ 1] from? The rs.Index? The rs.AbsolutePosition?

I could go on messing with y'all but I just found it. It's the
Form.CurrentRecord.

(Notice that's Google-proof, because "Form Current Record" will
return half the Googlebase...)

I don't know about that. If one sensibly googles for "Microsoft Access
form current record", the first page of results (at least when I did it
just now) includes a link to a page discussing how to replace the
standard Access navigation controls with one's own custom set, with
sample code that shows the use of the CurrentRecord property.

 

[Arvin Meyer [MVP]]

Just use the CurrentRecord property in a textbox's controlsource like:

=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
Count(*))
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I am not sure that you can. There is no offset or row number that the JET
engine recognizes. However, once you''ve gone to the record on the form, you
can identify it by the Primary Key value and use that value to query
anything based upon it. You still need to use code along with SQL.

Suppose I wanted to write the VCR control from scratch. (Maybe my boss wants
the text in Sanskrit, not English.)

I'm fairly certain that < and > call MoveLast and MoveNext. The "of 4" is
probably the rs.RecordCount.

What's the [ 1] from? The rs.Index? The rs.AbsolutePosition?

 

[Phlip]

I could have told you that. <g>

For this project, maybe all my posts should start with an MVP Disclaimer:

"I am coming to Access from a distinguished career in computer science,
linguistics, video games, and advanced software development methodologies. I
am not fresh out of grammar school and learning programming via the Access
wizards. I will never need to ask newbie questions, yet I do not know how to
ask my advanced ones in ways that won't trigger the default newbie answers.
I am typically trying to do the opposite or inverse of what newbies do to
develop."

 

[Tony Toews]

For this project, maybe all my posts should start with an MVP Disclaimer:

"I am coming to Access from a distinguished career in computer science,
linguistics, video games, and advanced software development methodologies. I
am not fresh out of grammar school and learning programming via the Access
wizards. I will never need to ask newbie questions, yet I do not know how to
ask my advanced ones in ways that won't trigger the default newbie answers.
I am typically trying to do the opposite or inverse of what newbies do to
develop."

So all this means to me is that you will insist on twisting and
forcing Access to do things the way you think it should. Rather than
finding out the best way of doing something within the Access
framework.

You may be better off writing your own database product as a
competitor to Access

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Phlip]

So all this means to me is that you will insist on twisting and
forcing Access to do things the way you think it should. Rather than
finding out the best way of doing something within the Access
framework.

Could someone Google all my other posts, then warn Tony of the danger he's
in?

Or should I just start the lecture now? ;-)

 

[Tony Toews]

Could someone Google all my other posts, then warn Tony of the danger he's
in?

Or should I just start the lecture now? ;-)

<shrug> What danger? What lecture? I don't care about your
"distinguished career". Mine is every bit as distinguished in my
mind. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Arvin Meyer]

Could someone Google all my other posts, then warn Tony of the danger he's
in?

Or should I just start the lecture now? ;-)

Oh Please ... spare us. Before you start your monologue perhaps you'd better
have a look at Tony's posting credentials. They exceed yours by at least a
factor of 2 and they're mostly MS-Access based, not scattered all over.
You'd do well, no matter how much you think you know, to sit at his feet and
gobble up whatever crumbs of wisdom he sees fit to throw you. Microsoft
didn't choose him as an MVP because he has a big mouth. They chose him for
his skills and degree of helpfulness, as evidenced not only by his posts,
but by his website, his articles, and his work as a consultant.

You are welcome here, and we'll even overlook your pontificating, but
understand that we will ignore you completely if you insult any of us. So
unless you really do know it *all*, just cool it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Phlip]

<shrug> What danger? What lecture? I don't care about your
"distinguished career".

Actually, that was an inside joke, in my mind too. It's the "distinction"
concept from G. Spencer Brown's book /The Laws of Form/. You had to be
there...

Mine is every bit as distinguished in my
mind. <smile>

Hmm. Arvin has politely advised me to learn from you. So instead of a
lecture, I'l just ask:

How, in general, do you do "Test Driven Development" with Access? How, for
example, do you write a test which shows that the Delete button on a form
deletes the current record? (Yes, that's stupid. It's just an example.)

Next, how do you make that test run as fast and silent as possible, so you
can run it as often as possible?

Hint: Anything the user can do to a form, the test rig must do to. This
requires writing everything a form can do, _backwards_.

That, in turn, requires researching things nobody else researches or
documents about a system.

(I know I know, why bother writing tests at all when VBA has such a helpful
debugger. ;-)

 

[Tony Toews]

Actually, that was an inside joke, in my mind too. It's the "distinction"
concept from G. Spencer Brown's book /The Laws of Form/. You had to be
there...

Ahh, gotcha.

How, in general, do you do "Test Driven Development" with Access? How, for
example, do you write a test which shows that the Delete button on a form
deletes the current record? (Yes, that's stupid. It's just an example.)

Next, how do you make that test run as fast and silent as possible, so you
can run it as often as possible?

Hint: Anything the user can do to a form, the test rig must do to. This
requires writing everything a form can do, _backwards_.

That, in turn, requires researching things nobody else researches or
documents about a system.

(I know I know, why bother writing tests at all when VBA has such a helpful
debugger. ;-)

I've been thinking about this same problem myself lately. In
particular due to my Auto FE Updater utility which has many thousands
of lines of VB code with many, many global variables and so on. But
not much in Access.

For example one person told me that because he didn't have the right
permissions set the Auto FE Updater silently failed while copying
files. So I will soon be putting in logic that ensures the file
size, date and time on the target files is the same as the source
files when done.

What I do is manually test things. And view the tables directly.
For example today I had to add some code to update a new field on an
unbound form. So I tried it using the three different paths the code
could take and viewed the record each time.

One trick when it comes to using the debugger is to put a breakpoint
on every line of code you want to test. Especially for compound
conditions. Test the various possibilities and once you're done there
shouldn't be any break points left.

Having met and chatted with Microsoft PMs, devs and testers over the
last few years they do have automated testing routines. But then
they things they test are far more complex. And testers who are
devious and twisted are prized. <smile> Testers also write some code
themselves as they create automated test suites.

One big difference is that for the most part we have one form and some
code behind that form. Other than tables we seldom have things which
interact with each other from one form to another.

So just be seeing if the data goes into and out of the tables properly
there's not much else to worry about.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Phlip]

Oh Please ... spare us. Before you start your monologue perhaps you'd better
have a look at Tony's posting credentials.

Get a grip, Holmes. I'm NOT gonna lecture him about _Access_!!!

 

[Phlip]

What I do is manually test things.

Okay. Call each little manual test an "experiment". Now imagine if you
encapsulated each experiment into a test case in a test rig.

If you run that over and over again, you can run it after each _tiny_ change
to source. The test rig will perform all those experiments, over and over
again.

One trick when it comes to using the debugger is to put a breakpoint
on every line of code you want to test.

That's test-last.

To do test-first, imagine writing a test case that fails because that line
is not there yet, then writing that line of code to pass the test.

If any test unexpectedly fails, you use Undo and back out your last change.
Because that change must be _tiny_, you don't have far to Undo before
everything works again. Then you try the edit again.

This technique covers every line of code with tests, like your trick using
the debugger to rate the coverage of _manual_ testing.

Except you have time to do it, because you never need to debug, because you
can always just hit Undo.

Having met and chatted with Microsoft PMs, devs and testers over the
last few years they do have automated testing routines. But then
they things they test are far more complex. And testers who are
devious and twisted are prized. <smile> Testers also write some code
themselves as they create automated test suites.

There's still a role for that kind of testage.

So just be seeing if the data goes into and out of the tables properly
there's not much else to worry about.

You are saying that it's only a little debugging so why worry about it.

The next benefit of TDD is you can change things much more aggressively. You
could, for example, refactor all your data tables, normalize them, rebalance
them, etc. If the tests still pass, the application is deliverable.

This is why I must ask sick questions here. If I word them poorly, MVPs
might respond with the manual intervention to avoid what I was trying to
automate. TDD for databases is hard, and for GUIs is exceptionally hard.

Access is both. That's why I'm exceptional. ;-)

 

[Tony Toews]

What I do is manually test things. And view the tables directly.

Another concept in very complex logic is to add an explanation of how
I arrived at a conclusion. For example pricing of a pipe weld is
dependant on weld diameter, material thickness, type of material, type
of weld and a few more I forget. The price book has about 40 pages
of spreadsheets covering various operations. Most of these pages are
for different operations but some of these are overrides, multipliers
and dividers.

So when I compute the price I stated what I based that price on in
English. Ok, programmers English but good enough for the invoicing
person to figure out.

Say the weld price is in 2" gradients for pipe diameter and 1/8" pipe
thickness gradients. But the diameter and thickness specified fall
in between the gradients at 13" and 5/16" wall thickness.. So I put
in "$153.70 based on 14" diameter and 3/8" thickness".

This helps me debug things as well as giving the user an explanation.

Another example just came across today is the price for a given
operation can come from any of three sources two of which if present
are overrides. So I put in the record id of the record which first
matched the criteria. Now when the user looks at this logic they can
see what record I choose to grab the price from.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Phlip]

Another concept in very complex logic is to add an explanation of how
I arrived at a conclusion. For example pricing of a pipe weld is
dependant on weld diameter, material thickness, type of material, type
of weld and a few more I forget. The price book has about 40 pages
of spreadsheets covering various operations. Most of these pages are
for different operations but some of these are overrides, multipliers
and dividers.

So when I compute the price I stated what I based that price on in
English. Ok, programmers English but good enough for the invoicing
person to figure out.

Say the weld price is in 2" gradients for pipe diameter and 1/8" pipe
thickness gradients. But the diameter and thickness specified fall
in between the gradients at 13" and 5/16" wall thickness.. So I put
in "$153.70 based on 14" diameter and 3/8" thickness".

This helps me debug things as well as giving the user an explanation.

Another example just came across today is the price for a given
operation can come from any of three sources two of which if present
are overrides. So I put in the record id of the record which first
matched the criteria. Now when the user looks at this logic they can
see what record I choose to grab the price from.

This is why the high-end of TDD is systems that build a table of inputs,
such as in Excel, like this:

_cost_ _diameter_ _thickness_ _comment_
$153.70 | 14" | 3/8" | okay
N/A | 15" | 3/8" | bad diameter
....

This forms the ultimate documentation, because you can add a Test button to
the corner of each document, and hit it to see if the code obeys all such
rules.

 

[Brendan Reynolds]

Access is an excellent development tool to use in many scenarios. But this
isn't one of them. I would not recommend investing a lot of time and effort
in getting Access to work in this scenario, because this is not the market
at which Access is aimed. It's entirely possible, therefore, that any
work-arounds you might come up with would be dependent on behaviour that may
change in future versions.

If test-driven development and automated testing are essential requirements
in your scenario, then my advice is to use a development tool that is
designed to support them.