Macro RunCode Syntax for Referring to a Control on a Form

[jhcoxx]

Hi -

I'm trying to set up a function that will be used for several controls
on several forms - and will be called from an Access macro.

The function is in a general VBA module and I need to pass it a
reference to the form, the control (usually a listbox) and an integer.

The declaration of the function is

Public Function SetListBox(aForm As Variant, lbxBox As Variant, lItemNo
As Long)

and by using the following in the Function box of the RunCode action:

SetListBox([Form],[Forms]![frmAltOpStart]![lbxNonConIdx], 0)

the function sees the reference to the form and to the long integer,
but shows Null for the lbxBox variant. I've tried all the logical and
semi-logical syntax variations I can think of - with no success.

Any suggestions on the error (if not folly) of my ways - and any
suggestions on how to call this function from a subform on the form
would be most appreciated!

James Cox

 

[Douglas J. Steele]

Is the listbox set for multiselect? If so, you can't refer to it in that
manner.

 

[jhcoxx]

Doug -

Thanks for the swift reply.

Unfortunately, that doesn't seem to be the problem - the listbox
MultiSelect property is None.

I have to admit to being mystified by the various ways Access allows /
requires us to reference controls and forms.

Any other insights on this - and on the (probably) more complex issue
of calling this function from a subform? Just to clarify things, my
first problem was on a control (listbox) on the main form - I haven't
tried to work on the issue of passing a reference to a control on a
subform until I get the first one working...

James Cox

Is the listbox set for multiselect? If so, you can't refer to it in that
manner.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi -

I'm trying to set up a function that will be used for several controls
on several forms - and will be called from an Access macro.

The function is in a general VBA module and I need to pass it a
reference to the form, the control (usually a listbox) and an integer.

The declaration of the function is

Public Function SetListBox(aForm As Variant, lbxBox As Variant, lItemNo
As Long)

and by using the following in the Function box of the RunCode action:

SetListBox([Form],[Forms]![frmAltOpStart]![lbxNonConIdx], 0)

the function sees the reference to the form and to the long integer,
but shows Null for the lbxBox variant. I've tried all the logical and
semi-logical syntax variations I can think of - with no success.

Any suggestions on the error (if not folly) of my ways - and any
suggestions on how to call this function from a subform on the form
would be most appreciated!

James Cox

 

[Douglas J Steele]

It's a guess, but try changing lbxBox As Variant to lbxBox As Control

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug -

Thanks for the swift reply.

Unfortunately, that doesn't seem to be the problem - the listbox
MultiSelect property is None.

I have to admit to being mystified by the various ways Access allows /
requires us to reference controls and forms.

Any other insights on this - and on the (probably) more complex issue
of calling this function from a subform? Just to clarify things, my
first problem was on a control (listbox) on the main form - I haven't
tried to work on the issue of passing a reference to a control on a
subform until I get the first one working...

James Cox

Is the listbox set for multiselect? If so, you can't refer to it in that
manner.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi -

I'm trying to set up a function that will be used for several controls
on several forms - and will be called from an Access macro.

The function is in a general VBA module and I need to pass it a
reference to the form, the control (usually a listbox) and an integer.

The declaration of the function is

Public Function SetListBox(aForm As Variant, lbxBox As Variant, lItemNo
As Long)

and by using the following in the Function box of the RunCode action:

SetListBox([Form],[Forms]![frmAltOpStart]![lbxNonConIdx], 0)

the function sees the reference to the form and to the long integer,
but shows Null for the lbxBox variant. I've tried all the logical and
semi-logical syntax variations I can think of - with no success.

Any suggestions on the error (if not folly) of my ways - and any
suggestions on how to call this function from a subform on the form
would be most appreciated!

James Cox

 

[jhcoxx]

Doug -

Good insight and I'm sure a better programming practice - but
apparently not the problem.

I'm checking the value of lblBox by putting a breakpoint in the
function code and then hovering the cursor over the lblBox in

Public Function SetListBox(aForm As Variant, lblBox As Control, lItemNo
As Long)

The little yellow (Intellisense?) box that pops up has "lblBox = Null"
as its contents. Hovering over lItemNo gives "lItemNo = 0" and hovering
over aForm gives nothing, but typing

?aForm.name

in the Immediate Pane returns "frmAltOpStart" which is the parent
form's name.

All that makes me think the basic concept is correct but that I just
don't have the proper syntax to reference a control object on a form.
I'm sure hoping you have some more tricks up your sleeve on this!

James Cox

 

[jhcoxx]

Doug -

Good insight and I'm sure a better programming practice - but
apparently not the problem.

I'm checking the value of lblBox by putting a breakpoint in the
function code and then hovering the cursor over the lblBox in

Public Function SetListBox(aForm As Variant, lblBox As Control, lItemNo
As Long)

The little yellow (Intellisense?) box that pops up has "lblBox = Null"
as its contents. Hovering over lItemNo gives "lItemNo = 0" and hovering
over aForm gives nothing, but typing

?aForm.name

in the Immediate Pane returns "frmAltOpStart" which is the parent
form's name.

All that makes me think the basic concept is correct but that I just
don't have the proper syntax to reference a control object on a form.
I'm sure hoping you have some more tricks up your sleeve on this!

James Cox

 

[Douglas J Steele]

Looking at your call to the function, I'm confused:

SetListBox([Form],[Forms]![frmAltOpStart]![lbxNonConIdx], 0)

What is [Form] supposed to be?

Is frmAltOpStart open when you're making this call? (If you're making the
call from within frmAltOpStart, try Me![lbxNonConIdx] instead).

And you're absolutely positive that Multiselect is None?

 

[jhcoxx]

Doug -

The [Form] is functioning as a reference to the form that the listbox
control is on and actually seems to work.

When I tried the Me!syntax. ie

SetListBox([Form],Me![lbxNonConIdx] , 0)

it gave an error

"The object doesn't contain the Automation object 'Me'.

Could this be because of the syntax requirements for the RunCode macro
action?

Yes, this RunCode macro action is being run by clicking a command
button on the form that contains the listbox - the name of the macro is
specified in the Event | On Click entry (it's
mcrAltOpStart.mcrRunQuery, to be exact). That macro does run a query,
but after the call to the function is to occur. And yes, the listbox
really is set at Multi Select "None".

All in all, most strange behavior (it seems to me)

James Cox

Looking at your call to the function, I'm confused:

SetListBox([Form],[Forms]![frmAltOpStart]![lbxNonConIdx], 0)

What is [Form] supposed to be?

Is frmAltOpStart open when you're making this call? (If you're making the
call from within frmAltOpStart, try Me![lbxNonConIdx] instead).

And you're absolutely positive that Multiselect is None?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug -

Good insight and I'm sure a better programming practice - but
apparently not the problem.

I'm checking the value of lblBox by putting a breakpoint in the
function code and then hovering the cursor over the lblBox in

Public Function SetListBox(aForm As Variant, lblBox As Control, lItemNo
As Long)

The little yellow (Intellisense?) box that pops up has "lblBox = Null"
as its contents. Hovering over lItemNo gives "lItemNo = 0" and hovering
over aForm gives nothing, but typing

?aForm.name

in the Immediate Pane returns "frmAltOpStart" which is the parent
form's name.

All that makes me think the basic concept is correct but that I just
don't have the proper syntax to reference a control object on a form.
I'm sure hoping you have some more tricks up your sleeve on this!

James Cox

 

[Douglas J. Steele]

Somehow I missed that you're using a macro. Why? Use VBA to call the
function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug -

The [Form] is functioning as a reference to the form that the listbox
control is on and actually seems to work.

When I tried the Me!syntax. ie

SetListBox([Form],Me![lbxNonConIdx] , 0)

it gave an error

"The object doesn't contain the Automation object 'Me'.

Could this be because of the syntax requirements for the RunCode macro
action?

Yes, this RunCode macro action is being run by clicking a command
button on the form that contains the listbox - the name of the macro is
specified in the Event | On Click entry (it's
mcrAltOpStart.mcrRunQuery, to be exact). That macro does run a query,
but after the call to the function is to occur. And yes, the listbox
really is set at Multi Select "None".

All in all, most strange behavior (it seems to me)

James Cox

Looking at your call to the function, I'm confused:

SetListBox([Form],[Forms]![frmAltOpStart]![lbxNonConIdx], 0)

What is [Form] supposed to be?

Is frmAltOpStart open when you're making this call? (If you're making the
call from within frmAltOpStart, try Me![lbxNonConIdx] instead).

And you're absolutely positive that Multiselect is None?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug -

Good insight and I'm sure a better programming practice - but
apparently not the problem.

I'm checking the value of lblBox by putting a breakpoint in the
function code and then hovering the cursor over the lblBox in

Public Function SetListBox(aForm As Variant, lblBox As Control, lItemNo
As Long)

The little yellow (Intellisense?) box that pops up has "lblBox = Null"
as its contents. Hovering over lItemNo gives "lItemNo = 0" and hovering
over aForm gives nothing, but typing

?aForm.name

in the Immediate Pane returns "frmAltOpStart" which is the parent
form's name.

All that makes me think the basic concept is correct but that I just
don't have the proper syntax to reference a control object on a form.
I'm sure hoping you have some more tricks up your sleeve on this!

James Cox

Douglas J Steele wrote:
It's a guess, but try changing lbxBox As Variant to lbxBox As Control

 

[jhcoxx]

Doug -

I'm using the Macro RunCode action because of an issue of
maintainability and reuse. We have folks that are wizards at adding
controls and using macros but don't know enough VB / VBA to get into
macro writing. Hence my desire to let them use the RunCode action to
activate a function that I could write in VBA.

Given the potential of VB / VBA going to VSTO, this seems more
reasonable than hoping that they all will devote the time to developing
VB / VBA skills...

James Cox

 

[Albert D.Kallal]

Why don't you dump the use of the macro?

Why not just use code?