Extra Validation

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

Select all Open in new window

Regards

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"

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

Select all Open in new window

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

Error message?

Please note that I am using A97 or A2003

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

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

I am getting Compile Error , Argument not optional

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.

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

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

how do I close a question?

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

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

Select all Open in new window

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

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 for the patience.... greatly appreciated.

glad I could help