Extra Validation

PipMic
PipMic used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
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

Author

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"
Top Expert 2016

Commented:
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

Ensure you’re charging the right price for your IT

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

Author

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
Top Expert 2016

Commented:
Error message?

Author

Commented:
Please note that I am using A97 or A2003

Author

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

Author

Commented:
gosh, I am reading the code and looks fine but somehow it doesn't allow it!!
Top Expert 2016

Commented:
sorry can't help further maybe you should close the question and reopen it

Author

Commented:
I am getting Compile Error , Argument not optional

Author

Commented:
Perhaps another expert can help?
Top Expert 2016

Commented:
Like I said at best close it and reopen it
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

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

Author

Commented:
I am sending it in so that it can be opened via A2003
Test2003.mdb

Author

Commented:
how do I close a question?

Author

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, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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

Author

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
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
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

Author

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

Author

Commented:
Thanks for the patience.... greatly appreciated.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
glad I could help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial