Create custom error message if duplicate record . . .

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

Guest

I tried using some code from someone else's post but it does not work. I simply want to return a custom error message when a duplicate record is entered into a table from a field.

My Table is called "Chemicals
My Form is called "ChemicalLocations
The control on my form is called "ChemName" (From Chemicals Table. ChemName is also indexed

The code I tried in A2003 was applied to the control ChemName on after_update in the ChemicalLocations Form (code follows) but it keeps returning an error message "Runtime Error 2001. You canceled your previous operation". Any help? Thx

Private Sub ChemName_AfterUpdate(
If Not IsNull(DLookup("[ChemName]", "[Chemicals]", "[ChemName] = " & Me.ChemName)) The
End I
End Sub
 
I tried using some code from someone else's post but it does not work. I simply want to return a custom error message when a duplicate record is entered into a table from a field.

My Table is called "Chemicals"
My Form is called "ChemicalLocations"
The control on my form is called "ChemName" (From Chemicals Table. ChemName is also indexed.

The code I tried in A2003 was applied to the control ChemName on after_update in the ChemicalLocations Form (code follows) but it keeps returning an error message "Runtime Error 2001. You canceled your previous operation". Any help? Thx.

Private Sub ChemName_AfterUpdate()
If Not IsNull(DLookup("[ChemName]", "[Chemicals]", "[ChemName] = " & Me.ChemName)) Then
End If
End Sub
Click to expand...

Use the BeforeUpdate event (which runs *before* it's too late; the
record has already been written to disk by AfterUpdate) instead:

Private Sub ChemName_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookup("[ChemName]", "[Chemicals]", _
"[ChemName] = " & e.ChemName)) Then
MsgBox "This ChemName has already been entered"
Cancel = True
End If
End Sub
 
Hi John
I have the same kind of error returned by MSAccess, but, differently from MBooze
I used the before-update event (the VBA code I wrote is very similar to the MBoozer one)
Thanks in advance for any hel
R

----- John Vinson wrote: ----

On Tue, 16 Mar 2004 19:01:08 -0800, "MBoozer
I tried using some code from someone else's post but it does not work. I simply want to return a custom error message when a duplicate record is entered into a table from a field.
My Table is called "Chemicals
Click to expand...
My Form is called "ChemicalLocations
The control on my form is called "ChemName" (From Chemicals Table. ChemName is also indexed
The code I tried in A2003 was applied to the control ChemName on after_update in the ChemicalLocations Form (code follows) but it keeps returning an error message "Runtime Error 2001. You canceled your previous operation". Any help? Thx
Private Sub ChemName_AfterUpdate(
Click to expand...
If Not IsNull(DLookup("[ChemName]", "[Chemicals]", "[ChemName] = " & Me.ChemName)) The
End I
End Su
Click to expand...

Use the BeforeUpdate event (which runs *before* it's too late; th
record has already been written to disk by AfterUpdate) instead

Private Sub ChemName_BeforeUpdate(Cancel as Integer
If Not IsNull(DLookup("[ChemName]", "[Chemicals]",
"[ChemName] = " & e.ChemName)) The
MsgBox "This ChemName has already been entered
Cancel = Tru
End I
End Su


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi
 
....OK I found the problem: I did not use the single quote

Errata
If Not IsNull(DLookup("[ChemName]", "[Chemicals]", "[ChemName] = " & Me.ChemName)) The
Corrige
If Not IsNull(DLookup("[ChemName]", "[Chemicals]", "[ChemName] = '" & Me.ChemName & "'")) The
Click to expand...

RF

----- RFerrari27 wrote: ----

Hi John
I have the same kind of error returned by MSAccess, but, differently from MBooze
I used the before-update event (the VBA code I wrote is very similar to the MBoozer one)
Thanks in advance for any hel
R

----- John Vinson wrote: ----

On Tue, 16 Mar 2004 19:01:08 -0800, "MBoozer
I tried using some code from someone else's post but it does not work. I simply want to return a custom error message when a duplicate record is entered into a table from a field.
My Table is called "Chemicals
Click to expand...
My Form is called "ChemicalLocations
The control on my form is called "ChemName" (From Chemicals Table. ChemName is also indexed
The code I tried in A2003 was applied to the control ChemName on after_update in the ChemicalLocations Form (code follows) but it keeps returning an error message "Runtime Error 2001. You canceled your previous operation". Any help? Thx
Private Sub ChemName_AfterUpdate(
Click to expand...
If Not IsNull(DLookup("[ChemName]", "[Chemicals]", "[ChemName] = " & Me.ChemName)) The
End I
End Su
Click to expand...

Use the BeforeUpdate event (which runs *before* it's too late; th
record has already been written to disk by AfterUpdate) instead

Private Sub ChemName_BeforeUpdate(Cancel as Integer
If Not IsNull(DLookup("[ChemName]", "[Chemicals]",
"[ChemName] = " & e.ChemName)) The
MsgBox "This ChemName has already been entered
Cancel = Tru
End I
End Su


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi
 
Back
Top