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
PipMicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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
PipMicAuthor Commented:
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"
Rgonzo1971Commented:
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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PipMicAuthor Commented:
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
Rgonzo1971Commented:
Error message?
PipMicAuthor Commented:
Please note that I am using A97 or A2003
PipMicAuthor Commented:
Sorry not an error message but the code module appears with the yellow band where the bold is.
PipMicAuthor Commented:
gosh, I am reading the code and looks fine but somehow it doesn't allow it!!
Rgonzo1971Commented:
sorry can't help further maybe you should close the question and reopen it
PipMicAuthor Commented:
I am getting Compile Error , Argument not optional
PipMicAuthor Commented:
Perhaps another expert can help?
Rgonzo1971Commented:
Like I said at best close it and reopen it
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
PipMicAuthor Commented:
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
PipMicAuthor Commented:
I am sending it in so that it can be opened via A2003
Test2003.mdb
PipMicAuthor Commented:
how do I close a question?
PipMicAuthor Commented:
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 FyeOwner, Developing Solutions LLCCommented:
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

PipMicAuthor Commented:
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
Dale FyeOwner, Developing Solutions LLCCommented:
I don't understand why that would be causing problems, we reference that control elsewhere in the code and it does not seem to cause a problem.  Most professional developers use a prefix in the name of the controls to distinguish between the control and the underlying field value.  If you fail to do that, it can be confusing.  For that reason, I would recommend that you go back and rename the controls associated with your PV_Number and Code textboxes to txt_PV_Number and txt_Code.
Private Sub Form_BeforeUpdate(Cancel As Integer)

     If Me.txt_PV_Number & "" = "" Then
        MsgBox "You must make an entry in the 'PV Number' field!"
        Me.txt_PV_Number.SetFocus
        Cancel = True
    ElseIf Not (Me.txt_PV_Number Like "[PpRr]*") Then
        MsgBox "'PV Number' 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 PV Number begins with 'P' or 'p' then the 'Code' field cannot begin with an 'R'!"
        Me.txt_Case.SetFocus
        Cancel = True
    End If

End Sub 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PipMicAuthor Commented:
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
PipMicAuthor Commented:
Thanks for the patience.... greatly appreciated.
Dale FyeOwner, Developing Solutions LLCCommented:
glad I could help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.