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 » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access 2000 Duplicate Key



 
 
Thread Tools Display Modes
  #1  
Old October 23rd, 2008, 03:53 PM posted to microsoft.public.access.tablesdbdesign
Gus
external usenet poster
 
Posts: 69
Default Access 2000 Duplicate Key

Hello,

I have a split database that users access via the LAN. I have a table
tblAgr_Check_Out (ChkO_ID, Date_Checked_Out, User_ID,FS_Agr_Num).

The ChkO_ID is the primary key with an AutoNumber Datatype I have it set so
that it is Indexed and that no duplicates are allowed. Even when I have set
up this way it still allows some duplicates and it creates problems. The
FS_Agr_Num is also a unique key so I have tried to set both FS_Agr_Num and
ChkO_ID as combined primary keys.

I also allowed the FS_Agr_Num to be indexed and no duplicates allowed, which
seemed to have led to even bigger user problems. When I did this the errors
increased to the point that I had to recreate the table because it was giving
me an index and search key error. The database is in constant use, so the
FS_Agr_Num is always being assigned and reassigned to users.

What are some suggestions you would make to stop this errors? I am
considering going to converting the DB to Access 2002 but I am not if this
will help or cause more problem?

Thanks.
  #2  
Old October 23rd, 2008, 06:04 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access 2000 Duplicate Key

On Thu, 23 Oct 2008 07:53:01 -0700, Gus wrote:

Hello,

I have a split database that users access via the LAN. I have a table
tblAgr_Check_Out (ChkO_ID, Date_Checked_Out, User_ID,FS_Agr_Num).

The ChkO_ID is the primary key with an AutoNumber Datatype I have it set so
that it is Indexed and that no duplicates are allowed. Even when I have set
up this way it still allows some duplicates and it creates problems. The
FS_Agr_Num is also a unique key so I have tried to set both FS_Agr_Num and
ChkO_ID as combined primary keys.

I also allowed the FS_Agr_Num to be indexed and no duplicates allowed, which
seemed to have led to even bigger user problems. When I did this the errors
increased to the point that I had to recreate the table because it was giving
me an index and search key error. The database is in constant use, so the
FS_Agr_Num is always being assigned and reassigned to users.

What are some suggestions you would make to stop this errors? I am
considering going to converting the DB to Access 2002 but I am not if this
will help or cause more problem?

Thanks.


2002 has been a lot more stable than 2000, in my experience. In fact I'd go to
2003. 2007 has a major change in the user interface and some teething pains;
you may want to investigate it but don't plunge in quite yet!

The biggest question is... have you Split the database? ANY multiuser Access
database (any version) should - even must - be split into a Backend containing
only the tables, with a Frontend linked to the tables; each user should have
their own copy of the frontend. If your database is shared by the users this
may be the source of a lot of your problems. There was a bug in 2000 (fixed,
supposedly, by a service pack) that caused Autonumbers to repeat.

It's rather unusual to have two unique fields in a table; it would be VERY
unusual to have a two-field primary key where each component of the key is
itself already unique! What's the point? If FS_Agr_Num is unique already,
combining it with ChkO_ID can't possibly make it any "uniquer"! Perhaps you
could explain how FS_Agr_Num is "assigned" to users.
--

John W. Vinson [MVP]
  #3  
Old October 23rd, 2008, 06:44 PM posted to microsoft.public.access.tablesdbdesign
Gus
external usenet poster
 
Posts: 69
Default Access 2000 Duplicate Key

Hi John,

The database is split each user has there own copy of the the FE the BE is
stored on the LAN.

What's the point? If FS_Agr_Num is unique already,
combining it with ChkO_ID can't possibly make it any "uniquer"! Perhaps you
could explain how FS_Agr_Num is "assigned" to users.


The FS_Agr_Num is normally entered by two users who make the most updates to
the table tblAgr_Check_Out, however any user can add an FS_Agr_Num if they
find that it is not currently in the table. The problem is that I am trying
to have the database not allow an FS_Agr_Num more then once. Would you say
that making the FS_Agr_Num required, Indexed and not allow duplicates would
be one of my best solutions?

As mentioned I did try this before and it gave me many errors. I corrected
that and the Database seems to be working fine as I have not gotten
complaints from my users. Though now I am afraid that if I modify the table
structure I may run into corruption problems. What do you suggest.

Thank you.



"John W. Vinson" wrote:

On Thu, 23 Oct 2008 07:53:01 -0700, Gus wrote:

Hello,

I have a split database that users access via the LAN. I have a table
tblAgr_Check_Out (ChkO_ID, Date_Checked_Out, User_ID,FS_Agr_Num).

The ChkO_ID is the primary key with an AutoNumber Datatype I have it set so
that it is Indexed and that no duplicates are allowed. Even when I have set
up this way it still allows some duplicates and it creates problems. The
FS_Agr_Num is also a unique key so I have tried to set both FS_Agr_Num and
ChkO_ID as combined primary keys.

I also allowed the FS_Agr_Num to be indexed and no duplicates allowed, which
seemed to have led to even bigger user problems. When I did this the errors
increased to the point that I had to recreate the table because it was giving
me an index and search key error. The database is in constant use, so the
FS_Agr_Num is always being assigned and reassigned to users.

What are some suggestions you would make to stop this errors? I am
considering going to converting the DB to Access 2002 but I am not if this
will help or cause more problem?

Thanks.


2002 has been a lot more stable than 2000, in my experience. In fact I'd go to
2003. 2007 has a major change in the user interface and some teething pains;
you may want to investigate it but don't plunge in quite yet!

The biggest question is... have you Split the database? ANY multiuser Access
database (any version) should - even must - be split into a Backend containing
only the tables, with a Frontend linked to the tables; each user should have
their own copy of the frontend. If your database is shared by the users this
may be the source of a lot of your problems. There was a bug in 2000 (fixed,
supposedly, by a service pack) that caused Autonumbers to repeat.

It's rather unusual to have two unique fields in a table; it would be VERY
unusual to have a two-field primary key where each component of the key is
itself already unique! What's the point? If FS_Agr_Num is unique already,
combining it with ChkO_ID can't possibly make it any "uniquer"! Perhaps you
could explain how FS_Agr_Num is "assigned" to users.
--

John W. Vinson [MVP]

  #4  
Old October 23rd, 2008, 07:27 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access 2000 Duplicate Key

On Thu, 23 Oct 2008 10:44:07 -0700, Gus wrote:

The FS_Agr_Num is normally entered by two users who make the most updates to
the table tblAgr_Check_Out, however any user can add an FS_Agr_Num if they
find that it is not currently in the table. The problem is that I am trying
to have the database not allow an FS_Agr_Num more then once. Would you say
that making the FS_Agr_Num required, Indexed and not allow duplicates would
be one of my best solutions?


Yes. For user friendliness, you might want to use the BeforeUpdate event of
the textbox bound to FS_Agr_Num on the form; use DLookUp to see if the user's
entry already exists and cancel the addition with a warning message if so.

As mentioned I did try this before and it gave me many errors. I corrected
that and the Database seems to be working fine as I have not gotten
complaints from my users. Though now I am afraid that if I modify the table
structure I may run into corruption problems. What do you suggest.


Since you haven't said what KIND of errors or what you've fixed, I cannot
advise - but Access 2000 does indeed handle unique indexes correctly.
--

John W. Vinson [MVP]
  #5  
Old October 23rd, 2008, 08:27 PM posted to microsoft.public.access.tablesdbdesign
Gus
external usenet poster
 
Posts: 69
Default Access 2000 Duplicate Key

Since you haven't said what KIND of errors or what you've fixed, I cannot
advise - but Access 2000 does indeed handle unique indexes correctly.


The problems that I fixed where problably due to database corruption, so I
recreated my table.

Yes. For user friendliness, you might want to use the BeforeUpdate event of
the textbox bound to FS_Agr_Num on the form; use DLookUp to see if the user's
entry already exists and cancel the addition with a warning message if so.


Great I will do and see if this works. Any suggestions as to the DLookup
code structure?

Thanks.

"John W. Vinson" wrote:

On Thu, 23 Oct 2008 10:44:07 -0700, Gus wrote:

The FS_Agr_Num is normally entered by two users who make the most updates to
the table tblAgr_Check_Out, however any user can add an FS_Agr_Num if they
find that it is not currently in the table. The problem is that I am trying
to have the database not allow an FS_Agr_Num more then once. Would you say
that making the FS_Agr_Num required, Indexed and not allow duplicates would
be one of my best solutions?


Yes. For user friendliness, you might want to use the BeforeUpdate event of
the textbox bound to FS_Agr_Num on the form; use DLookUp to see if the user's
entry already exists and cancel the addition with a warning message if so.

As mentioned I did try this before and it gave me many errors. I corrected
that and the Database seems to be working fine as I have not gotten
complaints from my users. Though now I am afraid that if I modify the table
structure I may run into corruption problems. What do you suggest.


Since you haven't said what KIND of errors or what you've fixed, I cannot
advise - but Access 2000 does indeed handle unique indexes correctly.
--

John W. Vinson [MVP]

  #6  
Old October 23rd, 2008, 09:33 PM posted to microsoft.public.access.tablesdbdesign
Gus
external usenet poster
 
Posts: 69
Default Access 2000 Duplicate Key

John,

WOuld you have any advice as to why a user would get an error everytime they
try to enter an FS_Agr_Num letting them know that the record already exits
and that if they add it it will create duplicates, but when they search for
that FS_Agr_Num the database says it ca not find it?


"John W. Vinson" wrote:

On Thu, 23 Oct 2008 10:44:07 -0700, Gus wrote:

The FS_Agr_Num is normally entered by two users who make the most updates to
the table tblAgr_Check_Out, however any user can add an FS_Agr_Num if they
find that it is not currently in the table. The problem is that I am trying
to have the database not allow an FS_Agr_Num more then once. Would you say
that making the FS_Agr_Num required, Indexed and not allow duplicates would
be one of my best solutions?


Yes. For user friendliness, you might want to use the BeforeUpdate event of
the textbox bound to FS_Agr_Num on the form; use DLookUp to see if the user's
entry already exists and cancel the addition with a warning message if so.

As mentioned I did try this before and it gave me many errors. I corrected
that and the Database seems to be working fine as I have not gotten
complaints from my users. Though now I am afraid that if I modify the table
structure I may run into corruption problems. What do you suggest.


Since you haven't said what KIND of errors or what you've fixed, I cannot
advise - but Access 2000 does indeed handle unique indexes correctly.
--

John W. Vinson [MVP]

  #7  
Old October 23rd, 2008, 09:52 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access 2000 Duplicate Key

On Thu, 23 Oct 2008 12:27:01 -0700, Gus wrote:

Yes. For user friendliness, you might want to use the BeforeUpdate event of
the textbox bound to FS_Agr_Num on the form; use DLookUp to see if the user's
entry already exists and cancel the addition with a warning message if so.


Great I will do and see if this works. Any suggestions as to the DLookup
code structure?


Something like this perhaps; the details may differ with your application and
your needs:

Private Sub FS_Agr_Num_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[FS_Agr_Num]", "YourTableName")) Then
MsgBox "This AGR Number has been used, please pick another", vbOKOnly
Cancel = True ' cancel the addition
Me!FS_Agr_Num.Undo ' erase the user's attempt
End If
End Sub

You could have code to jump to the record for the value entered, if you wish.
--

John W. Vinson [MVP]
  #8  
Old October 23rd, 2008, 10:59 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access 2000 Duplicate Key

On Thu, 23 Oct 2008 13:33:01 -0700, Gus wrote:

John,

WOuld you have any advice as to why a user would get an error everytime they
try to enter an FS_Agr_Num letting them know that the record already exits
and that if they add it it will create duplicates, but when they search for
that FS_Agr_Num the database says it ca not find it?


I presume because there's something wrong with either my code or how you've
implemented it! Please post the actual code from your table. You may also want
to set a breakpoint in the code (click in the grey bar to the left of the code
window next to an executable statement, you'll see a brown dot) and step
through it to see what the DLookUp is returning.
--

John W. Vinson [MVP]
  #9  
Old October 23rd, 2008, 11:24 PM posted to microsoft.public.access.tablesdbdesign
Gus
external usenet poster
 
Posts: 69
Default Access 2000 Duplicate Key

The error happened before I implemented you code.

Your code works prefectly : )
Private Sub FS_Agr_Num_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[FS_Agr_Num]", "tblAgr_Check_Out")) Then
MsgBox "This Agreement Number is already in the Database", vbOKOnly
Cancel = True ' cancel the addition
Me!FS_Agr_Num.Undo ' erase the user's attempt
End If
End Sub

I did notice though that when I hit the Save button I have on my FS_Agr_Form
repeatedly many times and then checked my table tbl_Agr_Check_Out there where
entrys int the table with a blank FS_Agr_Num.

I do have code to prvent the FS_Agr_Num from being saved as a blank record.
I must have done something that disabled it.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!FS_Agr_Num) Then
MsgBox "Please fill in FS Agreement Number"
Cancel = True
Me!FS_Agr_Num.Undo
End If
End Sub

Thank you!

"John W. Vinson" wrote:

On Thu, 23 Oct 2008 13:33:01 -0700, Gus wrote:

John,

WOuld you have any advice as to why a user would get an error everytime they
try to enter an FS_Agr_Num letting them know that the record already exits
and that if they add it it will create duplicates, but when they search for
that FS_Agr_Num the database says it ca not find it?


I presume because there's something wrong with either my code or how you've
implemented it! Please post the actual code from your table. You may also want
to set a breakpoint in the code (click in the grey bar to the left of the code
window next to an executable statement, you'll see a brown dot) and step
through it to see what the DLookUp is returning.
--

John W. Vinson [MVP]

 




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