compound indexing

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

Guest

I am trying to put a check in to place so that a person cannot be added to
the database more than once. I have created an index based on forname,
surname and NI number and have said this needs to be unique. Therefore if the
user tries to enter someone twice, then they will not be able to.

This seems to work, however, it dosesnt tell me that it hasnt added the
record. It just doesnt add the record. How do I make it obvious to the user
that the record cannot be added because it is a duplicate?

Hope this makes sense
 
lynn atkinson said:
I am trying to put a check in to place so that a person cannot be
added to the database more than once. I have created an index based
on forname, surname and NI number and have said this needs to be
unique. Therefore if the user tries to enter someone twice, then they
will not be able to.

This seems to work, however, it dosesnt tell me that it hasnt added
the record. It just doesnt add the record. How do I make it obvious
to the user that the record cannot be added because it is a duplicate?

Hope this makes sense
Click to expand...

What sort of process are you using to add records? If you're doing it
via a form, I'd expect you to get an error message (unless the error was
trapped and ignored in the form's Error event). If you're executing an
append query in code via the DAO Execute method, you'd need to specify
the dbFailOnError option if you want an error to be raised.
 
I am entering the data via a form which is based on a select query. I havent
to my knowledge told it to ignore any errors. Where would I find this
property?
 
I have found the onerror property and there is no procedure in here - should
Iput one in? If so, what do I say?

cheers
 
No, you shouldn't create an event procedure for the form's Error event;
at least, not yet. I'm puzzled, though. In the absence of any event
procedure to handle the event, you should get an error message if you
attempt to create record that duplicates an existing key in a unique
index.

Is this a bound form, so you're just filling in bound controls and
letting Access save the record in the normal way?

Is this happening in an MDB or an ADP?

What version of Access are you using?

Is the table in question a local table or a linked table?

What is the SQL of the query that is the form's recordsource?

If multiple tables are involved in the query, which one has the unique
undex defined on it?
 
I dont get any error message unless I enter the duplicate information
straight in to the table or (the linked table). The form appears to accept
the information, but when you look at the table, the change or new record has
not been saved
In answer to your other questions:

It is a bound form and it is happening in an MDB.
I am using Access 2002 (sp3)
the table is a linked table- the database is multiuser so it is split in to
front and back ends.

the SQL of the query underlying the table is

SELECT employeedetails.*, selection.action
FROM ([post details] RIGHT JOIN employeedetails ON [post details].[post ID]
= employeedetails.[post ID]) LEFT JOIN selection ON employeedetails.[employee
ID new] = selection.[employee ID];
The unique index is defined in the employeedetails table and is made up of
surname+forname+NInumber

If you can see any reason for this then I would be grateful.
 
Your form is not based on the [employeedetails] table, but on a query
that joins that table with three other tables, which I'm guessing have a
many-to-one relationship with [employeedetails]. I suspect that your
form is adding records to the table [selection], not to
[employeedetails]. Do you have the form's Recordset Type property set
to "Dynaset (Inconsistent Updates)", by any chance?

I've tried to reproduce this problem and haven't been able to. If you'd
like to send me a cut-down copy of your database, containing only the
elements necessary to demonstrate the problem, compacted and then zipped
to less than 1MB in size (preferably much smaller) -- I'll have a look
at it, time permitting. You can send it to the address derived by
removing NO SPAM from the reply address of this message.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

lynn atkinson said:
I dont get any error message unless I enter the duplicate information
straight in to the table or (the linked table). The form appears to
accept the information, but when you look at the table, the change or
new record has not been saved
In answer to your other questions:

It is a bound form and it is happening in an MDB.
I am using Access 2002 (sp3)
the table is a linked table- the database is multiuser so it is split
in to front and back ends.

the SQL of the query underlying the table is

SELECT employeedetails.*, selection.action
FROM ([post details] RIGHT JOIN employeedetails ON [post
details].[post ID] = employeedetails.[post ID]) LEFT JOIN selection
ON employeedetails.[employee ID new] = selection.[employee ID];
The unique index is defined in the employeedetails table and is made
up of surname+forname+NInumber

If you can see any reason for this then I would be grateful.


Dirk Goldgar said:
No, you shouldn't create an event procedure for the form's Error
event; at least, not yet. I'm puzzled, though. In the absence of
any event procedure to handle the event, you should get an error
message if you attempt to create record that duplicates an existing
key in a unique index.

Is this a bound form, so you're just filling in bound controls and
letting Access save the record in the normal way?

Is this happening in an MDB or an ADP?

What version of Access are you using?

Is the table in question a local table or a linked table?

What is the SQL of the query that is the form's recordsource?

If multiple tables are involved in the query, which one has the
unique undex defined on it?
Click to expand...
Click to expand...
 
Thanks for your reply. A strange thing has happened which makes me think it
is a relationship problem or a problem adding the index AFTER there is data
in the database(i created a new database containing the compound index and
imported all the records in to the tables). I made a copy of the database and
deleted a lot of the records so I could send to you - I am now getting the
error message in the form!
The form is adding records to the employeedetails table and NOT the
selection table as you suggested - I have checked this and the recordset type
property is set to Dynaset - NOT the(inconsistent updates)option.
I can still send you the cut down version if you think it may help (but I
cannot see a reply address except for the newsgroup). Or does this latest
info shed any light on what might be happening?

Cheers

Dirk Goldgar said:
Your form is not based on the [employeedetails] table, but on a query
that joins that table with three other tables, which I'm guessing have a
many-to-one relationship with [employeedetails]. I suspect that your
form is adding records to the table [selection], not to
[employeedetails]. Do you have the form's Recordset Type property set
to "Dynaset (Inconsistent Updates)", by any chance?

I've tried to reproduce this problem and haven't been able to. If you'd
like to send me a cut-down copy of your database, containing only the
elements necessary to demonstrate the problem, compacted and then zipped
to less than 1MB in size (preferably much smaller) -- I'll have a look
at it, time permitting. You can send it to the address derived by
removing NO SPAM from the reply address of this message.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

lynn atkinson said:
I dont get any error message unless I enter the duplicate information
straight in to the table or (the linked table). The form appears to
accept the information, but when you look at the table, the change or
new record has not been saved
In answer to your other questions:

It is a bound form and it is happening in an MDB.
I am using Access 2002 (sp3)
the table is a linked table- the database is multiuser so it is split
in to front and back ends.

the SQL of the query underlying the table is

SELECT employeedetails.*, selection.action
FROM ([post details] RIGHT JOIN employeedetails ON [post
details].[post ID] = employeedetails.[post ID]) LEFT JOIN selection
ON employeedetails.[employee ID new] = selection.[employee ID];
The unique index is defined in the employeedetails table and is made
up of surname+forname+NInumber

If you can see any reason for this then I would be grateful.


Dirk Goldgar said:
No, you shouldn't create an event procedure for the form's Error
event; at least, not yet. I'm puzzled, though. In the absence of
any event procedure to handle the event, you should get an error
message if you attempt to create record that duplicates an existing
key in a unique index.

Is this a bound form, so you're just filling in bound controls and
letting Access save the record in the normal way?

Is this happening in an MDB or an ADP?

What version of Access are you using?

Is the table in question a local table or a linked table?

What is the SQL of the query that is the form's recordsource?

If multiple tables are involved in the query, which one has the
unique undex defined on it?

message I have found the onerror property and there is no procedure in here
- should Iput one in? If so, what do I say?

cheers

:

I am entering the data via a form which is based on a select query.
I havent to my knowledge told it to ignore any errors. Where would
I find this property?

:

message I am trying to put a check in to place so that a person cannot be
added to the database more than once. I have created an index
based on forname, surname and NI number and have said this needs
to be unique. Therefore if the user tries to enter someone
twice, then they will not be able to.

This seems to work, however, it dosesnt tell me that it hasnt
added the record. It just doesnt add the record. How do I make
it obvious to the user that the record cannot be added because
it is a duplicate?

Hope this makes sense

What sort of process are you using to add records? If you're
doing it via a form, I'd expect you to get an error message
(unless the error was trapped and ignored in the form's Error
event). If you're executing an append query in code via the DAO
Execute method, you'd need to specify the dbFailOnError option if
you want an error to be raised.
Click to expand...
Click to expand...
Click to expand...
 
lynn atkinson said:
Thanks for your reply. A strange thing has happened which makes me
think it is a relationship problem or a problem adding the index
AFTER there is data in the database(i created a new database
containing the compound index and imported all the records in to the
tables). I made a copy of the database and deleted a lot of the
records so I could send to you - I am now getting the error message
in the form!
The form is adding records to the employeedetails table and NOT the
selection table as you suggested - I have checked this and the
recordset type property is set to Dynaset - NOT the(inconsistent
updates)option.
I can still send you the cut down version if you think it may help
(but I cannot see a reply address except for the newsgroup). Or does
this latest info shed any light on what might be happening?
Click to expand...

Now that is peculiar. Did you create this database from scratch, and
then import all the objects from the other database? Can you tell if
the problem was corrected at that point, even before you deleted a lot
of records?

I don't see much point in your sending me a sample database that doesn't
have the problem we're trying to debug! What happens if you take that
working, apparently error-free copy and import all the records from the
old database (by linking to the tables and running append queries, not
by deleting and re-importing the tables)? Does the problem reappear?
 
Back
Top