Preventing duplicate records

[Jim Hess]

I'm using Access 2002. I need code that will alert the user that there is a
duplicate requisition number as soon as user moves from the requisition
number field. This is the code I originally used and it worked in Access
2000:

Private Sub REQNUM_AfterUpdate()
'Code came from ZDJournal reply
'TVCodeTools ErrorEnablerStart
On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim My_REQNUM As String

My_REQNUM = [ReqNum].Value

If IsNull(DLookup("[REQNUM]", "MSTRREQ", "[REQNUM] = '" & My_REQNUM &
"'")) = False Then ' If the REQNUM exists already
MsgBox "REQNUM = " & My_REQNUM & " is already on file"

' The next entry cancels the current record entry
SendKeys "{ESC}", 200

' The next 2 lines bring up the record with the particular REQNUM
Me.RecordsetClone.FindFirst "[REQNUM] = '" & My_REQNUM & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

End If


'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd


End Sub

As you can see, I have some of the FMS tools. My problem is that this
function no longer works and I don't know how to fix it. Can someone help?

 

[John Spencer]

Instead of using sendKeys I would use
Me.Undo 'if in a form
or
Forms!YourFormname.Undo 'if doing this from a general module

(which undoes all changes to a form) - just like pressing the escape key.

Beyond that would you care to explain what you mean "by this function no
longer works"?
Does the code
-- not run at all? How do you know?
-- runs but does not clear the record
-- generates an error? What error?
-- runs, clears the record, but does not bring up the matched record
-- or something else.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jim Hess]

By "not working" I mean that it is performing badly in several different
ways. In the original form where I used this code, instead of notifying the
user of the duplicate and then moving to the original record, it notifies and
then gives another Microsoft-generated message box and then the user has to
press Esc to cancel the new record. In another instance, nothing happens.
In yet another instance it notifies, appears to move to the existing record,
but some of the other fields that were populated previously are now empty. I
honestly don't know what is going on anymore.

Instead of using sendKeys I would use
Me.Undo 'if in a form
or
Forms!YourFormname.Undo 'if doing this from a general module

(which undoes all changes to a form) - just like pressing the escape key.

Beyond that would you care to explain what you mean "by this function no
longer works"?
Does the code
-- not run at all? How do you know?
-- runs but does not clear the record
-- generates an error? What error?
-- runs, clears the record, but does not bring up the matched record
-- or something else.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I'm using Access 2002. I need code that will alert the user that there is a
duplicate requisition number as soon as user moves from the requisition
number field. This is the code I originally used and it worked in Access
2000:

Private Sub REQNUM_AfterUpdate()
'Code came from ZDJournal reply
'TVCodeTools ErrorEnablerStart
On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim My_REQNUM As String

My_REQNUM = [ReqNum].Value

If IsNull(DLookup("[REQNUM]", "MSTRREQ", "[REQNUM] = '" & My_REQNUM &
"'")) = False Then ' If the REQNUM exists already
MsgBox "REQNUM = " & My_REQNUM & " is already on file"

' The next entry cancels the current record entry
SendKeys "{ESC}", 200

' The next 2 lines bring up the record with the particular REQNUM
Me.RecordsetClone.FindFirst "[REQNUM] = '" & My_REQNUM & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

End If


'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd


End Sub

As you can see, I have some of the FMS tools. My problem is that this
function no longer works and I don't know how to fix it. Can someone help?

 

[John Spencer]

Ok, so did you try replacing sendkeys with Undo?

I am a bit confused. Are you getting these different results all on one form
or are you getting different results with different forms?

SendKeys IS NOT a good thing to use. One reason is that the object that has
the focus is potentially not the object that called the code.

Do you have a form with a timer event that is running? The timer event could
potentially change the object (form) that has the focus.

Beyond the above suggestions I am baffled.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

By "not working" I mean that it is performing badly in several different
ways. In the original form where I used this code, instead of notifying the
user of the duplicate and then moving to the original record, it notifies and
then gives another Microsoft-generated message box and then the user has to
press Esc to cancel the new record. In another instance, nothing happens.
In yet another instance it notifies, appears to move to the existing record,
but some of the other fields that were populated previously are now empty. I
honestly don't know what is going on anymore.

Instead of using sendKeys I would use
Me.Undo 'if in a form
or
Forms!YourFormname.Undo 'if doing this from a general module

(which undoes all changes to a form) - just like pressing the escape key.

Beyond that would you care to explain what you mean "by this function no
longer works"?
Does the code
-- not run at all? How do you know?
-- runs but does not clear the record
-- generates an error? What error?
-- runs, clears the record, but does not bring up the matched record
-- or something else.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I'm using Access 2002. I need code that will alert the user that there is a
duplicate requisition number as soon as user moves from the requisition
number field. This is the code I originally used and it worked in Access
2000:

Private Sub REQNUM_AfterUpdate()
'Code came from ZDJournal reply
'TVCodeTools ErrorEnablerStart
On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim My_REQNUM As String

My_REQNUM = [ReqNum].Value

If IsNull(DLookup("[REQNUM]", "MSTRREQ", "[REQNUM] = '" & My_REQNUM &
"'")) = False Then ' If the REQNUM exists already
MsgBox "REQNUM = " & My_REQNUM & " is already on file"

' The next entry cancels the current record entry
SendKeys "{ESC}", 200

' The next 2 lines bring up the record with the particular REQNUM
Me.RecordsetClone.FindFirst "[REQNUM] = '" & My_REQNUM & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

End If


'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd


End Sub

As you can see, I have some of the FMS tools. My problem is that this
function no longer works and I don't know how to fix it. Can someone help?

 

[Jim Hess]

Well, I did just try your suggestion and it does seem to work. I cannot
believe I didn't think of such a simple solution. I apologize for the
confusion on the other issue. The database I am working on has been working
for our department for the last 10 years. It has migrated from Access 97 up
to Access 2002. That is as far as our IT people have allowed us to go.
Frankly, I don't care any more. I'm retiring in a couple of years. But the
people who use the database wanted that functionality in a couple of other
forms. I copied the code which I got from another source, changed the field
and table references, and was hoping it would work in the same way. But even
in the original form it seems to not be working as it should. But it seems
that your suggestion is going to fix that.

When we migrated to Microsoft Access from an old DOS-based application, I
was given about six months to make the conversion and this was my first
application in Access. We had a few rough moments at the beginning when some
things did not work as expected. Consequently, when we finally got it
running reasonably well my boss just decided to leave it alone. After
several years she retired, and my current boss wants to make this a real
application. A lot of time has passed, and it's not easy to go back and fix
things. But I appreciate your suggestion as it seems to have fixed my
problem.

Ok, so did you try replacing sendkeys with Undo?

I am a bit confused. Are you getting these different results all on one form
or are you getting different results with different forms?

SendKeys IS NOT a good thing to use. One reason is that the object that has
the focus is potentially not the object that called the code.

Do you have a form with a timer event that is running? The timer event could
potentially change the object (form) that has the focus.

Beyond the above suggestions I am baffled.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

By "not working" I mean that it is performing badly in several different
ways. In the original form where I used this code, instead of notifying the
user of the duplicate and then moving to the original record, it notifies and
then gives another Microsoft-generated message box and then the user has to
press Esc to cancel the new record. In another instance, nothing happens.
In yet another instance it notifies, appears to move to the existing record,
but some of the other fields that were populated previously are now empty. I
honestly don't know what is going on anymore.

Instead of using sendKeys I would use
Me.Undo 'if in a form
or
Forms!YourFormname.Undo 'if doing this from a general module

(which undoes all changes to a form) - just like pressing the escape key.

Beyond that would you care to explain what you mean "by this function no
longer works"?
Does the code
-- not run at all? How do you know?
-- runs but does not clear the record
-- generates an error? What error?
-- runs, clears the record, but does not bring up the matched record
-- or something else.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Jim Hess wrote:
I'm using Access 2002. I need code that will alert the user that there is a
duplicate requisition number as soon as user moves from the requisition
number field. This is the code I originally used and it worked in Access
2000:

Private Sub REQNUM_AfterUpdate()
'Code came from ZDJournal reply
'TVCodeTools ErrorEnablerStart
On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim My_REQNUM As String

My_REQNUM = [ReqNum].Value

If IsNull(DLookup("[REQNUM]", "MSTRREQ", "[REQNUM] = '" & My_REQNUM &
"'")) = False Then ' If the REQNUM exists already
MsgBox "REQNUM = " & My_REQNUM & " is already on file"

' The next entry cancels the current record entry
SendKeys "{ESC}", 200

' The next 2 lines bring up the record with the particular REQNUM
Me.RecordsetClone.FindFirst "[REQNUM] = '" & My_REQNUM & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

End If


'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd


End Sub

As you can see, I have some of the FMS tools. My problem is that this
function no longer works and I don't know how to fix it. Can someone help?