Hide field 2 if filed 1 is null

  • Thread starter Thread starter scottyboyb
  • Start date Start date
S

scottyboyb

Hello, anyone want to tell a very newbie code boy why this is not working?

Private Sub BoardMember_AfterUpdate()
If IsNull(Me.BoardMember) Then
Me.Position.Visible = False
Else
Me.Position.Visible = True
End If
End Sub

where BoardMember is a Y/N (checkbox) field on the form from table
Contributors and Position is a text field on the same form from the same
table. The form is a data entry form. The Data Entry field in the form's data
tab is set to Yes. Popup and Modal are also set to Yes on the Other Tab.

I currently have this event set in the after update line of the events tab.
Where should it go?

Thanks,
Scott
 
By "not working" do you mean it always stays visible? ... or invisible?

If this were mine, I'd add in a breakpoint to that code so I could inspect
the value of the control as the code runs. ... and I'm guessing that the
control is named [BoardMember], EXACTLY the same as the underlying field.
This might cause problems .. what happens if you name the control something
else (e.g., chkBoardMember) and modify your code to reflect that?

Are you quite certain that the underlying field is a yes/no data type field,
and is only "yes" or "no" (true/false)?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
OK #1. I mean it stays visible. Which I guess is good if you are going to
have this problem, at least you can use the field.

OK #2. I do not understand breakpoints. I found them on the Debug menu, but
I do not know how to use them. The VBA Help is less than it's name. I told
you I was very newbie coder. So I was exagerating my abilities a little.

The control was named BoardMember. Now it is named chkBoardMember. Here is
new code:
Private Sub Form_Current()
If IsNull(Me.chkBoardMember) Then
Me.Position.Visible = False
Else
Me.Position.Visible = True
End If
End Sub

Private Sub chkBoardMember_AfterUpdate()
If IsNull(Me.chkBoardMember) Then
Me.Position.Visible = False
Else
Me.Position.Visible = True
End If
End Sub

I double checked the table design and the BoardMember field is a y/n field
and the position field is a text field.

I double checked the events to make sure they were pointing to the right
procedures and they are.

Any thoughts?

Best,
Scott

Jeff Boyce said:
By "not working" do you mean it always stays visible? ... or invisible?

If this were mine, I'd add in a breakpoint to that code so I could inspect
the value of the control as the code runs. ... and I'm guessing that the
control is named [BoardMember], EXACTLY the same as the underlying field.
This might cause problems .. what happens if you name the control something
else (e.g., chkBoardMember) and modify your code to reflect that?

Are you quite certain that the underlying field is a yes/no data type field,
and is only "yes" or "no" (true/false)?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


scottyboyb said:
Hello, anyone want to tell a very newbie code boy why this is not working?

Private Sub BoardMember_AfterUpdate()
If IsNull(Me.BoardMember) Then
Me.Position.Visible = False
Else
Me.Position.Visible = True
End If
End Sub

where BoardMember is a Y/N (checkbox) field on the form from table
Contributors and Position is a text field on the same form from the same
table. The form is a data entry form. The Data Entry field in the form's
data
tab is set to Yes. Popup and Modal are also set to Yes on the Other Tab.

I currently have this event set in the after update line of the events
tab.
Where should it go?

Thanks,
Scott
Click to expand...


.
Click to expand...
 
Also, just had (brilliant?) thought. Field position is named same in form. I
changed it to txtPosition. No joy. Here is new, new code:
Private Sub Form_Current()
If IsNull(Me.chkBoardMember) Then
Me.txtPosition.Visible = False
Else
Me.txtPosition.Visible = True
End If
End Sub

Private Sub chkBoardMember_AfterUpdate()
If IsNull(Me.chkBoardMember) Then
Me.txtPosition.Visible = False
Else
Me.txtPosition.Visible = True
End If
End Sub

Than
By "not working" do you mean it always stays visible? ... or invisible?

If this were mine, I'd add in a breakpoint to that code so I could inspect
the value of the control as the code runs. ... and I'm guessing that the
control is named [BoardMember], EXACTLY the same as the underlying field.
This might cause problems .. what happens if you name the control something
else (e.g., chkBoardMember) and modify your code to reflect that?

Are you quite certain that the underlying field is a yes/no data type field,
and is only "yes" or "no" (true/false)?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


scottyboyb said:
Hello, anyone want to tell a very newbie code boy why this is not working?

Private Sub BoardMember_AfterUpdate()
If IsNull(Me.BoardMember) Then
Me.Position.Visible = False
Else
Me.Position.Visible = True
End If
End Sub

where BoardMember is a Y/N (checkbox) field on the form from table
Contributors and Position is a text field on the same form from the same
table. The form is a data entry form. The Data Entry field in the form's
data
tab is set to Yes. Popup and Modal are also set to Yes on the Other Tab.

I currently have this event set in the after update line of the events
tab.
Where should it go?

Thanks,
Scott
Click to expand...


.
Click to expand...
 
Just had (brilliant?) thought? Checked and field Positionisnamed same in form
like BoardMember. I changed it to txtPosition. No Joy. HEre is new, new code:
Private Sub Form_Current()
If IsNull(Me.chkBoardMember) Then
Me.txtPosition.Visible = False
Else
Me.txtPosition.Visible = True
End If
End Sub

Private Sub chkBoardMember_AfterUpdate()
If IsNull(Me.chkBoardMember) Then
Me.txtPosition.Visible = False
Else
Me.txtPosition.Visible = True
End If
End Sub

Thanks,
Scott

Jeff Boyce said:
By "not working" do you mean it always stays visible? ... or invisible?

If this were mine, I'd add in a breakpoint to that code so I could inspect
the value of the control as the code runs. ... and I'm guessing that the
control is named [BoardMember], EXACTLY the same as the underlying field.
This might cause problems .. what happens if you name the control something
else (e.g., chkBoardMember) and modify your code to reflect that?

Are you quite certain that the underlying field is a yes/no data type field,
and is only "yes" or "no" (true/false)?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


scottyboyb said:
Hello, anyone want to tell a very newbie code boy why this is not working?

Private Sub BoardMember_AfterUpdate()
If IsNull(Me.BoardMember) Then
Me.Position.Visible = False
Else
Me.Position.Visible = True
End If
End Sub

where BoardMember is a Y/N (checkbox) field on the form from table
Contributors and Position is a text field on the same form from the same
table. The form is a data entry form. The Data Entry field in the form's
data
tab is set to Yes. Popup and Modal are also set to Yes on the Other Tab.

I currently have this event set in the after update line of the events
tab.
Where should it go?

Thanks,
Scott
Click to expand...


.
Click to expand...
 
Aha! You are testing for whether the checkbox is null... but wouldn't it
always be either Yes or No?!

Test for that instead, with something like:

If Me!chkBoardMember = True Then

Else

End if


Here are a few other ideas...

Try replacing "Me.xxxx" with "Me!xxxx" in those procedures.

Open a code module window (VBA).

Highlight a line of code (your first "If IsNull(...)"). In the menu, I
believe under Debug, click on the command that sets a Breakpoint.

Now, close/save the module window and run the form. When the code fires, it
should jump to the code window, stopped at the breakpoint.

Try searching on-line for "breakpoint" and "stepping through code" for more
ideas.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.




scottyboyb said:
Just had (brilliant?) thought? Checked and field Positionisnamed same in
form
like BoardMember. I changed it to txtPosition. No Joy. HEre is new, new
code:
Private Sub Form_Current()
If IsNull(Me.chkBoardMember) Then
Me.txtPosition.Visible = False
Else
Me.txtPosition.Visible = True
End If
End Sub

Private Sub chkBoardMember_AfterUpdate()
If IsNull(Me.chkBoardMember) Then
Me.txtPosition.Visible = False
Else
Me.txtPosition.Visible = True
End If
End Sub

Thanks,
Scott

Jeff Boyce said:
By "not working" do you mean it always stays visible? ... or invisible?

If this were mine, I'd add in a breakpoint to that code so I could
inspect
the value of the control as the code runs. ... and I'm guessing that the
control is named [BoardMember], EXACTLY the same as the underlying field.
This might cause problems .. what happens if you name the control
something
else (e.g., chkBoardMember) and modify your code to reflect that?

Are you quite certain that the underlying field is a yes/no data type
field,
and is only "yes" or "no" (true/false)?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


scottyboyb said:
Hello, anyone want to tell a very newbie code boy why this is not
working?

Private Sub BoardMember_AfterUpdate()
If IsNull(Me.BoardMember) Then
Me.Position.Visible = False
Else
Me.Position.Visible = True
End If
End Sub

where BoardMember is a Y/N (checkbox) field on the form from table
Contributors and Position is a text field on the same form from the
same
table. The form is a data entry form. The Data Entry field in the
form's
data
tab is set to Yes. Popup and Modal are also set to Yes on the Other
Tab.

I currently have this event set in the after update line of the events
tab.
Where should it go?

Thanks,
Scott
Click to expand...


.
Click to expand...
Click to expand...
 
Open a code module window (VBA).

Highlight a line of code (your first "If IsNull(...)"). In the menu, I
believe under Debug, click on the command that sets a Breakpoint.
Click to expand...

or just mouseclick in the vertical grey bar to the left of the code window. A
rust-red big dot will appear indicating the location of the breakpoint.

You can use Debug... on the menu to step through the code in various ways;
most of these have F-key equivalents, e.g. F8 means "execute one line of
code".
 
You, sir are a genius.

Thank you very much it woirks like a charm! And now I understand breakpoints
a little better. I did not know I went back into the app to test. That works
much better. I will experiment some more.

Best,
Scott


Jeff Boyce said:
Aha! You are testing for whether the checkbox is null... but wouldn't it
always be either Yes or No?!

Test for that instead, with something like:

If Me!chkBoardMember = True Then

Else

End if


Here are a few other ideas...

Try replacing "Me.xxxx" with "Me!xxxx" in those procedures.

Open a code module window (VBA).

Highlight a line of code (your first "If IsNull(...)"). In the menu, I
believe under Debug, click on the command that sets a Breakpoint.

Now, close/save the module window and run the form. When the code fires, it
should jump to the code window, stopped at the breakpoint.

Try searching on-line for "breakpoint" and "stepping through code" for more
ideas.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.




scottyboyb said:
Just had (brilliant?) thought? Checked and field Positionisnamed same in
form
like BoardMember. I changed it to txtPosition. No Joy. HEre is new, new
code:
Private Sub Form_Current()
If IsNull(Me.chkBoardMember) Then
Me.txtPosition.Visible = False
Else
Me.txtPosition.Visible = True
End If
End Sub

Private Sub chkBoardMember_AfterUpdate()
If IsNull(Me.chkBoardMember) Then
Me.txtPosition.Visible = False
Else
Me.txtPosition.Visible = True
End If
End Sub

Thanks,
Scott

Jeff Boyce said:
By "not working" do you mean it always stays visible? ... or invisible?

If this were mine, I'd add in a breakpoint to that code so I could
inspect
the value of the control as the code runs. ... and I'm guessing that the
control is named [BoardMember], EXACTLY the same as the underlying field.
This might cause problems .. what happens if you name the control
something
else (e.g., chkBoardMember) and modify your code to reflect that?

Are you quite certain that the underlying field is a yes/no data type
field,
and is only "yes" or "no" (true/false)?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


Hello, anyone want to tell a very newbie code boy why this is not
working?

Private Sub BoardMember_AfterUpdate()
If IsNull(Me.BoardMember) Then
Me.Position.Visible = False
Else
Me.Position.Visible = True
End If
End Sub

where BoardMember is a Y/N (checkbox) field on the form from table
Contributors and Position is a text field on the same form from the
same
table. The form is a data entry form. The Data Entry field in the
form's
data
tab is set to Yes. Popup and Modal are also set to Yes on the Other
Tab.

I currently have this event set in the after update line of the events
tab.
Where should it go?

Thanks,
Scott


.
Click to expand...
Click to expand...


.
Click to expand...
 
I'm playing with this now and it is starting to make sense. Thanks for all
the help on this.

I am on a roll with learning about code so I have a couple more ideas of
things I could do. But I have question on Form_Current. Can I hae more than 1
instance of Form_Current in a form's code? For instance, one control has an
event set on the form's On_Current. Now say I want to have another control
that fires on the On_Current event. Can this be done or do I somehow have to
combine the code together?

Thanks,
Scott
 
I'm thinking not as I am getting anerror message saing I have an ambiguous
name. So what does a poor boy do?

Thanks,
Scott
 
I am on a roll with learning about code so I have a couple more ideas of
things I could do. But I have question on Form_Current. Can I hae more than 1
instance of Form_Current in a form's code? For instance, one control has an
event set on the form's On_Current. Now say I want to have another control
that fires on the On_Current event. Can this be done or do I somehow have to
combine the code together?
Click to expand...

Controls don't "fire". I really have no idea what you're asking! What do you
want to happen when you move to a new record?

Note that the code in a Form's Current event can do many things. If you think
a procedure is limited to one line of code... well, it's not.
 
I'm thinking not as I am getting anerror message saing I have an ambiguous
name. So what does a poor boy do?
Click to expand...

Correct the error in the code (probably an ambiguous name, i.e. a name which
Access can't resolve - e.g. if you have two tables both with a field named
UserID and your code refers to "UserID", Access can't tell which table's
UserID you mean).

If you would like help with the code... post it so we can see it.
 
I'm sorry my terminology is poor. If you have two distinct things you want to
accomplish (say, make a field visible or invisible based on a cjheck box
being checked and also another group of fields visible or invisible based on
a checkbox in an option group being checked) and you wanted them both to (no
word here) happen on the On_Current event of the form, can yu put them bothin
the same Private Sub Form_Current() ... End Sub? I hoppe that is clearer.

Aso, would this hide a text field unless a checkbox is checked? If not what
is the syntax?
Private Sub Check25_GotFocus()
Me!txtCardholdersName.Visible = Me.Check25
End Sub

Private Sub Form_Current()
If Me!chkPaid = False Then
Me!txtPaymentDate.Visible = False
Me!frmHowPaid.Visible = False
Else
Me!txtPaymentDate.Visible = True
Me!frmHowPaid.Visible = False
End If
Me!txtCardholdersName.Visible = Me.Check25
End Sub
 
Bruce:

There is no After Update event listed on the properties sheet for the
checkbox. Do I use On Key Press? Here is the code as it stands now:

Private Sub Check25_AfterUpdate() (does not exist)
Me!txtCardholdersName.Visible = Me.chkCredit
End Sub

Private Sub Form_Current()
If Me!chkPaid = False Then
Me!txtPaymentDate.Visible = False
Me!frmHowPaid.Visible = False
Else
Me!txtPaymentDate.Visible = True
Me!frmHowPaid.Visible = True
End If

Me!txtCardholdersName.Visible = Me.chkCredit
(this is where the code breaks, I get an error saying: Run-time error "13":
Type Mismatch)

End Sub

I fixed the Me!frmHowPaid.Visible, thanks for seeing that.

Thanks,
Scott


BruceM via AccessMonster.com said:
You can shorten the code by doing something like this:

Me!txtPaymentDate.Visible = Me!chkPaid

I'm unclear what you want to do with Me!frmHowPaid.Visible, as it is false in
every case.

This should show the text box txtCardholdersName if the check box Check25 is
checked:
Me!txtCardholdersName.Visible = Me.Check25

However, the check box Got Focus event is not the place. More likely it
would be in the check box After Update event, AND in the form's Current event.
If you change it just in the After Update event of Check25 its Visible
property will stay the same until the next time you update Check25, either in
the current record or another record, for the rest of the time the form is
open. Using the Current event assures its Visible property will be
appropriate to the value in Check25 when you first arrive at a record.

An event may be used for several distinct things. Just add another line of
code (or block of code if you need more than a single line).

As for the ambiguous name, my guess is frmHowPaid. What happened when you
set a break point and stepped through the code? What is frmHowPaid?
I'm sorry my terminology is poor. If you have two distinct things you want to
accomplish (say, make a field visible or invisible based on a cjheck box
being checked and also another group of fields visible or invisible based on
a checkbox in an option group being checked) and you wanted them both to (no
word here) happen on the On_Current event of the form, can yu put them bothin
the same Private Sub Form_Current() ... End Sub? I hoppe that is clearer.

Aso, would this hide a text field unless a checkbox is checked? If not what
is the syntax?
Private Sub Check25_GotFocus()
Me!txtCardholdersName.Visible = Me.Check25
End Sub

Private Sub Form_Current()
If Me!chkPaid = False Then
Me!txtPaymentDate.Visible = False
Me!frmHowPaid.Visible = False
Else
Me!txtPaymentDate.Visible = True
Me!frmHowPaid.Visible = False
End If
Me!txtCardholdersName.Visible = Me.Check25
End Sub
I'm thinking not as I am getting anerror message saing I have an ambiguous
name. So what does a poor boy do?
Click to expand...
[quoted text clipped - 5 lines]
If you would like help with the code... post it so we can see it.
Click to expand...
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
I'm sorry my terminology is poor. If you have two distinct things you want to
accomplish (say, make a field visible or invisible based on a cjheck box
being checked and also another group of fields visible or invisible based on
a checkbox in an option group being checked) and you wanted them both to (no
word here) happen on the On_Current event of the form, can yu put them bothin
the same Private Sub Form_Current() ... End Sub? I hoppe that is clearer.
Click to expand...

Of course. You're allowed at least hundreds of lines of code before the module
gets too big; that code can contain any number of If... Then... Else... End If
blocks.
Aso, would this hide a text field unless a checkbox is checked? If not what
is the syntax?
Private Sub Check25_GotFocus()
Me!txtCardholdersName.Visible = Me.Check25
End Sub
Click to expand...

Yes.... but! You need to have it in both the form's Current event and the
checkbox's AfterUpdate event. GotFocus would not be the appropriate event.
Private Sub Form_Current()
If Me!chkPaid = False Then
Me!txtPaymentDate.Visible = False
Me!frmHowPaid.Visible = False
Else
Me!txtPaymentDate.Visible = True
Me!frmHowPaid.Visible = False
End If
Me!txtCardholdersName.Visible = Me.Check25
End Sub
Click to expand...

This checks chkPaid; if it's False then it sets frmHowPaid.Visible to False.
And if it's True, it ALSO sets frmHowPaid.Visible to False.

It may be simpler to just use the value in the checkbox:

Me!txtPaymentDate.Visible = Me!chkPaid
Me!frmHowPaid.Visible = Me!chkPaid

But... what is frmHowPaid? A Form? a Subform? an oddly naed textbox?
 
Bruce,

Your latest iteration worked after I set a default for the optionb group.
Yeah!!!!

Thanks for your help. I think I may have leraned a little.

Best,
Scott

BruceM via AccessMonster.com said:
If there is no After Update event I am guessing you are looking at an option
group that uses check boxes. In that case the check box is within a box on
the form, along with other check boxes. They will not have After Update
events. The box containing the checkboxes is the option group, which does
have an After Update event. That is where the code needs to be.

Each selectable control (radio button, toggle button, or check box) in an
option group has an Option Value on the Data tab of the Property Sheet. That
is the value you need to use in the code. Assuming I understand the
situation correctly, I will call the Option Group grpSelect. If the Option
Value of Check25 is 2 your code would be:

Private Sub grpSelect_AfterUpdate()

Me!txtCardholdersName.Visible = (Me.grpSelect = 2)

End Sub


Bruce:

There is no After Update event listed on the properties sheet for the
checkbox. Do I use On Key Press? Here is the code as it stands now:

Private Sub Check25_AfterUpdate() (does not exist)
Me!txtCardholdersName.Visible = Me.chkCredit
End Sub

Private Sub Form_Current()
If Me!chkPaid = False Then
Me!txtPaymentDate.Visible = False
Me!frmHowPaid.Visible = False
Else
Me!txtPaymentDate.Visible = True
Me!frmHowPaid.Visible = True
End If

Me!txtCardholdersName.Visible = Me.chkCredit
(this is where the code breaks, I get an error saying: Run-time error "13":
Type Mismatch)

End Sub

I fixed the Me!frmHowPaid.Visible, thanks for seeing that.

Thanks,
Scott
You can shorten the code by doing something like this:
Click to expand...
[quoted text clipped - 50 lines]
If you would like help with the code... post it so we can see it.
Click to expand...
Click to expand...
Click to expand...
 
John,

Thanks for the patience. I live where there is not a chance to have "formal"
training in Access programming, so I am taking it on as a personal side line
in my spare time. There is a lot to learn. You and everyone here has been
great.

As I told Bruce above, the latest interation works and I am very happy.

Now on to even more exciting things. Is there a website referernce you can
provide that will explain syntax issues such as how to write for check boxes
as opposed to text boxes as opposed to date fiields, etc?

Many Thanks,
Scott
 
Now on to even more exciting things. Is there a website referernce you can
provide that will explain syntax issues such as how to write for check boxes
as opposed to text boxes as opposed to date fiields, etc?
Click to expand...

You may need to start a bit further back. Checkboxes and textboxes are
Controls on a form; date fields are NOT "controls on a form", they're values
stored in a table in a Date/Time datatype field. A date/time field can be
displayed in a textbox, but they're not even apples and oranges - they're
apples and produce crates!

I can't think of one that answers *those specific questions*. A good book
might be worthwhile: Access <version> Inside Out by my friend John Viescas
might be a good choice. Here's some additional resources (which you may have
seen):

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
Back
Top