I Need your urgent support . Validation Rule and VBA Code (1 Viewer)

Ossama22 · Aug 31, 2018

Hello ,

I Have a Tabel which there are alot of cells , for Example i have 2 cells which i wanna to make a validation rule between them : Account Number & Clearance Code

i wanna to create a rule if the clearance code = any value , when i try to write any account number it shows a msg that we cannot add account number because there's a value in clearance code . is that possible in Microsoft Access Table ?

Or if any one can give me VBA cod to do that in form view : when i try to write in account number cell while Clearance code is not null , it shows msg box , can any one help ! Urgently

Another ask , i wanna code : if Clearance Code = HSBC then Account number didnt accept number more than 11 digits , if it greater than or less than that shows msg thacc acc number is wrong
Thx for your support

Ranman256 · Aug 31, 2018

instead of validation rule, put code in the form, so users cannot edit if theres a ClearanceCode:

Code:

sub form_OnCurrent()
   txtAcctNum.locked = not IsNull(txtClearCode)
end sub

user wont be able to write in the acct# if theres a code.

The_Doc_Man · Aug 31, 2018

Ossama, use Ranman's method because validation rules generally do not cross fields. That is, the validation rule ONLY names the one field most of the time. For beginners and even for intermediates, a more complex validation rule is not generally allowed (i.e. one that references another field.) Not impossible but very difficult to make happen, particularly where nulls are possible.

Ossama22 · Sep 1, 2018

Ranman256 said:
instead of validation rule, put code in the form, so users cannot edit if theres a ClearanceCode:

Code:

sub form_OnCurrent() txtAcctNum.locked = not IsNull(txtClearCode) end sub

user wont be able to write in the acct# if theres a code.

First of all , thx for your support , but let me tell you something , iam still beginner in dealing with access its all based on self study , so i will attach 3 photos for , Form View and Design view also the code , i tried it but it didnt work . so maybe this photos can help you to give me a specific code to use , sorry for being annoying

arnelgp · Sep 1, 2018

here is your validation for [Account Number] textbox (Change Event) on Form.
arrange your textboxes so that clerance get inputted first.

Code:

Private Sub Account_Number_Change()
    Static bolHandled As Boolean
    Dim strClearanceCode As String
    Dim strAccountNumber As String
    strClearanceCode = Me![Clearance Code] & ""
    strAccountNumber = Me![Account Number].Text & ""
    If strClearanceCode = "HSBC" Then
        If Len(strAccountNumber) > 11 Then
            MsgBox "Account Number cannot be more than 11 digits."
            SendKeys "{BACKSPACE}"
        End If
        
    Else
        If strClearanceCode <> vbNullString Then
            If bolHandled = False Then
                MsgBox "You cannot add Account Number because there is a value in the Clearance Code."
                SendKeys "{BACKSPACE}"
                bolHandled = True
                
            Else
                bolHandled = False
                
            End If
        End If
    
    End If
End Sub

Ossama22 · Sep 1, 2018

arnelgp said:

here is your validation for [Account Number] textbox (Change Event) on Form.
arrange your textboxes so that clerance get inputted first.

Code:

Private Sub Account_Number_Change()
    Static bolHandled As Boolean
    Dim strClearanceCode As String
    Dim strAccountNumber As String
    strClearanceCode = Me![Clearance Code] & ""
    strAccountNumber = Me![Account Number].Text & ""
    If strClearanceCode = "HSBC" Then
        If Len(strAccountNumber) > 11 Then
            MsgBox "Account Number cannot be more than 11 digits."
            SendKeys "{BACKSPACE}"
        End If
        

    Else
        If strClearanceCode <> vbNullString Then
            If bolHandled = False Then
                MsgBox "You cannot add Account Number because there is a value in the Clearance Code."
                SendKeys "{BACKSPACE}"
                bolHandled = True
                
            Else
                bolHandled = False
                
            End If
        End If
    
    End If
End Sub

Thx for your efforts , i tried that but it seems that smth is wrong as shows attached , it seems that it's a hopeless case , iam sorry for this disturbance

arnelgp · Sep 1, 2018

try adding space.

Me![Clearance Code]

or me. clearance_code

Cronk · Sep 1, 2018

Ossama, check the name of the control - Properties in design mode.

Ossama22 · Sep 2, 2018

arnelgp said:
try adding space.

Me![Clearance Code]

or me. clearance_code

I Tried it bro still not working , any help plz ?

arnelgp · Sep 3, 2018

can yiu zip a sample db and uoliaf.

arnelgp · Sep 3, 2018

here is a sample. if still not working upload a zip sample.

I Need your urgent support . Validation Rule and VBA Code (1 Viewer)

Ossama22

Registered User.

Ranman256

Well-known member

The_Doc_Man

Immoderate Moderator

Ossama22

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Ossama22

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Cronk

Registered User.

Ossama22

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

Attachments

Similar threads

Users who are viewing this thread