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
Microsoft AccessVisual Basic ClassicMicrosoft DevelopmentProgrammingMicrosoft Applications
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 IfEnd 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"
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 IfField67 = ""End Sub
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
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 IfEnd 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
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
pls try
Open in new window
Regards