Display control dependant on contents of another control

[Darhl Thomason]

I want to have a text box (txtBuypassID) display only when another text box
(txtProcessor) has specific text in it (Buypass). My database tracks
stores, credit card processors, merchant #'s, etc. One credit card
processor has an ID that is based on the merchant #. I have that part
figured out, but I only want this text box to display when the contents of
txtProcessor = "Buypass".

The control source of txtBuypassID is:
="Buypass ID = " & Mid([Merch#],5,6)

I have tried putting an if/then into the control source (i.e. If
txtProcessor = "Buypass" then txtBuypassID = "Buypass ID = " &
Mid([Merch#],5,6) but then the control only displays #Name?

I also intend on using this on a report, so if someone has an idea, keep
that in mind.

Any ideas would be very welcome!

Thanks,

Darhl

 

[rmullen]

If Me.txtProcessor = "BuyPass" Then
txtBuypassID.Visible = True
EndIf

you'll want to make the default value of txtbuypassID to be not visible though.

 

[Allen Browne]

You need to use 4 events to do this:
1. The AfterUpdate event procedure of the txtProcessor (so it responds to a
change);
2. The Current event of the form (so it sets when you change record);
3. The Undo event of the control (so it resets after undo of txtProcessor);
4. The Undo event of the form (so it resets if the record is undone.)

It makes sense to write one function and call it 4 times, rather than
writing and maintaining 4 instances of the code.

The first 2 events need to respond to the new Value of the control, and the
Undo events to its OldValue. That means we have to pass into the function
either the Value or the OldValue so it knows what to respond to.

The 2nd and 4th events above could occur when txtBuypassID has focus, so you
need error handling to change focus if that occurs.

For efficiency, and so it's not constantly blinking, we change the Visible
property only if it is wrong.

Try something like this:

Private Sub txtProcessor_AfterUpdate()
Call ShowHide(Me.txtProcessor.Value)
End Sub
Private Sub Form_Current()
Call ShowHide(Me.txtProcessor.Value)
End Sub
Private Sub Form_Undo()
Call ShowHide(Me.txtProcessor.OldValue)
End Sub
Private Sub txtProcessor_Undo()
Call ShowHide(Me.txtProcessor.OldValue)
End Sub

Private Function ShowHide(varValue As Varaint)
On Error GoTo ErrHandler
Dim bShow As Boolean

bShow = Nz((Me.txtProcessor = "Buypass"), False)
With Me.txtBuypassID
If .Visible <> bShow Then
.Visible = bShow
End If
End With

ExitHandler:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2164&, 2165& 'Can't disable/hide the control with focus.
Me.txtProcessor.SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume ExitHandler
End Select
End Function

 

[Darhl Thomason]

Thanks Allen,

I added the code and am now getting this error message:
"The expression On Open you entered as the event property setting produced
the following error:
User-defined type not defined.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro."

Thanks!

Darhl

You need to use 4 events to do this:
1. The AfterUpdate event procedure of the txtProcessor (so it responds to
a change);
2. The Current event of the form (so it sets when you change record);
3. The Undo event of the control (so it resets after undo of
txtProcessor);
4. The Undo event of the form (so it resets if the record is undone.)

It makes sense to write one function and call it 4 times, rather than
writing and maintaining 4 instances of the code.

The first 2 events need to respond to the new Value of the control, and
the Undo events to its OldValue. That means we have to pass into the
function either the Value or the OldValue so it knows what to respond to.

The 2nd and 4th events above could occur when txtBuypassID has focus, so
you need error handling to change focus if that occurs.

For efficiency, and so it's not constantly blinking, we change the Visible
property only if it is wrong.

Try something like this:

Private Sub txtProcessor_AfterUpdate()
Call ShowHide(Me.txtProcessor.Value)
End Sub
Private Sub Form_Current()
Call ShowHide(Me.txtProcessor.Value)
End Sub
Private Sub Form_Undo()
Call ShowHide(Me.txtProcessor.OldValue)
End Sub
Private Sub txtProcessor_Undo()
Call ShowHide(Me.txtProcessor.OldValue)
End Sub

Private Function ShowHide(varValue As Varaint)
On Error GoTo ErrHandler
Dim bShow As Boolean

bShow = Nz((Me.txtProcessor = "Buypass"), False)
With Me.txtBuypassID
If .Visible <> bShow Then
.Visible = bShow
End If
End With

ExitHandler:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2164&, 2165& 'Can't disable/hide the control with focus.
Me.txtProcessor.SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume ExitHandler
End Select
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to have a text box (txtBuypassID) display only when another text
box (txtProcessor) has specific text in it (Buypass). My database tracks
stores, credit card processors, merchant #'s, etc. One credit card
processor has an ID that is based on the merchant #. I have that part
figured out, but I only want this text box to display when the contents of
txtProcessor = "Buypass".

The control source of txtBuypassID is:
="Buypass ID = " & Mid([Merch#],5,6)

I have tried putting an if/then into the control source (i.e. If
txtProcessor = "Buypass" then txtBuypassID = "Buypass ID = " &
Mid([Merch#],5,6) but then the control only displays #Name?

I also intend on using this on a report, so if someone has an idea, keep
that in mind.

Any ideas would be very welcome!

Thanks,

Darhl

 

[Allen Browne]

On Open?

What do you have in the form's On Open property?

After adding the code to the form's module, did you try Compile (first item
on Debug menu) to make sure everything made sense to the VBA compiler?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

I added the code and am now getting this error message:
"The expression On Open you entered as the event property setting produced
the following error:
User-defined type not defined.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro."

Thanks!

Darhl

You need to use 4 events to do this:
1. The AfterUpdate event procedure of the txtProcessor (so it responds to
a change);
2. The Current event of the form (so it sets when you change record);
3. The Undo event of the control (so it resets after undo of
txtProcessor);
4. The Undo event of the form (so it resets if the record is undone.)

It makes sense to write one function and call it 4 times, rather than
writing and maintaining 4 instances of the code.

The first 2 events need to respond to the new Value of the control, and
the Undo events to its OldValue. That means we have to pass into the
function either the Value or the OldValue so it knows what to respond to.

The 2nd and 4th events above could occur when txtBuypassID has focus, so
you need error handling to change focus if that occurs.

For efficiency, and so it's not constantly blinking, we change the
Visible property only if it is wrong.

Try something like this:

Private Sub txtProcessor_AfterUpdate()
Call ShowHide(Me.txtProcessor.Value)
End Sub
Private Sub Form_Current()
Call ShowHide(Me.txtProcessor.Value)
End Sub
Private Sub Form_Undo()
Call ShowHide(Me.txtProcessor.OldValue)
End Sub
Private Sub txtProcessor_Undo()
Call ShowHide(Me.txtProcessor.OldValue)
End Sub

Private Function ShowHide(varValue As Varaint)
On Error GoTo ErrHandler
Dim bShow As Boolean

bShow = Nz((Me.txtProcessor = "Buypass"), False)
With Me.txtBuypassID
If .Visible <> bShow Then
.Visible = bShow
End If
End With

ExitHandler:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2164&, 2165& 'Can't disable/hide the control with focus.
Me.txtProcessor.SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume ExitHandler
End Select
End Function

I want to have a text box (txtBuypassID) display only when another text
box (txtProcessor) has specific text in it (Buypass). My database tracks
stores, credit card processors, merchant #'s, etc. One credit card
processor has an ID that is based on the merchant #. I have that part
figured out, but I only want this text box to display when the contents
of txtProcessor = "Buypass".

The control source of txtBuypassID is:
="Buypass ID = " & Mid([Merch#],5,6)

I have tried putting an if/then into the control source (i.e. If
txtProcessor = "Buypass" then txtBuypassID = "Buypass ID = " &
Mid([Merch#],5,6) but then the control only displays #Name?

I also intend on using this on a report, so if someone has an idea, keep
that in mind.

Any ideas would be very welcome!

 

[Darhl Thomason]

Got an error on compile at "Private Function ShowHide(varValue As Varaint)".
It said "User defined type not defined".

I don't see an "On Open", but I do see this that may be what you're
referring to:
Private Sub Form_Open(Cancel As Integer)
Me.tglNonTraditional.Enabled = False
Me.tglUpcomingStores.Enabled = False
Me.chkScheduled = True
Me.chkStandby = True
Call Get_Recordsource
End Sub

Thanks Allen!

Darhl

 

[Allen Browne]

Sorry: typo in the spelling of Variant.

 

[Darhl Thomason]

Oops, I'm surprised I didn't catch that one as well...

So, now we're past that one, I'm getting another compile error in line:
Private Sub Form_Undo()

The error is:
Procedure declaration does not match description of event or procedure
having the same name.

Thanks!

d

 

[Darhl Thomason]

OK, so I rem'd out the three lines of that sub and got the same error on the
next sub:
Private Sub txtProcessor_Undo()

Thanks again for all your help!!!

Darhl

 

[Allen Browne]

The Undo events can be cancelled, so they need the parameter in their
declaration:

Private Sub Form_Undo(Cancel As Integer)

Private Sub txtProcessor_Undo(Cancel As Integer)

 

[Darhl Thomason]

Thanks Allen! That did the trick. Now, can this be adapted to a report?

Thanks again,

Darhl

 

[Allen Browne]

So, you want to show the txtBuypassID field on a report, only when
txtProcessor contains the word "Buypass".

Simplest thing is to add a text box to the report, and set its Control
Source to:
=IIf([txtProcessor] = "Buypass", [txtBuyPassID], Null)

If you need to hide the label associated with that text box also,
right-click it and choose Change To | Text Box. Then set the Control Source
of this text box to:
=IIf([txtBuyPassID] Is Null, Null, "Whatever label you want")

It is possible to do this with the Format event of the (Detail?) Section of
the report, but that is way slower using the IIf() expression. It is also
possible to fudge it with Conditional Formatting (hiding via white text on a
white background), but again the simple IIf() is better.

If you end up with #Error in your text box, and the expression is correct,
make sure the text box Name is not the same as the name of one of the fields
in the report's RecordSource.

 

[Darhl Thomason]

Thanks Allen,

I've gotta learn that IIF better. It seems like it's a type of if/then you
can use on txt controls.

I changed the control source of the txt box to:
=IIf([txtProcessor]="Buypass","ID = " & Mid([Merch#],5,6),Null)

Thanks again for your help!

Darhl

So, you want to show the txtBuypassID field on a report, only when
txtProcessor contains the word "Buypass".

Simplest thing is to add a text box to the report, and set its Control
Source to:
=IIf([txtProcessor] = "Buypass", [txtBuyPassID], Null)

If you need to hide the label associated with that text box also,
right-click it and choose Change To | Text Box. Then set the Control
Source of this text box to:
=IIf([txtBuyPassID] Is Null, Null, "Whatever label you want")

It is possible to do this with the Format event of the (Detail?) Section
of the report, but that is way slower using the IIf() expression. It is
also possible to fudge it with Conditional Formatting (hiding via white
text on a white background), but again the simple IIf() is better.

If you end up with #Error in your text box, and the expression is correct,
make sure the text box Name is not the same as the name of one of the
fields in the report's RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen! That did the trick. Now, can this be adapted to a report?

Thanks again,

Darhl

 

[Darhl Thomason]

Allen,

Would you please explain to me why I should use the code you provided me
with (which works great) instead of doing it with an IIF statement (which
also works)? I'm just trying to learn better ways of doing things.

Thanks,

Darhl

Thanks Allen,

I've gotta learn that IIF better. It seems like it's a type of if/then
you can use on txt controls.

I changed the control source of the txt box to:
=IIf([txtProcessor]="Buypass","ID = " & Mid([Merch#],5,6),Null)

Thanks again for your help!

Darhl

So, you want to show the txtBuypassID field on a report, only when
txtProcessor contains the word "Buypass".

Simplest thing is to add a text box to the report, and set its Control
Source to:
=IIf([txtProcessor] = "Buypass", [txtBuyPassID], Null)

If you need to hide the label associated with that text box also,
right-click it and choose Change To | Text Box. Then set the Control
Source of this text box to:
=IIf([txtBuyPassID] Is Null, Null, "Whatever label you want")

It is possible to do this with the Format event of the (Detail?) Section
of the report, but that is way slower using the IIf() expression. It is
also possible to fudge it with Conditional Formatting (hiding via white
text on a white background), but again the simple IIf() is better.

If you end up with #Error in your text box, and the expression is
correct, make sure the text box Name is not the same as the name of one
of the fields in the report's RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen! That did the trick. Now, can this be adapted to a
report?

Thanks again,

Darhl

 

[Allen Browne]

If you just want to display the result on a form without storing it in a
table, then the IIf() statement will be fine. Depends what you need to
achieve.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Would you please explain to me why I should use the code you provided me
with (which works great) instead of doing it with an IIF statement (which
also works)? I'm just trying to learn better ways of doing things.

Thanks,

Darhl

Thanks Allen,

I've gotta learn that IIF better. It seems like it's a type of if/then
you can use on txt controls.

I changed the control source of the txt box to:
=IIf([txtProcessor]="Buypass","ID = " & Mid([Merch#],5,6),Null)

Thanks again for your help!

Darhl

So, you want to show the txtBuypassID field on a report, only when
txtProcessor contains the word "Buypass".

Simplest thing is to add a text box to the report, and set its Control
Source to:
=IIf([txtProcessor] = "Buypass", [txtBuyPassID], Null)

If you need to hide the label associated with that text box also,
right-click it and choose Change To | Text Box. Then set the Control
Source of this text box to:
=IIf([txtBuyPassID] Is Null, Null, "Whatever label you want")

It is possible to do this with the Format event of the (Detail?) Section
of the report, but that is way slower using the IIf() expression. It is
also possible to fudge it with Conditional Formatting (hiding via white
text on a white background), but again the simple IIf() is better.

If you end up with #Error in your text box, and the expression is
correct, make sure the text box Name is not the same as the name of one
of the fields in the report's RecordSource.

Thanks Allen! That did the trick. Now, can this be adapted to a
report?

 

[Darhl Thomason]

Got it!

Thanks again for your expert help!

Darhl

If you just want to display the result on a form without storing it in a
table, then the IIf() statement will be fine. Depends what you need to
achieve.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Would you please explain to me why I should use the code you provided me
with (which works great) instead of doing it with an IIF statement (which
also works)? I'm just trying to learn better ways of doing things.

Thanks,

Darhl

Thanks Allen,

I've gotta learn that IIF better. It seems like it's a type of if/then
you can use on txt controls.

I changed the control source of the txt box to:
=IIf([txtProcessor]="Buypass","ID = " & Mid([Merch#],5,6),Null)

Thanks again for your help!

Darhl


So, you want to show the txtBuypassID field on a report, only when
txtProcessor contains the word "Buypass".

Simplest thing is to add a text box to the report, and set its Control
Source to:
=IIf([txtProcessor] = "Buypass", [txtBuyPassID], Null)

If you need to hide the label associated with that text box also,
right-click it and choose Change To | Text Box. Then set the Control
Source of this text box to:
=IIf([txtBuyPassID] Is Null, Null, "Whatever label you want")

It is possible to do this with the Format event of the (Detail?)
Section of the report, but that is way slower using the IIf()
expression. It is also possible to fudge it with Conditional Formatting
(hiding via white text on a white background), but again the simple
IIf() is better.

If you end up with #Error in your text box, and the expression is
correct, make sure the text box Name is not the same as the name of one
of the fields in the report's RecordSource.

Thanks Allen! That did the trick. Now, can this be adapted to a
report?