Validation routine...help please

[mikeg8]

Cap'n Thicky here!

I need to validate a user input value...

Table: tbl-Specimen
Key field: SpecimenID (integer)
Temp variable: TestSpecID (integer)

I can't use 'autonumber' for the key field, so I need to check that whatever the user has entered is unique. I'm a very newbie user so please bear with me! I've got as far as working out that I might need to use a function (which I've called SpecIDOK which takes an integer argument and returns a boolean value so, in theory, somewhere I can say something like...

If SpecIDOK(TestSpecID) then
'User entered a unique ID
'Let them continue with new record entry
else
'User entered a non-unique ID, let them retry or
'abandon new record entry
endif

The problem is, I don't have the code for the SpecIDOK()function - in fact I have a total mental block on the code for the function!

Any ideas?

Mike

 

[jrbarnett]

Hi Mike,

An autonumber field will produce a unique value, but it has problems if used in a multi user environment. If this is the case, I would check out MichaelRed's FAQ on the subject.

The easiest way is to use DLookup to check if a record is found with the specified matching value, and if it doesn't, let the save go ahead, otherwise fail. Therefore, your SpecIDOK function will be very simple:

Function SpecIDOK (SpecID As Integer) As Boolean
  SpecIDOK = IsNull (DLookup ("SpecimenID", "tblSpecimens", "SpecimenID = " & SpecID))
End Function

This works because DLookup will return the value of the field SpecimenID from tblSpecimens, or NULL if it doesn't find any. This will get converted to a True/False value by the IsNull function which is assigned to the function name (this becomes its return value).

In fact, as it is only one line, why not just use it in your code thus:

If IsNull (DLookup ("SpecimenID", "tblSpecimens", "SpecimenID = " & SpecID))
 then
    'User entered a unique ID
    'Let them continue with new record entry
else
    'User entered a non-unique ID, let them retry or
    'abandon new record entry
endif

John

 

[DrSimon]

Mike
What John suggest is perfectly correct, but how important is it to have a functions that sends a nice message?
If that field is unique, the property of that field should be made unique (if not Primary Key). Then if someone tries to save a record with the same Spec No, Access will prevent it, admittedly with an unfriendly message. It is generally much better to let the database maintain data integrity rather than write code to do so.
Simon Rouse

 

[TonyJollans]

Hi mikeg8,

Why not set it up as a unique index and let Access take care of it? I know its messages aren't the user-friendliest in the world but it should guarantee correct results.

Enjoy,
Tony

 

[mikeg8]

Thanks everybody. I shall try the code and see how I get on and also try letting Access do it by itself.

I can't use an autonumber as a) the records will start from 1001 (which I could get around) and b) if a user is part way through entering a new record, then decides against it, Access 'uses up' that autonumber value - unfortunately, the specimen is still so identified, indelibly!

Thanks again

Mike

ps. As you have all been so helpful, could you take a look at a thread I posted a couple of days ago concerning using a calendar control: I haven't had any replies yet, hopefully not because it was just such a dumb question!

 

[PeteJohnston]

Simon, Tony

It is generally much better to let the database maintain data integrity rather than write code to do so

Fair enough if the people using the application are techies but, in the outside world, letting a Violated database integrity type of message box hit a user and crash the application is not the way to make friends and influence people!

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[DrSimon]

Pete
I don't really disagree with you. But here was a case of a relatively naive user trying to solve a standard problem in what I thought could be an unnecessarily complicated way. Rather than write code to detect replicate records, I thought it important to point out the simple way, which would be to let Access do it for you.
The strength and weakness of applications like Access is that you can set up a database without any need to consider the design of the relational structure. I am really concerned that some people get round this by writing complicated code that becomes a nightmare to maintain. I think Tek-Tips is a fantastic site but I look at some threads and think "If I as going there, I wouldn't have started from from where you come from"!
There is clearly a need for both good database design and underlying code and I totally agree that most of the default error messages are horrible and if it were me, yes I'd write the code probably with error trapping too.
Regards
Simon Rouse

 

[TonyJollans]

Hi Pete,

As I said in my post, with perhaps slight understatement, the system messages are not the best. But the system checks WORK and that seems to me to be a prime consideration.

I fear that an inexperienced developer trying to do this himself will run into problems in a multi user environment unless he takes great care and codes more than has been offered as a solution here.

Enjoy,
Tony

 

[TonyJollans]

Hi mikeg8 (and All),

Been thinking about this since I posted and what you want is a system where a number (whether chosen by the User or generated by the system) is reserved - whether or not details relating to it are input at the same time.

To do this means writing the record at the time the number is input (not waiting till all the data are input) and updating (rather than inserting) it when the rest of the data are input. Given this I don't see why you couldn't use an AutoNumber, which you could retrieve after generation and pass back to the User. That doesn't mean I'd recommend using an autonumber this way (I wouldn't) but it ought to work.

Enjoy,
Tony

 

[mikeg8]

Hi Tony (et al)

I like the idea of pre-assigning all the numbers (presumably by creating otherwise blank records?) then letting the user(s) choose a number, as it were, I hadn't thought of that one.

Part of my problem (I suspect) is that in the dim and distant past, long before OOP came along, I used to write database type apps (Basic, Pascal and Cobol) which obviously didn't feature all the built-in goodies that Access does. As a result, I'm spending half my time un-learning my old methods and trying to get to grips with the new methods (There could be a hideous pun buried in there somewhere - sorry!).

Anyway, thanks again for the suggestions and ideas.

Mike