Simple 2 Questions - VBA v. Macros - Why Macros AND Best way to access current record.
Tester
These may be very simple questions, but I want to find the definite answers.
For an Access expert (or even novice perhaps), these should be a breeze.
1) I am a VB programmer new to Access 2000. From what I can see, there is no
need for me to even bother looking into Macro's. Is this correct? Is there
anything they offer over VBA code.
2) I have a form connected to a set of records of a DB table. I have made my
own navigation buttons on the form. What is the best way to access the
fields of the current record via VB code. When I used me.recordset!field (me
being the current form) I got some funny errors elsewhere in the program.
Basically, what is the best way to access the current record
programmatically.
Thanks in advance for any assistance,
Regards,
Bruce M. Thompson
be "Autokeys" and "AutoExec", both explained in Access Help and both of which I
use on occasion.
2) Try "Me![FieldName]".
--
Bruce M. Thompson, Microsoft Access MVP
bthm...@mvps.org (See the Access FAQ at http://www.mvps.org/access)
>>No Email, Please. Keep all communications
within the newsgroups so that all might benefit.<<
"Tester" <No Spam Please> wrote in message
news:3e15...@news1.homechoice.co.uk...
Albert D. Kallal
no
> need for me to even bother looking into Macro's. Is this correct? Is there
> anything they offer over VBA code.
There is no need, or real benefit to using macros. I will also stress that
this is NOT a issue of Macros are for soft noodle brained developers. Again,
this is not a macho thing were we look down on Macros. Macros can certainly
can help a person who has no idea about programming. There are AMAZING tool
that MS added to ms-access. There are MANY applications developed by people
JUST using macros. I think this a great idea.
However, they really to be avoided by anyone who has programming skills. So,
yes..avoid macros (there is no reason to use them). Again, I want to stress
that this is not a issue of "easy" Vs cool, or easy Vs. hard. I use the
combo wizard all the time, it is both easy...and the results are fantastic.
So, I freely admit to using the wizards to create combo boxes for me..but I
will not touch macros.
>
> 2) I have a form connected to a set of records of a DB table. I have made
my
> own navigation buttons on the form. What is the best way to access the
> fields of the current record via VB code. When I used me.recordset!field
(me
> being the current form) I got some funny errors elsewhere in the program.
> Basically, what is the best way to access the current record
> programmatically.
Ah, good question! the keyword "me" refers to the current instance of the
form (this is important, since we can have multiple instances of THE SAME
FORM opened). Note that our forms in ms-access are bound, and thus any field
in the underlying table (or query) can be referenced as follows:
me.TheFieldName
The above is commonly used, since inteli-sense does kick in. However, the
correct syntax is really:
me!TheFieldName
You want reference the data that way. You can use me.RecordSet!FieldName,
but then you tend to fight between the reocordset and the form.
Of course if the code is running OUT side of the current instance of the
form, then the general reference format is:
forms!ForName!FieldName
The "bang" operator is generally assumed to be for your collections, and the
".dot" is usually reserved for built in properties and methods. However, all
fields do fact popup and can be referenced via the "dot" prompt. I would not
fell too shy about using the "dot" notation, but the "dot" notation does not
work for forms were the field is NOT on the form *and* you change the
recordsouce via code (which does not happen too often).
Note that the field name does NOT actually have to be on the screen. Thus,
you want to be careful, since most of the time a control on your screen has
the SAME NAME as the underlying field in the table/query.
It will take some time for you to get used to a bound form. They are cool,
and can do a lot of things. You also want to be aware of the concept of a
sub-form.
You can read my article on sub forms at:
http://www.attcanada.net/%7ekallal.msn/Articles/fog0000000005.html
Also, the ms-access ten commands is good read also. (if you have any
questions about the ten commands...feel free to ask !
The ten commandments:
http://www.mvps.org/access/tencommandments.htm
--
Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com
www.attcanada.net/~kallal.msn
Tester
"Albert D. Kallal" <kal...@msn.com> wrote in message
news:GymR9.346491$ka.89...@news1.calgary.shaw.ca...
>
> You can read my article on sub forms at:
>
> http://www.attcanada.net/%7ekallal.msn/Articles/fog0000000005.html
>
> Also, the ms-access ten commands is good read also. (if you have any
> questions about the ten commands...feel free to ask !
>
> The ten commandments:
>
> http://www.mvps.org/access/tencommandments.htm
> --
here. With regard to your state of the Access DB contracting/general market,
do you think the Access market is likely to go up or down, RELATIVE to the
general IT market. As someone who is crash coursing Access as we speak, it
would be nice to know that I could use my skills again for the odd side
contract here and there.
With regard to sub forms, you obviously like them. Now having developed my
first form in access, I hid everything Access from the user and developed my
own set of navigational controls, along with insert, edit and delete buttons
(To make it look like more of an application for the novice user). Was not
as easy to do as I thought coming from a VB background because I was
thinking about things differently, came across some limitations, new object
models to learn and lack of good books that just give you the facts (instead
they are mostly very large, beginner targeted books, which spend a long time
explaining some simple concepts).
Anyway, what do you think of placing all my navigation controls into a form,
and then using that as a sub form on any form that I want to add simple user
navigation too.
I have not tried it yet, but I am assuming it would be quite straight
forward to write the code for the control buttons to be generic so that they
work on recordset objects associate with an arbitrary parent form. This
navigation sub control could even have an onload event, that sets any
necessary parameters/properties of the parent form that need to be set to
make the navigation sub form work well (like setting the allowedits property
to false).
This seems like it would be straight forward, but as I have found, Access is
not VB and I miss problems. (Also I have only be doing Access on off, just
over a week). Does anyone foresee any problems with what I have suggested.
Thanks in advance.
Rick Brandt
I wrote a generic navigation subform a while back. You can simply refer to
Me.Parent for the recordsetclone properties so yes, you just drop it on any
form and it works with no additional changes.
Albert D. Kallal
more of a short cut. However, don't let that fool you. Much of the
productivity increase of ms-access over VB is due to the fact that we follow
a "STANDARD" approach to most forms. (thus, we don't write our own
navigation buttons, and thus we save that time!!). Hence, you tend to see a
lot more "cool" in VB. In ms-access if you stick to basic forms, then you
don't need as much code. So, a big part of the "speed" of development in
access is due to this standard approach.
Also, while most ms-access developers just use the built-in navigation
buttons, a good many of us actually avoid them. In fact, this means we don't
have ANY navigation buttons! You will find that software for client/server
is also written this way (ie: you type in a part number, and then a form
loads).
In general, a good design will ONLY load up a form with one record. There
are many reason for this:
One good reason is that you reduce network traffic to min, since only ONE
record is loaded into the form. You can get VERY high performance when you
do this, even on tables with 200,000 records. A form load can be instant if
you use a where clause. When done, the user exits the form, and the data is
automatically saved for you.
This approach also forces the user to work with one "thing" at a time. Most
peoples brain prefers this!! They edit and then are done. Now, on to the
next task!
One of the great things about ms-access forms is the "where" clause. You can
open a form, and go to a record with ONE line of code.
docmd.OpenForm "theForm",,,"id = 123"
For example, the line of code below is used from a continues form to open up
the "edit" form with ONE record (in this case I do recommend navigation
buttons be turned on in the continues form. However, I am talking about the
built in navigation buttons, and that is only in a continues form that looks
like a data grid). That one line of code is:
docmd.OpenForm "theForm",,,"id = " & me.id
The above is actually the one line code I use to pop open the form in the
following example search screen. Take a quick look at the screen shots here:
http://www.attcanada.net/~kallal.msn/Search/index.html
Gee, one line of code to open a form to particular record. It is stuff like
the above that makes ms-access so incredible.
Since you do have VB skills, then you are way ahead in the game of learning
ms-access.
>
> I hid everything Access from the user and developed my
> own set of navigational controls, along with insert, edit and delete
buttons
> (To make it look like more of an application for the novice user). Was not
> as easy to do as I thought coming from a VB background because I was
> thinking about things differently, came across some limitations, new
object
> models to learn and lack of good books that just give you the facts
(instead
> they are mostly very large, beginner targeted books, which spend a long
time
> explaining some simple concepts).
I will say that the fact that you mention the above shows how you are made
for computers! You seem to instantly understand that things are not bad..but
they are different! This is a sign a good developer! I also don't need or
want 100 pages showing me stuff like how to use a mouse to moving something
on a screen. We all know that basic stuff!!
You need 100% beef here!!
>
> Anyway, what do you think of placing all my navigation controls into a
form,
> and then using that as a sub form on any form that I want to add simple
user
> navigation too.
This is exactly what Rick also is suggesting. You can do this. Note that
code I the sub-form can reference the parent form as follows:
me.Parent."some thing"
If you let the wizard build the navigation button for you (do give the
wizard a try). The wizard will build you some navigation buttons. The code
the wizard makes is:
DoCmd.GoToRecord , , acNext
However, the wizard does not know you going to be doing this from a sub
form, thus you can edit the above code to :
DoCmd.GoToRecord acDataForm, Me.Parent.Name, acNext
Thus, the button code will now operate on the parent form, but exist in the
sub-form. That means your navigation "sub form" bar can be re-used in each
screen.
Also, I have a bunch more tips in the following article of mine:
(don't let the part about you having to know Pick discourage you, you will
get some great ideas from the following:
http://www.attcanada.net/%7ekallal.msn/Articles/fog0000000003.html
As for the job market..my thoughts on ms-access can be found at:
http://www.attcanada.net/%7ekallal.msn/Articles/fog0000000004.html
David W. Fenton
<3e162732$1...@news1.homechoice.co.uk>:
>I have not tried it yet, but I am assuming it would be quite
>straight forward to write the code for the control buttons to be
>generic so that they work on recordset objects associate with an
>arbitrary parent form.
You would then refer in the subform control's code to Me.Parent,
which is a reference to the form containing the instance of the
subform.
But you won't be doing things with the recordset of the form, in
any case. The recordset behind a form and the form buffer that is
displayed are not always the same (and there's the RecordsetClone,
too), and so it's better to not do things to the recordset of the
form.
For saving a record, Me.Dirty = False, for undoing a control edit,
Me!ControlName.Undo, for undoing the record edit, Me.Undo, for
navigation between records, use Me.RecordsetClone and bookmarks.
Another easy approach to custom navigation buttons is to use the
ones from the Access Developer's Handbook, which use code in a
public module and functions in the events of the buttons. All you
have to do is paste the navigation controls onto your form and they
simply work, without any need for code in the form itself except
for one line of code that initializes the buttons.
But a subform should work well, too. Just be aware that in certain
circumstances that are not too clear, Access 2K loses track of
what's a subform control and what's not. That is, I've found
references to subforms and parent forms of subforms to be
substantially less reliable in A2K than in A97 (and all previous
versions of Access, too).
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Stephen Lebans
http://www.lebans.com/recnavbuttons.htm
RecordNavigationButtons is an MDB containing code to replace the
standard Navigation Buttons. The custom buttons exactly emulate the
standard navigation bar including the autorepeat property.
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"Tester" <No Spam Please> wrote in message
news:3e162732$1...@news1.homechoice.co.uk...
>
Gary Rockley
in this regard, a standard caveats for AutoExec and AutoKey macros in
a secured db should be
1. Never use them
2. Create blank ones that do nothing and can not be modified.
:-)
gary
Brendan Reynolds
news:GymR9.346491$ka.89...@news1.calgary.shaw.ca...
> me.TheFieldName
>
> The above is commonly used, since inteli-sense does kick in. However, the
> correct syntax is really:
>
> me!TheFieldName
To get InteliSense with the bang syntax, press Ctrl after typing the !
Me!<press Ctrl now>
It's not exactly the same list you get with the dot syntax, but can still be
useful.
--
Brendan Reynolds
bren...@indigo.ie
Scott Gilbert
> no
> > need for me to even bother looking into Macro's. Is this correct? Is there
> > anything they offer over VBA code.
>
> There is no need, or real benefit to using macros.
Minor nitpick, I have found that when creating custom menus, I have
been unable to point the menu items directly at code. So I point the
menu item at a macro which calls the code.
Unless I'm mistake in believing that custom menu items cannot point
directly at code, I think that macros are necessary. But only in this
particular instance.
[snip]
Rick Brandt
news:e4946d4b.0301...@posting.google.com...
They can point at VBA code written as functions instead of sub-routines.
Just change your subs to functions and use =MyFunction()
Lyle Fairfield
news:e4946d4b.0301...@posting.google.com:
I regularly "point" menu items directly to code in Ac97, 2K and XP. In the
On Action box of the dialog I enter the name of the (Public) procedure I
wish to call. No parentheses are necessary.
I have not used a macro for many years.
--
Lyle
Bruce M. Thompson
news:3E17D7DB...@attbi.com...
> Bruce,
>
> in this regard, a standard caveats for AutoExec and AutoKey macros in
> a secured db should be
>
> 1. Never use them
I tend to agree with you, but I prefer to avoid the use of the work "never",
especially since I still support Access 2.0. <g>
> 2. Create blank ones that do nothing ...
That is generally my usage (AutoKeys) regardless of whether the mdb is secured
or not.