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!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
pls try
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
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"
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
hi Rgonzo,
I get the following error: the bold bit shows up as yellow
Private Sub PV_Number_BeforeUpdate(Can
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
I get the following error: the bold bit shows up as yellow
Private Sub PV_Number_BeforeUpdate(Can
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
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.
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
it was sent as A97. I assumed it could be converted to A2003. Please let me know if you can't convert it.
Thanks
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
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
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
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
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial