I have 2 fields that I have to make sure don't get screwed up.
(could be one it doesn't matter)
but what they want is no duplicates, warn the user
changing existing numbers is a no no. (of course they will have to
change errors so this is kinda WHAT ??)
the way it is set up is they put in an account number then a person id
number (which is text LOL)
ok if existing acount number then set it back to whatever and warn
them.
But I can't get all the checks to work at the same time, new record
can't equal old record in acount number or person id... BOTH are
unique.
so I keep fixing one way and another way doesn't work right.
are there any geniuses out there that can tell me what I need to
modify to catch existing value on entry as well as changing value to
existing values.
thanks big time for help I am just going in circles
Jerry
=============== =============== =============== =======
so what I did was this on before update of the 2 fields...
trying acount number and it if passes going to person id then checking
and if it passes ok good record...If not setting back to whatever
Private Sub AcntNumber_Befo reUpdate(Cancel As Integer)
Cancel = ckContinue("Acn tNumber")
End Sub
Private Sub PersonID_Before Update(Cancel As Integer)
Cancel = ckContinue("per sonid")
End Sub
=============== =============== =============== =======
Public Function ckContinue(cnam e As String) As Boolean
Dim rst As Recordset
Dim str As String
Dim delim As String
If IsNull(Me.Contr ols(cname).Valu e) Then
MsgBox " You Must put in a valid AcntNumber to continue"
ActiveControl.U ndo
DoCmd.RunComman d acCmdUndo
Else
str = Me.Controls(cna me).Value
Set rst = CurrentDb.OpenR ecordset(Me.Rec ordSource, dbOpenDynaset)
If rst.Fields(cnam e).Type = dbText Then
delim = """"
Else
delim = ""
End If
rst.FindFirst "[" & cname & "] = " & delim & str & delim
If Not rst.NoMatch Then
MsgBox "A record with AcntNumber = " & rst!AcntNumber & "
ID number = " & rst!idnum & " already exists"
ActiveControl.U ndo
DoCmd.RunComman d acCmdUndo
Else
Exit Function
End If
rst.Close
End If
End Function