Field validation

[ah]

Hi;

Appreciate if someone could help me with the following:

I've 2 text fields called "Bank Name" & "Bank Account Number" in my form now.
Can anyone guide me on how to add the the following validation checking into
my form?

a) When Bank A is being selected, in the Bank Name field, check whether the
user has key in 10 digits into the Bank Account Number field. If the user has
key in something > or < than 10 digits, prompt out an error message that ask
them to key in the correct bank account number
b) When Bank B is being selected, check whether the user has key in 12
digits into the Bank Account Number field.
and etc

 

[Greg Maxey]

ah,

Something like this should do. Run the onExit macro from the
Bank_Account_Number field. Run the onEntry macro from all fields:

Option Explicit
Private mstrFF As String
Sub aFFOnExit()
With GetCurrentFF
Select Case ActiveDocument.FormFields("Bank_Name").Result
Case Is = "A"
If Len(.Result) <> 10 Or Not IsNumeric(.Result) Then
MsgBox "Your message"
mstrFF = .Name
End If
Case Is = "B"
If Len(.Result) <> 12 Or Not IsNumeric(.Result) Then
MsgBox "Your message"
mstrFF = .Name
End If
End Select
End With
End Sub
Public Sub AOnEntry()
Dim strCurrentFF As String
If LenB(mstrFF) > 0 Then
ActiveDocument.FormFields(mstrFF).Select
mstrFF = vbNullString
End If
End Sub
Private Function GetCurrentFF() As Word.FormField
With Selection
If .FormFields.Count = 1 Then
Set GetCurrentFF = .FormFields(1)
ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
Set GetCurrentFF = ActiveDocument.FormFields _
(.Bookmarks(.Bookmarks.Count).Name)
End If
End With
End Function

 

[ah]

Hi Greg;

Thanks for your kind help. I tried, but it doesn't work at my side. below
are the steps that I've performed, please advice whether I've done it
correctly

a) Create a macro called "BankCheckingMacro"
b) Copy your code as follows: (Note: I've changed to BankName in stead of
Bank_Name to follow my field's name

Option Explicit
Private mstrFF As String
Sub aFFOnExit()
With GetCurrentFF
Select Case ActiveDocument.FormFields("BankName").Result
Case Is = "Citibank(7214)"
If Len(.Result) <> 10 Or Not IsNumeric(.Result) Then
MsgBox "Your message"
mstrFF = .Name
End If
Case Is = "Development-Bank-of-Singapore(7171)"
If Len(.Result) <> 12 Or Not IsNumeric(.Result) Then
MsgBox "Your message"
mstrFF = .Name
End If
End Select
End With
End Sub
Public Sub AOnEntry()
Dim strCurrentFF As String

If LenB(mstrFF) > 0 Then
ActiveDocument.FormFields(mstrFF).Select
mstrFF = vbNullString
End If
End Sub
Private Function GetCurrentFF() As Word.FormField
With Selection
If .FormFields.Count = 1 Then
Set GetCurrentFF = .FormFields(1)
ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
Set GetCurrentFF = ActiveDocument.FormFields _
(.Bookmarks(.Bookmarks.Count).Name)
End If
End With
End Function

c) Go to the "Bank_Account_Number" text field, select "AOnEntry" as the
Entry MAcro and "AffOnExit" as the exit macro

d) Tick "Calculate on exit"

Then, I tried to select the respective bank, but I did not get any error
prompt.

Please advice

 

[Greg Maxey]

ah,

You don't need to create a macro. The three macros you need were provided.

You need to open your document. Then open the VB Editor. You can paste the
code in the "This Document" module or create a new module in project.

I am not exactly sure what our sequence is. I assumed that you have a
dropdown with the entries "A" and "B." You select A or B then you go to the
AccountNumberField and enter the 10 or 12 digit number. If the number isn't
the right number of digits or not numeric then you want a message box and
the user to be trapped in the AccountNumberField until they get it right.

So you want to run the aFFOnExit Procedrue "on exit" from the
AccountNumberField and run the AOnEntry Procedure in all fields.

 

[ah]

Hi Greg;

Yup, I've copied your code into the module, and select "aOffOnExit" on the
exit field of my "Bank_Account_Number" and "AOnEntry" as the entry macro for
the next text field that I've, but it doesn't work and I did not get any
prompt when I key in <10 digits into the Bank_Account_Number ffield.

FYI, I've a user form that run at the BankName field as follows, is this the
issue?

Private Sub UserForm_Initialize()
Dim myArray1() As String 'AUSTRALIA
Dim myArray5() As String 'CHINA
Dim myArray8() As String 'HONG KONG
Dim myArray9() As String 'INDIA/SRI LANGKA/BANGLADESH
Dim myArray10() As String 'INDONESIA
Dim myArray11() As String 'JAPAN
Dim myArray12() As String 'KOREA
Dim myArray14() As String 'MALAYSIA
Dim myArray15() As String 'PHILIPPINES
Dim myArray16() As String 'SINGAPORE
Dim myArray17() As String 'TAIWAN
Dim myArray18() As String 'THAILAND
Dim myArray21() As String 'VIETNAM
Dim myArray22() As String 'NEW ZEALAND

Dim i As Long

myArray1 = Split("ADL-ADELAIDE-BANK-LIMITED ADV-ADVANCE-BANK-AUSTRALIA-LTD
ANZ-AUSTRALIA-AND-NEW-ZEALAND-BANKING-GROUP-LIMITED BBL-BENDIGO-BANK-LIMITED
BML-BANK-OF-MELBOURNE BQL-BANK-OF-QUEENSLAND-LIMITED
BSA-BANK-OF-SOUTH-AUSTRALIA-- BWA-BANK-OF-WESTERN-AUSTRALIA-LIMITED
CBA-COMMONWEALTH-BANK-OF-AUSTRALIA CMB-CHASE-MANHATTAN-BANK-(THE)
CRU-CREDIT-UNION-FINANCIAL-SERVICES--LIMITED CST-COLONIAL-STATE-BANK--
CTI-CITIBANK-LIMITED CUS-CREDIT-UNION-SETTLEMENT-SERVICES-LTD
GBS-GREATER-BUILDING-SOCIETY-LIMITED GIO-GIO-BUILDING-SOCIETY-LIMITED
HBA-HSBC-BANK-AUSTRALIA-LIMITED HBS-HERITAGE-BUILDING-SOCIETY-LIMITED
HOM-HOME-BUILDING-SOCIETY-LIMITED ING-ING-BANK-AUSTRALIA-LIMITED
MBL-MACQUARIE-BANK-LIMITED MET-METWAY-(SUNCORP-METWAY-BANK-LTD)
NAB-NATIONAL-AUSTRALIA-BANK-LIMITED STG-ST-GEORGE-BANK-LIMITED
WBC-WESTPAC-BANKING-CORPORATION ASB-ASB-BANK-LIMITED BNZ-BANK-OF-NEW-ZEALAND
NBNZ-THE-NATIONAL-BANK-OF-NEW-ZEALAND TSB-TSB-BANK-LIMITED")

'myArray5 = Split("NA")

'myArray8 = Split("NA)")

'myArray9 = Split("NA")

'myArray10 = Split("NA")

'myArray11 = Split("NA")

myArray12 = Split("Korea-Develpoment-Bank(02) Industrial-Bank-of-Korea(03)
Kook-Min-Bank(04) Korea-Exchange-Bank(05) Fisheries-Coorperatives(07)
Agri-(Joogang)(11) Agricultural-Cooperatives(12) Woori-Bank(20)
Scfirst-Bank(23) Citi-Bank(27) Daegu-Bank(31) Pusan-Bank(32) Kwangju-Bank(34)
Jeju-Bank(35) Jeon-Buk-Bank(37) KyoungNam-Bank(39)
Community-Credt-Cooperatives(45) Korea-Post(71) Hana-Bank(81) Shinhan(88)")

myArray14 = Split("Bank-Simpanan-National(BSN) Public-Bank(PBB) Citibank(CB)
Maybank(MBB) HongKong-Bank(HSBC) Commerce-Int.Merchant-Bank(CIMB))")

'myArray15 = Split("NA")

myArray16 = Split("Citibank(7214) Development-Bank-of-Singapore(7171)
HongKong&Shanghai-Banking-Co(7232) Keppel-Tatlee-Bank-of-Singapore(7029)
Oversea-Chinese-Banking-Corp-Ltd.(7339) Overseas-Union-Bank-Limited(7348)
Post-Office-Saving-Bank(7171081) Standard-Chartered-Bank(7144)
United-Overseas-Bank-Limited(7375)")

'myArray17 = Split("NA")

'myArray18 = Split("NA")

'myArray21 = Split("NA")

myArray22 = Split("ADL-ADELAIDE-BANK-LIMITED ADV-ADVANCE-BANK-AUSTRALIA-LTD
ANZ-AUSTRALIA-AND-NEW-ZEALAND-BANKING-GROUP-LIMITED BBL-BENDIGO-BANK-LIMITED
BML-BANK-OF-MELBOURNE BQL-BANK-OF-QUEENSLAND-LIMITED
BSA-BANK-OF-SOUTH-AUSTRALIA-- BWA-BANK-OF-WESTERN-AUSTRALIA-LIMITED
CBA-COMMONWEALTH-BANK-OF-AUSTRALIA CMB-CHASE-MANHATTAN-BANK-(THE)
CRU-CREDIT-UNION-FINANCIAL-SERVICES--LIMITED CST-COLONIAL-STATE-BANK--
CTI-CITIBANK-LIMITED CUS-CREDIT-UNION-SETTLEMENT-SERVICES-LTD
GBS-GREATER-BUILDING-SOCIETY-LIMITED GIO-GIO-BUILDING-SOCIETY-LIMITED
HBA-HSBC-BANK-AUSTRALIA-LIMITED HBS-HERITAGE-BUILDING-SOCIETY-LIMITED
HOM-HOME-BUILDING-SOCIETY-LIMITED ING-ING-BANK-AUSTRALIA-LIMITED
MBL-MACQUARIE-BANK-LIMITED MET-METWAY-(SUNCORP-METWAY-BANK-LTD)
NAB-NATIONAL-AUSTRALIA-BANK-LIMITED STG-ST-GEORGE-BANK-LIMITED
WBC-WESTPAC-BANKING-CORPORATION ASB-ASB-BANK-LIMITED BNZ-BANK-OF-NEW-ZEALAND
NBNZ-THE-NATIONAL-BANK-OF-NEW-ZEALAND TSB-TSB-BANK-LIMITED")

Me.BankName.Clear

Select Case ActiveDocument.FormFields("Country_Name").Result
Case "AUSTRALIA"
Me.BankName.AddItem "Please select one"
Me.BankName.ListIndex = 0
Me.BankName.List = myArray1

Case "CHINA"
Me.BankName.AddItem "Type your bank name here"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "HONG KONG"
Me.BankName.AddItem "Type your bank name here"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "INDIA/SRI LANGKA/BANGLADESH"
Me.BankName.AddItem "Please provide bank name and branch"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "INDONESIA"
Me.BankName.AddItem "Type your bank name here"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "JAPAN"
Me.BankName.AddItem "Please fill up Bank Update form"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "KOREA"
Me.BankName.AddItem "Please select one"
Me.BankName.ListIndex = 0
Me.BankName.List = myArray12

Case "MALAYSIA"
Me.BankName.AddItem "Please select one"
Me.BankName.ListIndex = 0
Me.BankName.List = myArray14

Case "PHILIPPINES"
Me.BankName.AddItem "Please provide bank name and branch"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "SINGAPORE"
Me.BankName.AddItem "Please from the list"
Me.BankName.ListIndex = 0
Me.BankName.List = myArray16

Case "TAIWAN"
Me.BankName.AddItem "Type your bank name here. Bank Name (Code) –
Branch Name (Code)"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "THAILAND"
Me.BankName.AddItem "Type your bank name here. Provide Branch code:
BBLxxx.TFBxxx,SCBxxx,BAYxxx"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "VIETNAM"
Me.BankName.AddItem "Please provide bank name and branch"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "NEW ZEALAND"
Me.BankName.AddItem "Please select one"
Me.BankName.ListIndex = 0
Me.BankName.List = myArray22


End Select
End Sub

Private Sub OK_Click()
ActiveDocument.FormFields("BankName").Result = Me.BankName.Text
ActiveDocument.Bookmarks("BankName").Range.Fields(1).Result.Select
Unload Me
End Sub


Private Sub Reset_Click()


ActiveDocument.FormFields("BankName").Result = " "
ActiveDocument.Bookmarks("BankName").Range.Fields(1).Result.Select
Unload Me
End Sub

 

[Greg Maxey]

ah,

The little snippet of code I sent to you works here, but if it doesn't work
for you something in your complete code or the way it is being applied is at
fault. I really can't say what without seeing it and if it didn't provide
you with enough information to work through it yourself then you will have
to let me have a look at the whole thing. I will probably have some time
later today so if you will e-mail it to me I will have a look.