Link to home
Start Free TrialLog in
Avatar of PipMic
PipMicFlag for Gibraltar

asked on

Extra Validation

Hi,
I am trying to add extra validation in a sample db (done in A97 see attached)
I am attempting the following: Please not that the db has existing validation which I would like to keep (or change if advised) 

Conditions:
1.      Input into field “ID” must start with the letter P or p or R or r
2.      If field  “ID” starts with the letter P or p and field “Code” starts with the letter R or r, then a message stating that this combination is invalid should appear
3.      If field  “ID” starts with the letter R or r and field “Code”] starts with the letter P or p, then a message stating that this combination is invalid should appear


Grateful for assistance. Thanks
Test.mdb
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try

Private Sub PV_Number_BeforeUpdate(Cancel As Integer)
    If UCase$(Left$(PV_Number), 1) = "R" Or UCase$(Left$(PV_Number), 1) = "P" Then
        If UCase$(Left$(I___D_Fund), 1) = "R" Or UCase$(Left$(PV_Number), 1) = "P" Or _
                UCase$(Left$(I___D_Fund), 1) = "P" Or UCase$(Left$(PV_Number), 1) = "R" Then
            MsgBox "ID and Code combination not valid"
            Cancel = True
        End If
        
    Else
        MsgBox "ID should begin with 'R' or 'P'"
        Cancel = True
    End If
End Sub

Open in new window

Regards
Avatar of PipMic

ASKER

I am also  trying the following within my previous validation the following which might complicate matters.

I have a table , tbl_flagged , of numbers which refer to a code.

In my input form, when I enter a number in the field “code” and press enter, I would like it to check my tbl_flagged to see if it exists there and if it does prevent it from accepting it perhaps with a message box appearing saying that “this code is flagged” .

I have been  trying with DLookUp but having no success

Dim flagged As String
flagged = if(Dlookup("[I & D Fund]","tbl_flagged "= Forms![frm_flagged]![I & D Fund]"))Then
MsgBox "Not Allowed"
Maybe

Private Sub I___D_Fund_BeforeUpdate(Cancel As Integer)
    If DLookup("[I & D Fund]", "tbl_flagged", "[I & D Fund] = " & I___D_Fund) = I___D_Fund Then
        MsgBox "Not Allowed"
        Cancel = True
    End If
Field67 = ""
End Sub

Open in new window

Avatar of PipMic

ASKER

hi Rgonzo,

I get the following error: the bold bit shows up as yellow

Private Sub PV_Number_BeforeUpdate(Cancel As Integer)
If UCase$(Left$(PV_Number), 1) = "R" Or UCase$(Left$(PV_Number), 1) = "P" Then
        If UCase$(Left$(I___D_Fund), 1) = "R" Or UCase$(Left$(PV_Number), 1) = "P" Or _
                UCase$(Left$(I___D_Fund), 1) = "P" Or UCase$(Left$(PV_Number), 1) = "R" Then
            MsgBox "ID and Code combination not valid"
            Cancel = True
        End If
       
    Else
        MsgBox "ID should begin with 'R' or 'P'"
        Cancel = True
    End If

End Sub
Error message?
Avatar of PipMic

ASKER

Please note that I am using A97 or A2003
Avatar of PipMic

ASKER

Sorry not an error message but the code module appears with the yellow band where the bold is.
Avatar of PipMic

ASKER

gosh, I am reading the code and looks fine but somehow it doesn't allow it!!
sorry can't help further maybe you should close the question and reopen it
Avatar of PipMic

ASKER

I am getting Compile Error , Argument not optional
Avatar of PipMic

ASKER

Perhaps another expert can help?
Like I said at best close it and reopen it
I can't open the database using 2013, but make sure you don't already have another Private Sub <your controlname>_BeforeUpdate for that module.
Avatar of PipMic

ASKER

Hi Scott,

it was sent as A97. I assumed it could be converted to A2003. Please let me know if you can't convert it.

Thanks
Avatar of PipMic

ASKER

I am sending it in so that it can be opened via A2003
Test2003.mdb
Avatar of PipMic

ASKER

how do I close a question?
Avatar of PipMic

ASKER

Hi all,

Thank you all for your contributions but I am no nearer the solution.

I have thought perhaps it best if I break the problem into some steps:


1. If  Input into field “ID” must start with the letter P or p or R or r
2. If field  “ID” starts with the letter P and field “Code” starts with the letter R then a message stating that this combination is invalid should appear.

If someone assists me with 1 and 2 maybe then I can replicate (2) in order to meet with my objective.

Thanks
I generally do not put code in the BeforeUPdate event of particular controls.  Instead, I use the Form_BeforeUPdate event.

Private Sub Form_BeforeUPdate(cancel as Integer)

    If me.ID & "" = "" THEN
        msgbox "You must make an entry in the ID field!"
        me.Id.SetFocus
        Cancel = true
    Elseif (me.ID NOT LIKE "[PpRr]*") Then
        msgbox "ID must begin with 'P', 'p', 'R', or 'r'!"
        me.Id.SetFocus
        Cancel = true
    Elseif (me.ID Like "[Pp]") and (me.Code LIKE "[Rr]*") THEN
        msgbox "IF ID begins with 'P' or 'p' then the 'Code' field cannot begin with an 'R'!"
        me.Case.SetFocus
        Cancel = true
    End If

End Sub

Open in new window

Avatar of PipMic

ASKER

Hi Dale,

Thanks for your assistance. I have managed to recode it very slightly as shown below. However I am still getting : Compile Error, Method or Data member not found

and it stops on the bold.

Grateful if you can assist further. Thanks

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.PV_Number & "" = "" Then
        MsgBox "You must make an entry in the ID field!"
        Me.PV_Number.SetFocus
        Cancel = True
    ElseIf Not (Me.PV_Number Like "[PpRr]*") Then
        MsgBox "ID must begin with 'P', 'p', 'R', or 'r'!"
        Me.PV_Number.SetFocus
        Cancel = True
    ElseIf (Me.PV_Number Like "[Pp]*") And (Me.Code Like "[Rr]*") Then
        MsgBox "IF PV Number begins with 'P' or 'p' then the 'Code' field cannot begin with an 'R'!"
        Me.Case.SetFocus
        Cancel = True
    End If

End Sub
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PipMic

ASKER

Hi, sorry for the delay...slightly modified as suggested and so far it works as planned.....

Thanks very much.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.txt_PV_Number & "" = "" Then
        MsgBox "You must make an entry in the ID field!"
        Me.txt_PV_Number.SetFocus
        Cancel = True
    ElseIf Not (Me.txt_PV_Number Like "[PpRr]*") Then
        MsgBox "ID must begin with 'P', 'p', 'R', or 'r'!"
        Me.txt_PV_Number.SetFocus
        Cancel = True
    ElseIf (Me.txt_PV_Number Like "[Pp]*") And (Me.txt_Code Like "[Rr]*") Then
        MsgBox "IF txt_PV_Number begins with 'P' or 'p' then the 'Code' field cannot begin with an 'R'!"
        Me.txt_Code.SetFocus
        Cancel = True
    End If

End Sub
Avatar of PipMic

ASKER

Thanks for the patience.... greatly appreciated.
glad I could help