primary text field checking for dupes after text field is updated

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
 
M

Marshall Barton

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


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
 
G

Guest

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

Marshall Barton

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

Guest

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
__________________________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top