When is an error not an error?

[st3ve]

I am adding data via a form. When I try to add a non-unique number I get an error message saying 'non-unique' etc.

OK, so if a non-unique number is detected I want to put up a message box to the user and then delete the erroneous record. I have written some code to do this on detection of an error, but it never runs! I have tested the code on its own and it runs fine..

The unique number is generated by joining the two numbers input into the form; the two numbers themselves can be duplicates.

Thanks for reading this, and for any ideas you may have.

 

[Fizzio]

Where are you trying to trap the error (under which form/control event)

 

[st3ve]

Thanks for your interest,
I am trying to trap the error at the point where i enter the two 'numbers' into the form. I use an AfterUpdate which actually runs after I input data into the next entry box (PatientName).
I run a query to join the two parts together and then write it into the same table in a separate field.

When this query runs I get the 'non-unique' message - as I have set this in the field's properties.

Cheers.

 

[Fizzio]

It would be better to intercept the non-unique number before the queries are run. Can you store the two numbers without combining them as this is classed as the storage of a calculated value which does not usually need to be stored.

I would look closely at whether you need to store these values. If you do, use a Dcount to ascertain if the value exists before writing to the table. You should also not need a query to write this to a table as the form's controls (if bound) will write directly to the table.

I know I've asked more than I've answered but there may be a data structure issue to resolve first.

 

[st3ve]

I hear what you're saying Fizzio re: not storing calculated fields.

These two numbers are in fact entered as text. The actual number I wanted to enter is like 76000083123.
This is too big to be entered as a number, (and too much work) so i set up a separate entry point on the form. I divided the number into 76000083 called the pad number and 123 which I called the script number. The pad number is selected from a combo box list and the script number is entered as 123 etc.

The actual prescription pad contains the whole number! This must be unique, but i wanted to ensure that a number could not be re-entered (in error).

In particular though, what reasons could there be for not running my on_error routine when access itself has found an error (viz.'non-unique-ness')? and in general, how can two or more fields be 'paired up' to guarantee a unique entry

 

[Fizzio]

You can define a multi-field primary key by selecting the combination of fields you want to avoid a duplication of and selecting the primary key.
I would still keep the values separate as you can combine them by concatenating the values in a form, report or query.