Avatar of PipMic
PipMic
Flag 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
Microsoft AccessVisual Basic ClassicMicrosoft DevelopmentProgrammingMicrosoft Applications

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
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
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"
Rgonzo1971

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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
Rgonzo1971

Error message?
PipMic

ASKER
Please note that I am using A97 or A2003
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PipMic

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

ASKER
gosh, I am reading the code and looks fine but somehow it doesn't allow it!!
Rgonzo1971

sorry can't help further maybe you should close the question and reopen it
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
PipMic

ASKER
I am getting Compile Error , Argument not optional
PipMic

ASKER
Perhaps another expert can help?
Rgonzo1971

Like I said at best close it and reopen it
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Scott McDaniel (EE MVE )

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.
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
PipMic

ASKER
I am sending it in so that it can be opened via A2003
Test2003.mdb
Your help has saved me hundreds of hours of internet surfing.
fblack61
PipMic

ASKER
how do I close a question?
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
Dale Fye

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
PipMic

ASKER
Thanks for the patience.... greatly appreciated.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Dale Fye

glad I could help