Using an imported code module

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BruceM - 21 Jun 2006 20:57 GMT

I use some controls and code, such as custom navigation buttons and
associated code, over and over. I have seen the recommendation to use a
standard module for such things, but am not sure I am implementing it
properly. To use a simple example, the code to go to the next record
(command button Click event) is:

DoCmd.GoToRecord , , acNext

That line of code is in a public function named TheNextRecord in the code
module. In the command button's Click event line I put:
=TheNextRecord()

That works, but I don't know if it is the best way to do this. There is
error handling in the functions.

Where this doesn't work is in my code for custom navigation buttons. In the
form's Current event I have (in part):

Dim strCurrent As String
strCurrent = Me.CurrentRecord

This is the beginning of code to put the current and total records into a
text box (e.g. Record 1 of 3). However, I cannot find a convenient way of
calling something in a standard module that will accomplish the same thing.
If I do so it trips over the Me in Me.Current, which is understandable since
Current isn't a property of the standard module (or something like that).
Can I handle this in a standard module that I could call from different
forms, or do I need to copy and past the code to the current event of
various forms?

Reply to this Message

Marshall Barton - 21 Jun 2006 23:26 GMT

>I use some controls and code, such as custom navigation buttons and
>associated code, over and over. I have seen the recommendation to use a
[quoted text clipped - 25 lines]
>forms, or do I need to copy and past the code to the current event of
>various forms?

You can put this in a standard module function. The less
than 100% reliable way is for the function to use the
Screen.ActiveForm object instead of Me. The reason I don't
like to rely on the Screen object is that it can change out
from under you if the user clicks randomly around the screen
or if you have another form running its Timer event.

I think the simplest alternative is for the function to have
an argument for the form object:
Public Function SetCurrentRecordNumber(frm As Form)

and then call it this way:
SetCurrentRecordNumber(Me)
or directly from an event property:
SetCurrentRecordNumber(Form)

Signature

Marsh
MVP [MS Access]

Reply to this Message

BruceM - 22 Jun 2006 13:06 GMT

>>I use some controls and code, such as custom navigation buttons and
>>associated code, over and over. I have seen the recommendation to use a
[quoted text clipped - 44 lines]
> or directly from an event property:
> SetCurrentRecordNumber(Form)

Thanks for the reply. As the current event (VBA Procedure) of the subform,
the code I have works as intended for identifying the record number and
activating the navigation buttons as needed (for instance, at the first
record the Previous button is disabled). I have copied that code as a
public function, named as you have suggested with frm as Form in
parentheses.
I went to the subform's current event, and tried different combinations
directly on the Current line in the property sheet, which is what I think
you mean by "directly from an event property". As I mentioned earlier, when
I called the function TheNextRecord I did so thus in the command button's
Click event line:
=TheNextRecord()
This worked as intended, but the same syntax did not work for
SetCurrentRecordNumber. I tried it with the = sign, but when I added (Form)
after the function name Access put brackets around it, then had the problem
with the Me in Me.CurrentRecord as I described in my original post (invalid
use of Me keyword). I also tried it with the name of the form, but again
the brackets and the error message. I also tried it with (frm as Form)
exactly as it appears in the function itself, but that was invalid syntax.
I also tried every combination I could think of, but without the = sign.
However, that led to an error message that Access couldn't find the macro
named whatever I put on that line.
What am I missing? Does it make any difference that this is the subform's
Current event rather than a main form's current event?

Reply to this Message

Marshall Barton - 22 Jun 2006 16:12 GMT

>> BruceM wrote:
>>
[quoted text clipped - 71 lines]
>What am I missing? Does it make any difference that this is the subform's
>Current event rather than a main form's current event?

It sounds like you forgot to change the function's use of Me
to the frm argument, which was the wole purpose of adding
the argument in the first place.

Then function in the standard module will look like:

Public Function SetCurrentRecordNumber(frm As Form)
        . . .
    something = frm.CurrentRecord
        . . .
End Function

The button's OnClick property would then be:

    =SetCurrentRecordNumber(Form)

Signature

Marsh
MVP [MS Access]

Reply to this Message

BruceM - 22 Jun 2006 18:05 GMT

>>> BruceM wrote:
>>>
[quoted text clipped - 96 lines]
>
> =SetCurrentRecordNumber(Form)

Ah! I see. It wasn't that I forgot, but rather that I didn't understand
what I needed to do. I'm starting to see what to do with the parentheses
after the sub or function name. It works as it should now, and I have saved
hours on future projects. Thanks so much.
By the way, when I entered:

=SetCurrentRecordNumber(Form)

Access changed it to:

=SetCurrentRecordNumber([Form])

Is that the expected behavior, or is something amiss? I'm using Access
2000, and I entered that line into the subform's Current event property on
the property sheet.

Reply to this Message

Marshall Barton - 22 Jun 2006 19:06 GMT

>> BruceM wrote:
>>>> BruceM wrote:
[quoted text clipped - 113 lines]
>2000, and I entered that line into the subform's Current event property on
>the property sheet.

The addition of brackets in control source expressions
(and/or parenthesis in other circumstances) is a (usually?)
harmless habit that Access uses (needed or not) to make sure
names are in a formally correct syntax. The rule for square
brackets is that any name that includes any characters
except alphnumeric or underscorre or starts with a numeric
digit, must be enclosed in [ ]. Access's simple minded
approach to this rule is to make sure everything is
enclosed. While this is normally harmless, you should
double check to make sure that the [ ] don't enclose more
than a single name.

Signature

Marsh
MVP [MS Access]

Reply to this Message

BruceM - 22 Jun 2006 19:28 GMT

>>> BruceM wrote:
>>>>> BruceM wrote:
[quoted text clipped - 138 lines]
> double check to make sure that the [ ] don't enclose more
> than a single name.

Thanks again. I use only alphanumeric characters and underscores, so I
don't give the brackets much thought except to wonder from time to time why
they are there.

Reply to this Message

Marshall Barton - 22 Jun 2006 21:28 GMT

>>>> BruceM wrote:
>>>>>> BruceM wrote:
[quoted text clipped - 142 lines]
>don't give the brackets much thought except to wonder from time to time why
>they are there.

You are following the Best Practice recommended by almost
every professional.

Keep up the good work.

Signature

Marsh
MVP [MS Access]

Reply to this Message

eos - 27 Jun 2006 14:51 GMT

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

Reply to this Message