primary text field checking for dupes after text field is updated

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using a single table with a primary key field Account_ no dupes, on a new
record in a form (Form name = "CaseData"), how can I get the message which
pops up after the record is complete that the Account_ you are your entering
is a duplicate, to come up after the field is updated.

My thought is that this would be a...
-------------------------------------------------
Private Sub ACCOUNT__AfterUpdate()
me.[account_].requery
end sub
-------------------------------------------------
This does not work for me. Why? I'm using MS Access 2000.
Is it becuase the Account_ field is a primary key?


Sugada
 
Sugada said:
I'm using a single table with a primary key field Account_ no dupes, on a new
record in a form (Form name = "CaseData"), how can I get the message which
pops up after the record is complete that the Account_ you are your entering
is a duplicate, to come up after the field is updated.

My thought is that this would be a...
Click to expand...


You should use the control's BeforeUpdate event to see if
the account is already in the table:

If Not IsNull(DLookup("Account_", "thetable", _
"Account_=" & Me.txtAccount)) Then
MsgBox "Duplicate"
Me.Account_.Undo
Cancel = True
End If
 
Thanks but I'm getting an error after using that code.

Compile error:
Method or data member not found.

My table name is "CaseData" so my final code was this:
___________________________________________________________
Private Sub ACCOUNT__BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("Account_", "CaseData", "Account_=" &
Me.txtAccount)) Then
MsgBox "Duplicate"
Me.ACCOUNT_.Undo
Cancel = True
End If

End Sub
__________________________________________________________
 
Sorry, I used txtAccount for the account number text box.
Probably some kind of unconscious reaction to your funky
underscore names ;-)

Change the DLookup to use:
. . . , "Account_=" & Me.Account_
--
Marsh
MVP [MS Access]

Thanks but I'm getting an error after using that code.

Compile error:
Method or data member not found.

My table name is "CaseData" so my final code was this:
___________________________________________________________
Private Sub ACCOUNT__BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("Account_", "CaseData", "Account_=" &
Me.txtAccount)) Then
MsgBox "Duplicate"
Me.ACCOUNT_.Undo
Cancel = True
End If

End Sub
__________________________________________________________
Click to expand...
 
Thanks. FYI, I imported that funky name from an 97 Approach DB and didn't
change the names. I also couldn't get the .undo to undo the do. So I took it
out replaced it with a do cmd.
____________________________________
'Me.ACCOUNT_.Undo
DoCmd.Close
DoCmd.OpenForm ("CaseData")
____________________________________
Thanks again for all your help. Sugada!

Marshall Barton said:
Sorry, I used txtAccount for the account number text box.
Probably some kind of unconscious reaction to your funky
underscore names ;-)

Change the DLookup to use:
. . . , "Account_=" & Me.Account_
--
Marsh
MVP [MS Access]

Thanks but I'm getting an error after using that code.

Compile error:
Method or data member not found.

My table name is "CaseData" so my final code was this:
___________________________________________________________
Private Sub ACCOUNT__BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("Account_", "CaseData", "Account_=" &
Me.txtAccount)) Then
MsgBox "Duplicate"
Me.ACCOUNT_.Undo
Cancel = True
End If

End Sub
__________________________________________________________
Click to expand...
Click to expand...
 
Back
Top