PipMic
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
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
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"
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
ASKER
hi Rgonzo,
I get the following error: the bold bit shows up as yellow
Private Sub PV_Number_BeforeUpdate(Can cel 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 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
Error message?
ASKER
Please note that I am using A97 or A2003
ASKER
Sorry not an error message but the code module appears with the yellow band where the bold is.
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
ASKER
I am getting Compile Error , Argument not optional
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.
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
it was sent as A97. I assumed it could be converted to A2003. Please let me know if you can't convert it.
Thanks
ASKER
I am sending it in so that it can be opened via A2003
Test2003.mdb
Test2003.mdb
ASKER
how do I close a question?
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Thanks for the patience.... greatly appreciated.
glad I could help
pls try
Open in new window
Regards