Need A code for Visible/Hide base on Combo Box

[Alam]

I have been working on a small corporate DB And am stuck on an account form
Name: TransactionMainFm
I need a Function which can show field base on TransactionTypeID selection.
I am abl to pull it off mostly by using tha old fashioned After UpDate
TransTypeIDCombo & On Current Events If and Else and Visible true and False .

I have also used Dirk Goldgar's : Code for validation required field
fncRequiredFieldsMissing using Tag property, it is working nicely (( THANKS
DIRK ,00)) Except for a few scenarios !!
I am wondering if someone can help me with some kind of Public function.

I am dead tired writing and checking My If and else , looks like it will be
over 500 line code B/W OnCurrent And Combo After Update event. There must be
some kind of function like Dirk Goldgar's : fncRequiredFieldsMissing
Function ??

Here’s My TransactionTb

Table frmControl



So far I am able to make a nice small single form which shows and hides
fields
Depending on Transaction Type ID ( ComboBox )
Transaction Type ID Is fixed not standard but works Perfect in my situation.
It has 10 options

Table frmControl

TransactionID Auto # PK TextBox
AgencyID Number ComboBox
BankID Number ComboBox
ChequeNo Text TextBox
CreditCardID Number ComboBox
CardTranscationNo Text TextBox
TranscationDate Date/Time TextBox
TranscationTypeID Number ComboBox
PayeesID Number ComboBox
DepositAmount Currency TextBox
WithdrawalAmount Currency TextBox
BankCreditCardFee Currency TextBox
ExpenseAmount Currency TextBox
CheckIssueDate Date/Time TextBox
CardBillPay Currency TextBox
ExpenseTypeID Number ComboBox
Note Text Text
Recipt Yes/No CheckBox
EmployeeID Number ComboBox
PayCheckAmount Currency TextBox



TransactionTypeTb Look Like :

Transaction TransactionType Description
TypeID

Explanation: use in Mainfm
1 Agency Transaction Only Agency Check Deposit DepositAmount
2 Other Check Deposit Personal, IRS Return ect. DepositAmount
3 Cash Transaction Cash Expense ExpenseAmount
4 Credit Card Transaction Credit Card Charge ExpenseAmount
5 Credit Card Bill Payment Credit Card Bill Payment CardBillPatAmount
6 ATM Withdrawal ATM WithdrawalAmount
7 Check Issue Check Issue For Expense ExpenseAmount
8 Fees And Other Charges Bank or Credit Card Fees or Services CardBillPay
9 Cash Deposit Cash Deposit In BankAcc ExpenseAmount
10 PAY ROLL Employee’s Check Issue PayCheckAmount

Any Help is greatly appreciated.

 

[Jeanette Cunningham]

If I understand what you are trying to do, here is a suggestion.
Create a routine which will show and hide the controls as needed.
Call this sub when ever needed.


Private Sub ShowHideControl()
Me.ChequeNo.Visible = Me.TransTypeIDCombo = 1
Me.CardTranscationNo.Visible = Me.TransTypeIDCombo = 2
Me.WithdrawalAmount.Visible = Me.TransTypeIDCombo = 3
'and continue on for as many as you need
'replace the value for the combo with the correct number or text to suit
your needs
End Sub

Now in the AfterUpdate for the combo, you can do
Call ShowHideControl

And in the form's current event, you can do
Call ShowHideControl


The above will save quite a bit of typing, and save you from lots of If ...
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Alam via AccessMonster.com]

If I understand what you are trying to do, here is a suggestion.
Create a routine which will show and hide the controls as needed.
Call this sub when ever needed.

Private Sub ShowHideControl()
Me.ChequeNo.Visible = Me.TransTypeIDCombo = 1
Me.CardTranscationNo.Visible = Me.TransTypeIDCombo = 2
Me.WithdrawalAmount.Visible = Me.TransTypeIDCombo = 3
'and continue on for as many as you need
'replace the value for the combo with the correct number or text to suit
your needs
End Sub

Now in the AfterUpdate for the combo, you can do
Call ShowHideControl

And in the form's current event, you can do
Call ShowHideControl

The above will save quite a bit of typing, and save you from lots of If ...
End If

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have been working on a small corporate DB And am stuck on an account form
Name: TransactionMainFm

[quoted text clipped - 69 lines]

Any Help is greatly appreciated.

 

[Alam via AccessMonster.com]

If I understand what you are trying to do, here is a suggestion.
Create a routine which will show and hide the controls as needed.
Call this sub when ever needed.

Private Sub ShowHideControl()
Me.ChequeNo.Visible = Me.TransTypeIDCombo = 1
Me.CardTranscationNo.Visible = Me.TransTypeIDCombo = 2
Me.WithdrawalAmount.Visible = Me.TransTypeIDCombo = 3
'and continue on for as many as you need
'replace the value for the combo with the correct number or text to suit
your needs
End Sub

Now in the AfterUpdate for the combo, you can do
Call ShowHideControl

And in the form's current event, you can do
Call ShowHideControl

The above will save quite a bit of typing, and save you from lots of If ...
End If

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have been working on a small corporate DB And am stuck on an account form
Name: TransactionMainFm

[quoted text clipped - 69 lines]

Any Help is greatly appreciated.

Thank you Jeanette for your quick respond this is exactly what I need
Very claver.
Form On Current and AfrerUpDate TransactionTypeID event works great together
only problem is when the user changes the pervious record by changing
TransactionTypeID then On Current event Hide the records in relate field
WITH OLD DATA and TransactionTypeID allow update New data in visible Fields

After TransactionTypeID Update It Should remove all other previously entered
data in
that Row.

How can I delete the pervious data by updating TransactionTypeID .Combo
Thanks’ again you sure save me a hake of If And Else.

 

[Jeanette Cunningham]

Re: only problem is when the user changes the pervious record by changing

TransactionTypeID then On Current event Hide the records in relate field
WITH OLD DATA and TransactionTypeID allow update New data in visible
Fields

You can restrict ShowHideControl to work only with new records, see if that
helps.

Private Sub ShowHideControl()
If Me.NewRecord = True Then
Me.ChequeNo.Visible = Me.TransTypeIDCombo = 1
Me.CardTranscationNo.Visible = Me.TransTypeIDCombo = 2
Me.WithdrawalAmount.Visible = Me.TransTypeIDCombo = 3
End If
'and continue on for as many as you need
'replace the value for the combo with the correct number or text to
'suit your needs
End Sub



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

If I understand what you are trying to do, here is a suggestion.
Create a routine which will show and hide the controls as needed.
Call this sub when ever needed.

Private Sub ShowHideControl()
Me.ChequeNo.Visible = Me.TransTypeIDCombo = 1
Me.CardTranscationNo.Visible = Me.TransTypeIDCombo = 2
Me.WithdrawalAmount.Visible = Me.TransTypeIDCombo = 3
'and continue on for as many as you need
'replace the value for the combo with the correct number or text to
suit
your needs
End Sub

Now in the AfterUpdate for the combo, you can do
Call ShowHideControl

And in the form's current event, you can do
Call ShowHideControl

The above will save quite a bit of typing, and save you from lots of If
...
End If

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have been working on a small corporate DB And am stuck on an account
form
Name: TransactionMainFm

[quoted text clipped - 69 lines]

Any Help is greatly appreciated.

Thank you Jeanette for your quick respond this is exactly what I need
Very claver.
Form On Current and AfrerUpDate TransactionTypeID event works great
together
only problem is when the user changes the pervious record by changing
TransactionTypeID then On Current event Hide the records in relate field
WITH OLD DATA and TransactionTypeID allow update New data in visible
Fields

After TransactionTypeID Update It Should remove all other previously
entered
data in
that Row.

How can I delete the pervious data by updating TransactionTypeID .Combo
Thanks' again you sure save me a hake of If And Else.

 

[Alam via AccessMonster.com]

Re: only problem is when the user changes the pervious record by changing

TransactionTypeID then On Current event Hide the records in relate field
WITH OLD DATA and TransactionTypeID allow update New data in visible
Fields

You can restrict ShowHideControl to work only with new records, see if that
helps.

Private Sub ShowHideControl()
If Me.NewRecord = True Then
Me.ChequeNo.Visible = Me.TransTypeIDCombo = 1
Me.CardTranscationNo.Visible = Me.TransTypeIDCombo = 2
Me.WithdrawalAmount.Visible = Me.TransTypeIDCombo = 3
End If
'and continue on for as many as you need
'replace the value for the combo with the correct number or text to
'suit your needs
End Sub

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
[quoted text clipped - 44 lines]

How can I delete the pervious data by updating TransactionTypeID .Combo
Thanks' again you sure save me a hake of If And Else.

Thanks again for your time .
Perhaps I got a little excited too soon after checking a few fields .
Now that I wrote the complete code , I am having problems with
TransactionTypeID
A few fields are not visible both OnCurrent as well as AfterUpdate
I am sure it is form or controls properties .
This Is a Single Form, 6†Wide And 4.5†Tall, all controls visible property
set visible =‘Yes†It has 20 fields . At a time maximum 9 fields and
minimum 6 fields are visible .
Some Controls are on top of each other and visible accordingly and some move

Up and down e.g. Me.CreditCardID.Top = 1750. Also Label caption changes with
selection e.g:

‘MyCombo=5 Credit Card Bill Payment
If Me.TranscationTypeID = 5 Then
Me.CreditCardID.Top = 1375
Me.LBCreditCardID.Top = 1375
Me.LBBankID.Caption = "Pay From Bank Acc"
Else
Me.LBBankID.Caption = "Bank Account"
' End
End If


With your code this is not working
Me.LBBankID.Caption = "Bank Account" = TranscationTypeID = 1
Me.LAB = "Deposit" = TranscationTypeID = 1

And BankID Creditid Card ID ExpenseTypeID is not visible but ironically
BankID visible On PAY ROLL TransID=10
Thank you so much for taking the time to help me, it is greatly appreciated.

 

[Jeanette Cunningham]

Re: only problem is when the user changes the pervious record by changing

TransactionTypeID then On Current event Hide the records in relate
field
WITH OLD DATA and TransactionTypeID allow update New data in visible
Fields

You can restrict ShowHideControl to work only with new records, see if
that
helps.

Private Sub ShowHideControl()
If Me.NewRecord = True Then
Me.ChequeNo.Visible = Me.TransTypeIDCombo = 1
Me.CardTranscationNo.Visible = Me.TransTypeIDCombo = 2
Me.WithdrawalAmount.Visible = Me.TransTypeIDCombo = 3
End If
'and continue on for as many as you need
'replace the value for the combo with the correct number or text to
'suit your needs
End Sub

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

If I understand what you are trying to do, here is a suggestion.
Create a routine which will show and hide the controls as needed.

[quoted text clipped - 44 lines]

How can I delete the pervious data by updating TransactionTypeID .Combo
Thanks' again you sure save me a hake of If And Else.

Thanks again for your time .
Perhaps I got a little excited too soon after checking a few fields .
Now that I wrote the complete code , I am having problems with
TransactionTypeID
A few fields are not visible both OnCurrent as well as AfterUpdate
I am sure it is form or controls properties .
This Is a Single Form, 6" Wide And 4.5" Tall, all controls visible
property
set visible ='Yes" It has 20 fields . At a time maximum 9 fields and
minimum 6 fields are visible .
Some Controls are on top of each other and visible accordingly and some
move

Up and down e.g. Me.CreditCardID.Top = 1750. Also Label caption changes
with
selection e.g:

'MyCombo=5 Credit Card Bill Payment
If Me.TranscationTypeID = 5 Then
Me.CreditCardID.Top = 1375
Me.LBCreditCardID.Top = 1375
Me.LBBankID.Caption = "Pay From Bank Acc"
Else
Me.LBBankID.Caption = "Bank Account"
' End
End If


With your code this is not working
Me.LBBankID.Caption = "Bank Account" = TranscationTypeID = 1
Me.LAB = "Deposit" = TranscationTypeID = 1

And BankID Creditid Card ID ExpenseTypeID is not visible but ironically
BankID visible On PAY ROLL TransID=10
Thank you so much for taking the time to help me, it is greatly
appreciated.

 

[Jeanette Cunningham]

This is not easy to set up correctly, it takes a lot of time to keep
fiddling with it until you get it working.
I have done something similar once, but I never wanted to do that again.


To try to change the caption of labels depending on the combo value is an
extra complication that isn't worth the time.
One thing I suggest, is to have each control together with its own label and
just make the control visible.
When the control is visible, its label will be visible, when the control is
hidden, its label will also be hidden.


Another suggestion is to make a separate subform for each value of
TransTypeIDCombo .
I know that this sounds like overkill, but it is often easier to build and
maintain.
Each time user makes a different selection from the combo, just supply the
correct subform.
This method saves a lot of fiddling around.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Re: only problem is when the user changes the pervious record by changing

TransactionTypeID then On Current event Hide the records in relate
field
WITH OLD DATA and TransactionTypeID allow update New data in visible
Fields

You can restrict ShowHideControl to work only with new records, see if
that
helps.

Private Sub ShowHideControl()
If Me.NewRecord = True Then
Me.ChequeNo.Visible = Me.TransTypeIDCombo = 1
Me.CardTranscationNo.Visible = Me.TransTypeIDCombo = 2
Me.WithdrawalAmount.Visible = Me.TransTypeIDCombo = 3
End If
'and continue on for as many as you need
'replace the value for the combo with the correct number or text to
'suit your needs
End Sub

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

If I understand what you are trying to do, here is a suggestion.
Create a routine which will show and hide the controls as needed.

[quoted text clipped - 44 lines]

How can I delete the pervious data by updating TransactionTypeID .Combo
Thanks' again you sure save me a hake of If And Else.

Thanks again for your time .
Perhaps I got a little excited too soon after checking a few fields .
Now that I wrote the complete code , I am having problems with
TransactionTypeID
A few fields are not visible both OnCurrent as well as AfterUpdate
I am sure it is form or controls properties .
This Is a Single Form, 6" Wide And 4.5" Tall, all controls visible
property
set visible ='Yes" It has 20 fields . At a time maximum 9 fields and
minimum 6 fields are visible .
Some Controls are on top of each other and visible accordingly and some
move

Up and down e.g. Me.CreditCardID.Top = 1750. Also Label caption changes
with
selection e.g:

'MyCombo=5 Credit Card Bill Payment
If Me.TranscationTypeID = 5 Then
Me.CreditCardID.Top = 1375
Me.LBCreditCardID.Top = 1375
Me.LBBankID.Caption = "Pay From Bank Acc"
Else
Me.LBBankID.Caption = "Bank Account"
' End
End If


With your code this is not working
Me.LBBankID.Caption = "Bank Account" = TranscationTypeID = 1
Me.LAB = "Deposit" = TranscationTypeID = 1

And BankID Creditid Card ID ExpenseTypeID is not visible but ironically
BankID visible On PAY ROLL TransID=10
Thank you so much for taking the time to help me, it is greatly
appreciated.

 

[Alam via AccessMonster.com]

This is not easy to set up correctly, it takes a lot of time to keep
fiddling with it until you get it working.

To try to change the caption of labels depending on the combo value is an
extra complication that isn't worth the time.
Another suggestion is to make a separate subform for each value of
TransTypeIDCombo .
I know that this sounds like overkill, but it is often easier to build and
This method saves a lot of fiddling around.

Sure it did took me a while but it work perfect. Last night I completed
My If And Else on Combo AfterUpdate And On Current Results WOW!
I check every Thing thoroughly And YES it is working, except one

TransactionTypeID # 8 Fees And Other Charges .
This can be Bank Or Credit Card Not Both and Amount will be store in Field
Name “BankCreditCardFee†for both in TransactionTb .

So I Removed Tag “Required†From CreditCardID pass required validation reule
On Form Before Update Event

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim LMsg As String
Dim LResponse
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim TranscationTypeID As String

If IsNull(Me.CreditCardID) And Me!CreditCardID.Visible = True And Me.BankID.
Visible = False Then
strMsg = "Tanscation Main Form" & vbNewLine & _
"Is Not Completed " & vbNewLine & "Credit Card Name Is Required" & vbNewLine
& vbNewLine & _
" Click 'OK' To Select Credit Card Name"
strTitle = "Credit Card ID Is Missing"
intStyle = vbOKOnly + vbInformation
MsgBox strMsg, intStyle, strTitle
‘ Cancle = True
Me.CreditCardID.SetFocus
End
End If

LMsg = "Transcation Record For " & Me.TranscationTypeID.Column(1) &
vbNewLine & _
Me.EmployeeID.Column(1) & _
Me.AgencyID.Column(1) & _
Me.PayeesID.Column(1) & _
Me!ExpenseTypeID.Column(1) & " And/From" & vbNewLine & _
Me!BankID.Column(1) & " " & _
Me!CreditCardID.Column(1) & " Will Be Save " & vbNewLine & _

"Click 'Yes' To Save Changes And 'No' To Cancle "

Cancel = fncRequiredVisibleMissing(Me)

LResponse = MsgBox(LMsg, vbYesNo + vbQuestion, "Save Confirmation")
If LResponse = vbNo Then

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If

End Sub


There Are 4 valid possibilities for TransactionTypeID # 8 Fees And Other
Charges


1) If CreditCardID Is blank My Before Update event trigger MsgBox “ My
Messageâ€

2) If BankID Is blank Dirk’s Code trigger

3) If Both Selected then I use GotFocus On Both BankID And CreditCardID like
This :

Private Sub BankID_GotFocus()
Dim TranscationTypeID As String
If Me.TranscationTypeID.Value = 8 Then
If Not IsNull(Me.CreditCardID) Then

MsgBox "You Already Selected A " & vbNewLine & _
Me.CreditCardID.Column(1) & vbNewLine & _
"Delete Credite Card Name First Then" & vbNewLine & _
"Select The Bank Name ", vbOKOnly + vbQuestion, "Select Only One "
Me.TransactionID.SetFocus
End If
End If
End Sub

Same On CriditCardID like
Private Sub CreditCardID_GotFocus()
MsgBox “My Message & BankID.Column(1) “
Me.BankID.SetFocus

This will prevent update on both if one IsNot Null.


PROBLEM

4) If CreditCardID is select then Dirk Code trigger because BankID is blank.

Now I am Stuck !!!. No where to go. if I remove BankID tag property then
It’s Back to square one .

Cancel = fncRequiredVisibleMissing(Me)
This is very cool code I did play with it and able to pull out label name in
msg box using

If blnNoValue Then
strErrorMessage = strErrorMessage & vbCr & _
" " & ctl.Controls(0).Caption
If .TabIndex < lngErrCtlTabIndex Then
strMyLable = ctl.Controls(0).Caption
lngErrCtlTabIndex = .TabIndex

If we able to FIX Problem # 4 I will be done with it and move to other form


Thanks again for your time .
After last post I was sure that you will quit as this form sound scary
20 fields from the table, some visible, some required and some moves

 

[Jeanette Cunningham]

Please excuse the blank post.

Would you summarize the requirements for TransactionTypeID # 8 Fees And
Other Charges.

I am guessing that these are the rules.
1. The user can complete either the textbox for Bank or the textbox for
Credit Card, but not both.
2. what else . . .


What controls when CreditCardID is visible ?
What controls when BankID is visible?

You can set some code on both CreditCardID and BankID so that user can only
fill in one.

If both CreditCardID And BankID are visible,
On the Enter event for CreditCardID, you can go

If Len(Me.BankID & vbNullString) >0 Then
Me.CreditCardID.Locked = True
MsgBox "You can only have one"
End If


On the Enter event for BankID , you can go

If Len(Me.CreditCardID& vbNullString) >0 Then
Me.BankID .Locked = True
MsgBox "You can only have one"
End If

The above code stops user from putting entries in both credit card and bank.
This makes your validation code simpler.

Give this a try and see if it makes the validation code easier.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Alam via AccessMonster.com]

What controls when CreditCardID is visible ?

What controls when BankID is visible?

Controls are visible or hide only with TranscationTypeID update not with
Bank or CCard ID’s
If and else is working great for all of the 10 options with TranscationTypeID

Now select one is also working well with Got Focus on Bank And CreditID

If TransactionTypeID # 8 Fees And Other Charges Then

All below are visible = True

Transcation ID Auto #
Transcation Date ( Text Box)
TranscationTypeID = # 8 Fees And Other Charges (combo box)
BanckID (Combo Box) **Required** >>[ Dirk’s Code trigger] !!! Problem !!!
CreditID ( Combo Box) *Required* >> [My Before Update event trigger] !!!
Problem !!!
Bank/CreditCardFees ( Text Box )
Wor is A label indicator Caption “ Select Only One Bank Or CreditCardâ€
[visible only TransID # 8]


The Other 9 Options work out well for visible / hide and required fields
using
If and Else and
Form Before Update Is Null for CreditCardID &
All other required fields are working with Dirk’s Code

If both CreditCardID And BankID are visible,
On the Enter event for CreditCardID, you can go

If Len(Me.BankID & vbNullString) >0 Then
Me.CreditCardID.Locked = True
MsgBox "You can only have one"
End If


The above code stops user from putting entries in both credit card and bank.
This makes your validation code simpler.

If Both are selected, I use Got focus On Both CreditCardID And BankID, it
works well to
prevent update on both of them at the same time.

Would you summarize the requirements for TransactionTypeID # 8 Fees And
Other Charges

The only difference is when TransactionTypeID # 8 ‘ Fees And Other Charges ‘
is selected
only one field is required not both and both field are visible ( BankID And
CreditCardID )
at the same time .

?? Required Field Codes for TransactionTypeID # 8 are conflicting with each
other ??



Dirk’s Code >> Cancel = fncRequiredVisibleMissing(Me) worked with visible &
tag property Required
and BankID tag property = Required for all .!! If we can stop code only with
TransTypeID # 8 !!

My Code for CreditCardID >>[tag property is blank]
Worked with Form Before Update like :

If IsNull(Me.CreditCardID) And Me!CreditCardID.Visible = True And Me.BankID.
Visible = False Then
strMsg = "Tanscation Main Form" & vbNewLine & _
" Is Not Completed " & vbNewLine & "Credit Card Name Is Required" & _
" Click 'OK' To Select Credit Card Name"
strTitle = "Credit Card ID Is Missing"
intStyle = vbOKOnly + vbInformation
MsgBox strMsg, intStyle, strTitle
Me.CreditCardID.SetFocus
End
End If


If Dirk’s Code fncRequiredVisibleMissing(Me) some how recognized
TransactionTypeID # 8
On Form Name = BTranscationSubFm then I guess we can fix this ??


I really appreciate your time with this mess.
Thanks
H.Alam

 

[Jeanette Cunningham]

What controls when CreditCardID is visible ?
What controls when BankID is visible?

Controls are visible or hide only with TranscationTypeID update not with
Bank or CCard ID's
If and else is working great for all of the 10 options with
TranscationTypeID

Now select one is also working well with Got Focus on Bank And CreditID

If TransactionTypeID # 8 Fees And Other Charges Then

All below are visible = True

Transcation ID Auto #
Transcation Date ( Text Box)
TranscationTypeID = # 8 Fees And Other Charges (combo box)
BanckID (Combo Box) **Required** >>[ Dirk's Code trigger] !!! Problem !!!
CreditID ( Combo Box) *Required* >> [My Before Update event trigger] !!!
Problem !!!
Bank/CreditCardFees ( Text Box )
Wor is A label indicator Caption " Select Only One Bank Or CreditCard"
[visible only TransID # 8]


The Other 9 Options work out well for visible / hide and required fields
using
If and Else and
Form Before Update Is Null for CreditCardID &
All other required fields are working with Dirk's Code

If both CreditCardID And BankID are visible,
On the Enter event for CreditCardID, you can go

If Len(Me.BankID & vbNullString) >0 Then
Me.CreditCardID.Locked = True
MsgBox "You can only have one"
End If


The above code stops user from putting entries in both credit card and
bank.
This makes your validation code simpler.

If Both are selected, I use Got focus On Both CreditCardID And BankID, it
works well to
prevent update on both of them at the same time.

Would you summarize the requirements for TransactionTypeID # 8 Fees And
Other Charges

The only difference is when TransactionTypeID # 8 ' Fees And Other
Charges '
is selected
only one field is required not both and both field are visible ( BankID
And
CreditCardID )
at the same time .

?? Required Field Codes for TransactionTypeID # 8 are conflicting with
each
other ??



Dirk's Code >> Cancel = fncRequiredVisibleMissing(Me) worked with visible
&
tag property Required
and BankID tag property = Required for all .!! If we can stop code only
with
TransTypeID # 8 !!

My Code for CreditCardID >>[tag property is blank]
Worked with Form Before Update like :

If IsNull(Me.CreditCardID) And Me!CreditCardID.Visible = True And
Me.BankID.
Visible = False Then
strMsg = "Tanscation Main Form" & vbNewLine & _
" Is Not Completed " & vbNewLine & "Credit Card Name Is Required" & _
" Click 'OK' To Select Credit Card Name"
strTitle = "Credit Card ID Is Missing"
intStyle = vbOKOnly + vbInformation
MsgBox strMsg, intStyle, strTitle
Me.CreditCardID.SetFocus
End
End If


If Dirk's Code fncRequiredVisibleMissing(Me) some how recognized
TransactionTypeID # 8
On Form Name = BTranscationSubFm then I guess we can fix this ??


I really appreciate your time with this mess.
Thanks
H.Alam

 

[Jeanette Cunningham]

Sorry about the blank post.

One thing you can do, is use vba code to set the tag property for BankID and
CreditCardID.
Depending on the value of TranscationTypeID, you can change the tag - make
it an empty string or set it to whatever you want.

It would be easier if Dirk's Code trigger and My Before Update event trigger
could be combined into one sub or function.
See if you can combine them.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

What controls when CreditCardID is visible ?
What controls when BankID is visible?

Controls are visible or hide only with TranscationTypeID update not with
Bank or CCard ID's
If and else is working great for all of the 10 options with
TranscationTypeID

Now select one is also working well with Got Focus on Bank And CreditID

If TransactionTypeID # 8 Fees And Other Charges Then

All below are visible = True

Transcation ID Auto #
Transcation Date ( Text Box)
TranscationTypeID = # 8 Fees And Other Charges (combo box)
BanckID (Combo Box) **Required** >>[ Dirk's Code trigger] !!! Problem !!!
CreditID ( Combo Box) *Required* >> [My Before Update event trigger] !!!
Problem !!!
Bank/CreditCardFees ( Text Box )
Wor is A label indicator Caption " Select Only One Bank Or CreditCard"
[visible only TransID # 8]


The Other 9 Options work out well for visible / hide and required fields
using
If and Else and
Form Before Update Is Null for CreditCardID &
All other required fields are working with Dirk's Code

If both CreditCardID And BankID are visible,
On the Enter event for CreditCardID, you can go

If Len(Me.BankID & vbNullString) >0 Then
Me.CreditCardID.Locked = True
MsgBox "You can only have one"
End If


The above code stops user from putting entries in both credit card and
bank.
This makes your validation code simpler.

If Both are selected, I use Got focus On Both CreditCardID And BankID, it
works well to
prevent update on both of them at the same time.

Would you summarize the requirements for TransactionTypeID # 8 Fees And
Other Charges

The only difference is when TransactionTypeID # 8 ' Fees And Other
Charges '
is selected
only one field is required not both and both field are visible ( BankID
And
CreditCardID )
at the same time .

?? Required Field Codes for TransactionTypeID # 8 are conflicting with
each
other ??



Dirk's Code >> Cancel = fncRequiredVisibleMissing(Me) worked with visible
&
tag property Required
and BankID tag property = Required for all .!! If we can stop code only
with
TransTypeID # 8 !!

My Code for CreditCardID >>[tag property is blank]
Worked with Form Before Update like :

If IsNull(Me.CreditCardID) And Me!CreditCardID.Visible = True And
Me.BankID.
Visible = False Then
strMsg = "Tanscation Main Form" & vbNewLine & _
" Is Not Completed " & vbNewLine & "Credit Card Name Is Required" & _
" Click 'OK' To Select Credit Card Name"
strTitle = "Credit Card ID Is Missing"
intStyle = vbOKOnly + vbInformation
MsgBox strMsg, intStyle, strTitle
Me.CreditCardID.SetFocus
End
End If


If Dirk's Code fncRequiredVisibleMissing(Me) some how recognized
TransactionTypeID # 8
On Form Name = BTranscationSubFm then I guess we can fix this ??


I really appreciate your time with this mess.
Thanks
H.Alam

 

[Alam via AccessMonster.com]

What controls when CreditCardID is visible ?
Controls Visibility Is NOT CHANGE WITH BANK ID OR CRIDIT CARD ID.

All related controls with TransactionTypeID # 8 Are visible
These controls are visible when TransactionTypeID # 8 is selected.

1) Transcation ID
2) Transcation Date
3) TranscationTypeID # 8 “ Fees And Other Charges “
4) BanckID
5) CreditID
6) Bank/CreditCardFees
7) Wor is A label indicator

Hope this time I made it clear

It would be easier if Dirk's Code trigger and My Before Update event trigger
could be combined into one sub or function.
See if you can combine them.

How ?


Regards,
H.Alam

 

[Alam via AccessMonster.com]

One thing you can do, is use vba code to set the tag property for BankID and

CreditCardID.
It would be easier if Dirk's Code trigger and My Before Update event trigger
could be combined into one sub or function.
See if you can combine them.

I try used Dirk’s Code as a routine instead of module only for this form
Like:
Private Sub TransRequiredFields()

Cut/ Past Drick’s Code and

I changed all ctl to Form.Controls

Some minor adjustment and call this from Before update event
Like:
Call TransReruiedFields

It is working Okay for all required field with tag Property trigger the code
if blank

So I Abe to pull the code in this form, now there is a possibility that I can
put
TransactionTypeID # 8 Rule / Condition some where in the code .
Like :

If Me.TranscationTypeID = 8 Then

Stop This Code and evaluate this :

If IsNull(Me.BankID) Or Me CreditCardID= ҠThen
Msgbox “Data required field can not be left blank select one “

If Bank seleced then lock credit card
Msg “you already selected Bankâ€

The question is How?? and Where ??