A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

eliminate Duplicate entries



 
 
Thread Tools Display Modes
  #1  
Old November 17th, 2009, 07:55 PM posted to microsoft.public.access.forms
Ed Sexton
external usenet poster
 
Posts: 1
Default eliminate Duplicate entries

I have a DB for tracking workorders, I want to notify the operator when they have entered a duplicate work order number.
Conditions
Table name: WorkOrder
Field Name: WO
The WO field is the primary key for teh table and I've selected duplicates no for the field. I used the table to create the form, and want to generate a error message when a duplicate owrk order is entered in the form field. Presently, the at entry, the form closes and if the work order number is a duplicate, the new entry is trashed with no indicateion to the operator.

EggHeadCafe - Software Developer Portal of Choice
..NET GDI+ - Draw Tree / Hierarchy
http://www.eggheadcafe.com/tutorials...w-tree--h.aspx
  #2  
Old November 18th, 2009, 04:22 AM posted to microsoft.public.access.forms
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default eliminate Duplicate entries

On Tue, 17 Nov 2009 10:55:16 -0800, Ed Sexton wrote:

You have a couple of options, including:
* Write code in the Form_Error event. Set a breakpoint there and see
what is passed in. You can then provide a user-friendly error message.
* Write code in the control.BeforeUpdate event to check for existing
value:
if dcount("WO", "WorkOrder", "WO=" & Me.txtWO)0 then
Msgbox "Yo! We already have this one."
Cancel=True
end if
(I guessed at some of your object names)
Importantly Cancel is set to True so the user cannot leave the field
without supplying a unique number.
* Write code to auto-generate a number, for example using the "DMax+1"
technique.

-Tom.
Microsoft Access MVP


I have a DB for tracking workorders, I want to notify the operator when they have entered a duplicate work order number.
Conditions
Table name: WorkOrder
Field Name: WO
The WO field is the primary key for teh table and I've selected duplicates no for the field. I used the table to create the form, and want to generate a error message when a duplicate owrk order is entered in the form field. Presently, the at entry, the form closes and if the work order number is a duplicate, the new entry is trashed with no indicateion to the operator.

EggHeadCafe - Software Developer Portal of Choice
.NET GDI+ - Draw Tree / Hierarchy
http://www.eggheadcafe.com/tutorials...w-tree--h.aspx

  #3  
Old November 18th, 2009, 04:30 AM posted to microsoft.public.access.forms
Al Campagna[_2_]
external usenet poster
 
Posts: 1,462
Default eliminate Duplicate entries

Ed,
Several ways to do that...
Use the BeforeUpdate event of the WO control.
Do a Dlookup with the new WO value, to see if a dupe exists. If so,
mesaage the user, and
Cancel = True
WO.Undo
Otherwise continue...

I would suggest that you not allow users to create/enter/edit key field
values.
It's OK that they enter a WO number, but you should have your own "real"
key field (ex. autonumber) hidden... and handling the relationships
in the background.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Ed Sexton wrote in message ...
I have a DB for tracking workorders, I want to notify the operator when
they have entered a duplicate work order number.
Conditions
Table name: WorkOrder
Field Name: WO
The WO field is the primary key for teh table and I've selected duplicates
no for the field. I used the table to create the form, and want to
generate a error message when a duplicate owrk order is entered in the
form field. Presently, the at entry, the form closes and if the work order
number is a duplicate, the new entry is trashed with no indicateion to the
operator.

EggHeadCafe - Software Developer Portal of Choice
.NET GDI+ - Draw Tree / Hierarchy
http://www.eggheadcafe.com/tutorials...w-tree--h.aspx



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:00 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.