Hope someone can help me with this...trying to keep invalid or dupes on records

[sparks]

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_BeforeUpdate(Cancel As Integer)
Cancel = ckContinue("AcntNumber")
End Sub

Private Sub PersonID_BeforeUpdate(Cancel As Integer)
Cancel = ckContinue("personid")
End Sub

================================================== ==

Public Function ckContinue(cname As String) As Boolean

Dim rst As Recordset
Dim str As String
Dim delim As String

If IsNull(Me.Controls(cname).Value) Then
MsgBox " You Must put in a valid AcntNumber to continue"
ActiveControl.Undo
DoCmd.RunCommand acCmdUndo
Else
str = Me.Controls(cname).Value
Set rst = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
If rst.Fields(cname).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.Undo
DoCmd.RunCommand acCmdUndo
Else
Exit Function
End If
rst.Close
End If
End Function

[Lyle Fairfield]

Make a unique index or indexes, or a unique non-null index or indexes
as the case requires. Disallow zero length strings if you wish. Let
Access/Jet handle the rest, including messages etc.
Tell "they" that's the way it is.
Sleep soundly; all is under control.

[Bob Quintal]

sparks <js******@swbell.netwrote in
news:1o********************************@4ax.com:

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 can have only one account, and that can't be a joint
account with my significant othre. If this is for a bank, tell
me which one, so I can avoid doing business there.

so I keep fixing one way and another way doesn't work right.

If the personID and accountID are both unique, one ID is
redundant.

Anyways, tackle the two problems individually, your code is much
more convoluted than it need be

In the Before_Update Event of the account number, simply do a

if NOT isnull(dlookup("accountID", _
"tableName", _
accountID = " & me.accountID & ")" then
cancel = true
msgbox "Duplicate Account Number Not Allowed"
end if

Use the same concept in the PersonID's BeforeUpdate event

>
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

Good luck.

>
================================================== ==
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_BeforeUpdate(Cancel As Integer)
Cancel = ckContinue("AcntNumber")
End Sub

Private Sub PersonID_BeforeUpdate(Cancel As Integer)
Cancel = ckContinue("personid")
End Sub

================================================== ==

Public Function ckContinue(cname As String) As Boolean

Dim rst As Recordset
Dim str As String
Dim delim As String

If IsNull(Me.Controls(cname).Value) Then
MsgBox " You Must put in a valid AcntNumber to continue"
ActiveControl.Undo
DoCmd.RunCommand acCmdUndo
Else
str = Me.Controls(cname).Value
Set rst = CurrentDb.OpenRecordset(Me.RecordSource,
dbOpenDynaset) If rst.Fields(cname).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.Undo
DoCmd.RunCommand acCmdUndo
Else
Exit Function
End If
rst.Close
End If
End Function

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[sparks]

Thanks Bob I will give this a go.

At first it was an account No.
Then they came up with the Person ID
It made no since to me since they were the same person.
Just 2 different ways to identify them.
They said oh 2 different departments...one is assigning the account
number. then the other dept assigns them a person ID.
Like you said this is 2 numbers for the same person but that is what
they want.

I will check the act num and if its ok then move on to the person num.

the problem is with something like this

act num person num
1 1
2 18

new record they type in
3 1
on one I tell them that its used. ok yours works fine

now they go to existing record of 1 1 and type
2 as soon as they do I have to set it back to 1
this yours will do also...
man I have mentally walked thru this a hundred times it kinda makes
since..but like you...WHY 2 ROFL

oh well its friday so I can put up the machine gun and go get a beer
Jerry


On 14 Jul 2006 18:59:05 GMT, Bob Quintal <rq******@sPAmpatico.ca>
wrote:

>sparks <js******@swbell.netwrote in
news:1o********************************@4ax.com :

>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 can have only one account, and that can't be a joint
account with my significant othre. If this is for a bank, tell
me which one, so I can avoid doing business there.

>so I keep fixing one way and another way doesn't work right.

If the personID and accountID are both unique, one ID is
redundant.

Anyways, tackle the two problems individually, your code is much
more convoluted than it need be

In the Before_Update Event of the account number, simply do a

if NOT isnull(dlookup("accountID", _
"tableName", _
accountID = " & me.accountID & ")" then
cancel = true
msgbox "Duplicate Account Number Not Allowed"
end if

Use the same concept in the PersonID's BeforeUpdate event

>>
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

Good luck.

>>
================================================= ===
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_BeforeUpdate(Cancel As Integer)
Cancel = ckContinue("AcntNumber")
End Sub

Private Sub PersonID_BeforeUpdate(Cancel As Integer)
Cancel = ckContinue("personid")
End Sub

================================================= ===

Public Function ckContinue(cname As String) As Boolean

Dim rst As Recordset
Dim str As String
Dim delim As String

If IsNull(Me.Controls(cname).Value) Then
MsgBox " You Must put in a valid AcntNumber to continue"
ActiveControl.Undo
DoCmd.RunCommand acCmdUndo
Else
str = Me.Controls(cname).Value
Set rst = CurrentDb.OpenRecordset(Me.RecordSource,
dbOpenDynaset) If rst.Fields(cname).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.Undo
DoCmd.RunCommand acCmdUndo
Else
Exit Function
End If
rst.Close
End If
End Function

--
Bob Quintal

PA is y I've altered my email address.