Help! Muddling Up Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I received the following instruction to have a page open up after a
particular value is selected in a ComboBox within a form:

In the AfterUpdate event procedure for the combo box, set visible property
as appropriate:

If Me!My ComboBox = CertainValue Then
Me!MySecondPage.Visible - True
End If

Q.1 When I go into the event procedure and click on the '...' I am in the
VB editor. I assume this is what is meant by 'set visible property' ?

Q.2 If I type in the above code I got an error when testing to say that
'MyComboBox' is not recognising. I then thought I should substitule 'Combo
Box' for the field name. In doing so it still did not recognise it.

Q.3 Could someone please explain the significance of 'Me!' and 'My!' in VB?

Thanks so much
 
I received the following instruction to have a page open up after a
particular value is selected in a ComboBox within a form:

In the AfterUpdate event procedure for the combo box, set visible property
as appropriate:

If Me!My ComboBox = CertainValue Then
Me!MySecondPage.Visible - True
Click to expand...

The - should be =; you're setting the Visible property of the control
named MySecondPage to True.
End If
Click to expand...
Q.1 When I go into the event procedure and click on the '...' I am in the
VB editor. I assume this is what is meant by 'set visible property' ?
Click to expand...

No. It's not. Clicking the ... puts you into the VBA editor, where you
can write any code you wish to write. The line I corrected above is
"setting the Visible property to true". You could have done all sorts
of other things in the code!
Q.2 If I type in the above code I got an error when testing to say that
'MyComboBox' is not recognising. I then thought I should substitule 'Combo
Box' for the field name. In doing so it still did not recognise it.
Click to expand...

Fields are fields, controls are controls. They are NOT THE SAME.
Fields exist in tables; Controls exist on forms. Typically the
"Control Source" property of a Control is the name of the Field to
which that control is bound.

It's the Name property of the control that you need to use here; this
might or might not be the same as the name of the table field to which
it is bound. Access (unwisely in my opinion) defaults to make them the
same. If I have a field named (say) State, and create a combo box
bound to it, I'll name the combo box cboState. This saves ambiguity.

Open your Form in design view; view the properties of the combo box in
question. The first line on the All tab is the "Name" property of the
combo. That's what you need after the Me! in Me!My ComboBox. And if
the name *is* in fact "My ComboBox", then you must enclose the name in
square brackets, since there is a blank in the name; VBA sees the
blank as the end of one term and the beginning of another, and will
not understand that Me!My and ComboBox are actually parts of
the same "thing". Make it

Me![My ComboBox]

or, better, don't use blanks or special characters in control names.
Q.3 Could someone please explain the significance of 'Me!' and 'My!' in VB?
Click to expand...

Me! means "the form that this code is attached to". It saves you from
having to type Forms!frmAddressMaintenance!cboState and lets you just
type Me!cboState instead; this also facilitates using the same code on
different forms.


John W. Vinson[MVP]
 
Setting the Visible property can be done in the object's property sheet
(double-click on it in Design View) or with code:

Sub Form_Current()

If Me.NewRecord = True Then
Me.cboVendorID.Visible = True
Else
If Len(Me.cboVendorID & vbNullString) > 0 Then
Me.cboVendorID.Visible = True
Else
Me.cboVendorID = False
End If
End If

End Sub

Me is the short way of referring to the current form or report. It takes the
place of: [Forms]![MyFormName]

My is just a prgrammers way of creating an object name placeholder. It has
no significance in VB or any other language. So:

Me.MyCombo should be:

Me.cboVendorID

or

[Forms]![MyFormName]![cboVendorID]
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Arvin,
Thanks for your response. I reponded to John's post re; my confusion about
setting the visible property. The confusion probably comes from my elementary
level in access. I'm finding these discussion pages extremely helpful however
and very much appreciate your taking the time to help out.

Cheers!

Arvin Meyer said:
Setting the Visible property can be done in the object's property sheet
(double-click on it in Design View) or with code:

Sub Form_Current()

If Me.NewRecord = True Then
Me.cboVendorID.Visible = True
Else
If Len(Me.cboVendorID & vbNullString) > 0 Then
Me.cboVendorID.Visible = True
Else
Me.cboVendorID = False
End If
End If

End Sub

Me is the short way of referring to the current form or report. It takes the
place of: [Forms]![MyFormName]

My is just a prgrammers way of creating an object name placeholder. It has
no significance in VB or any other language. So:

Me.MyCombo should be:

Me.cboVendorID

or

[Forms]![MyFormName]![cboVendorID]
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Angeline said:
I received the following instruction to have a page open up after a
particular value is selected in a ComboBox within a form:

In the AfterUpdate event procedure for the combo box, set visible property
as appropriate:

If Me!My ComboBox = CertainValue Then
Me!MySecondPage.Visible - True
End If

Q.1 When I go into the event procedure and click on the '...' I am in the
VB editor. I assume this is what is meant by 'set visible property' ?

Q.2 If I type in the above code I got an error when testing to say that
'MyComboBox' is not recognising. I then thought I should substitule 'Combo
Box' for the field name. In doing so it still did not recognise it.

Q.3 Could someone please explain the significance of 'Me!' and 'My!' in VB?

Thanks so much
Click to expand...
Click to expand...
 
John,
Thanks so much for your time on this response.

I'm just a bit confused though. I did use '=' apologise for the typo. So I'm
back to how to 'set visible property'. My instructions in my original post
were to do it in the even procedure in the afterupdate of the combobox. If I
go into the properties of the combo box, click into 'after update' and select
'event procedure', where to from there?

Also, what exactly does 'setting the visible property' actually do ?

Thanks Again for your time!

John Vinson said:
I received the following instruction to have a page open up after a
particular value is selected in a ComboBox within a form:

In the AfterUpdate event procedure for the combo box, set visible property
as appropriate:

If Me!My ComboBox = CertainValue Then
Me!MySecondPage.Visible - True
Click to expand...

The - should be =; you're setting the Visible property of the control
named MySecondPage to True.
End If
Click to expand...
Q.1 When I go into the event procedure and click on the '...' I am in the
VB editor. I assume this is what is meant by 'set visible property' ?
Click to expand...

No. It's not. Clicking the ... puts you into the VBA editor, where you
can write any code you wish to write. The line I corrected above is
"setting the Visible property to true". You could have done all sorts
of other things in the code!
Q.2 If I type in the above code I got an error when testing to say that
'MyComboBox' is not recognising. I then thought I should substitule 'Combo
Box' for the field name. In doing so it still did not recognise it.
Click to expand...

Fields are fields, controls are controls. They are NOT THE SAME.
Fields exist in tables; Controls exist on forms. Typically the
"Control Source" property of a Control is the name of the Field to
which that control is bound.

It's the Name property of the control that you need to use here; this
might or might not be the same as the name of the table field to which
it is bound. Access (unwisely in my opinion) defaults to make them the
same. If I have a field named (say) State, and create a combo box
bound to it, I'll name the combo box cboState. This saves ambiguity.

Open your Form in design view; view the properties of the combo box in
question. The first line on the All tab is the "Name" property of the
combo. That's what you need after the Me! in Me!My ComboBox. And if
the name *is* in fact "My ComboBox", then you must enclose the name in
square brackets, since there is a blank in the name; VBA sees the
blank as the end of one term and the beginning of another, and will
not understand that Me!My and ComboBox are actually parts of
the same "thing". Make it

Me![My ComboBox]

or, better, don't use blanks or special characters in control names.
Q.3 Could someone please explain the significance of 'Me!' and 'My!' in VB?
Click to expand...

Me! means "the form that this code is attached to". It saves you from
having to type Forms!frmAddressMaintenance!cboState and lets you just
type Me!cboState instead; this also facilitates using the same code on
different forms.


John W. Vinson[MVP]
Click to expand...
 
John,
Thanks so much for your time on this response.

I'm just a bit confused though. I did use '=' apologise for the typo. So I'm
back to how to 'set visible property'. My instructions in my original post
were to do it in the even procedure in the afterupdate of the combobox. If I
go into the properties of the combo box, click into 'after update' and select
'event procedure', where to from there?
Click to expand...

When you click the ... icon (and, if it asks, select the Code
Builder), Access will put you into the VBA editor window; it will
automatically fill in the Sub and End Sub lines of the code. Simply
edit the code to match what's posted in this thread, using your own
control names (which of course we don't know).

The line

Me!MySecondPage.Visible = True

*IS* setting the Visible property of the control to True.

Me! means "this form"
MySecondPage (after an !) means "the control named MySecondPage in
this object's default collection"; a Form has a collection of
Controls, which contains all of the textboxes, labels, pages, combos,
etc. that you've put on the form. Naming one of them in this syntax
simply selects it for use in the code.

..Visible refers to one of the (many) properties of the control you
have selected. The Visible property has two possible values - True
means "yes, this control is visible to the user"; False means "this
control will be invisible when the form is displayed to the user".

The control has lots of other properties; you can see what they are by
typing Ctrl-G to open the Immediate window (if it's not open already)
and typing, say,

?Me!cboMyCombo.Visible
or
?Me!cboMyCombo.ControlSource
or
?Me!cboMyCombo.RowSource

The ? tells the VBA editor to actually show you the value of whatever
you put after the ? - try it and see, using the name of a real control
on your form.
Also, what exactly does 'setting the visible property' actually do ?
Click to expand...

Makes the selected control visible if you set it to True, and makes it
invisible if you set it to False.


John W. Vinson[MVP]
John W. Vinson[MVP]
 
Okay, still having a problem. Here is the exact code I am using with the
correct control names:

Private Sub First_Reason_AfterUpdate()
If Me!My "First Reason" = "Manager" Then
Me!MySecondPage.Visible = True

End Sub

I've put the control name in "" because it has a space. Is that correct?

I'm sure the answer is simple and obvious but I think I'm having one of
those days. Thanks so much John for all your instruction it is very helpful.
 
Okay, still having a problem. Here is the exact code I am using with the
correct control names:

Private Sub First_Reason_AfterUpdate()
If Me!My "First Reason" = "Manager" Then
Me!MySecondPage.Visible = True

End Sub

I've put the control name in "" because it has a space. Is that correct?

I'm sure the answer is simple and obvious but I think I'm having one of
those days. Thanks so much John for all your instruction it is very helpful.
Click to expand...

Reread my previous message: That's not what I suggested. To quote:

============
And if
the name *is* in fact "My ComboBox", then you must enclose the name in
square brackets, since there is a blank in the name; VBA sees the
blank as the end of one term and the beginning of another, and will
not understand that Me!My and ComboBox are actually parts of
the same "thing". Make it

Me![My ComboBox]
=============

If the name of the control is

First Reason

then simply enclose the name of the control in square brackets, not
quote marks:

If Me![First Reason] = "Manager" Then

Sorry about the confusion with the word "My". There is no "My" in
Access syntax. Since I did not know what your combo box might be
named, I was using the name "My Combo" as a hypothetical example. If
the name you're using doesn't have the word My in it, then you don't
need to - and should not - follow my hypothetical example as if it
were your actual database!

John W. Vinson[MVP]
 
John,
I think I got it! The only thing is, I think this instruction is to make the
page tab visible so that you can click into it. This was always the case.
What I actually need is for the page to actually open up rather than just 'be
visible'.

The way I have the form up is with a series of 9 pages. The first page is
the main page and has the combo box. The combo box has 8 different values
each relevant to it's own page. I want the relevant page to open up when the
value is selected. The pages have all been expanded to almost the size of the
full form and at the top are the 9 tabs all with the new page names on them.

When I carried out these instructions, I realised that they were to make the
page visible. The reason I know this is because to check, I changed the code
to : Me!Page2.visible = false. I tested it and noticed that the page in
question was invisible. Or I should say the 'tab' then became invisible.

John, I'm learning heaps from you through this journey and I appreciate all
your posts very much.



John Vinson said:
Okay, still having a problem. Here is the exact code I am using with the
correct control names:

Private Sub First_Reason_AfterUpdate()
If Me!My "First Reason" = "Manager" Then
Me!MySecondPage.Visible = True

End Sub

I've put the control name in "" because it has a space. Is that correct?

I'm sure the answer is simple and obvious but I think I'm having one of
those days. Thanks so much John for all your instruction it is very helpful.
Click to expand...

Reread my previous message: That's not what I suggested. To quote:

============
And if
the name *is* in fact "My ComboBox", then you must enclose the name in
square brackets, since there is a blank in the name; VBA sees the
blank as the end of one term and the beginning of another, and will
not understand that Me!My and ComboBox are actually parts of
the same "thing". Make it

Me![My ComboBox]
=============

If the name of the control is

First Reason

then simply enclose the name of the control in square brackets, not
quote marks:

If Me![First Reason] = "Manager" Then

Sorry about the confusion with the word "My". There is no "My" in
Access syntax. Since I did not know what your combo box might be
named, I was using the name "My Combo" as a hypothetical example. If
the name you're using doesn't have the word My in it, then you don't
need to - and should not - follow my hypothetical example as if it
were your actual database!

John W. Vinson[MVP]
Click to expand...
 
John,
I think I got it! The only thing is, I think this instruction is to make the
page tab visible so that you can click into it. This was always the case.
What I actually need is for the page to actually open up rather than just 'be
visible'.
Click to expand...

Sorry! We were answering the question you asked, literally, instead of
answering the question you had in mind. Sometimes the jargon gets in
the way of clear communication!
The way I have the form up is with a series of 9 pages. The first page is
the main page and has the combo box. The combo box has 8 different values
each relevant to it's own page. I want the relevant page to open up when the
value is selected. The pages have all been expanded to almost the size of the
full form and at the top are the 9 tabs all with the new page names on them.
Click to expand...

Ummm... why the extra control? The user can open any tab page they
wish by simply clicking on the tab! How is it easier to click on a
combo box, find the row of the combo that's wanted, and then click a
second time to select it?

If you really want to do so, however, change

Me!MySecondPage.Visible = True

Instead, you need the Name property of the Tab control itself; and you
need to know (and, ideally, include as the combo box's bound column)
the sequential page number of each page. If the Tab Control is named
tbTheTab, then you can simply set its value to the page number you
want opened:

Me!tbTheTab = Me!ComboBoxName
When I carried out these instructions, I realised that they were to make the
page visible. The reason I know this is because to check, I changed the code
to : Me!Page2.visible = false. I tested it and noticed that the page in
question was invisible. Or I should say the 'tab' then became invisible.
Click to expand...

Exactly. "Visible" means just that: the tab page (or whatever other
control) is either visible or it is invisible. Clearly (sorry for the
pun) if it's invisible, then it cannot be selected.
John, I'm learning heaps from you through this journey and I appreciate all
your posts very much.
Click to expand...

Thanks!

John W. Vinson[MVP]
 
Back
Top