Using an imported code module

[BruceM]

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?

 

[Marshall Barton]

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?

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)

 

[BruceM]

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)

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?

 

[Marshall Barton]

"Marshall Barton" wrote

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?

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)

 

[BruceM]

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)

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.

 

[Marshall Barton]

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)

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.

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.

 

[BruceM]

BruceM wrote:
"Marshall Barton" wrote
BruceM wrote:

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?


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)


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?


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)

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.

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.

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.

 

[Marshall Barton]

BruceM wrote:
"Marshall Barton" wrote
BruceM wrote:

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?


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)


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?


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)


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.

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.

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.

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

Keep up the good work.

 

[eos]

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