check user defined reference

[emcf]

Afternoon All,

I've got another problem, no doubt simply solved by some access expert -

I have a user defined, indexed unique reference number on a form. How can I get access to check whether a reference that is entered has not been used in a previous record? The way I have the form set up just now the user will enter all the info only to be told when they move off the record that the reference number has been used before (then the whole record is not saved.... v.frustrating for the user!). I can see two possible solutions -
1) have some sort of lookup using Afterupdate(), or
2) make the reference automatically generated.

I would prefer to keep the reference user defined, anyone got any suggestions?

thanks.

 

[Tay]

You could try using Dlookup on the AfterUpdate event and maybe have a messagebox appear to tell the user that they need to use another ref (if it has already been used). Change the set-up so that they can add a new ref, without losing their data. HTH

 

[boblarson]

Sorry to correct, but you want it on the BEFORE Update, not After Update. You want to cancel their input and notify them before it tries to update the record.

I usually just loop through the recordset to see if it exists. But, that can increase time if it is a large dataset.

 

[Tay]

There's nothing wrong with being pedantic - I looked at my db where I've used Dlookup and saw BeforeUpdate but then looked at emfc's post and saw AfterUpdate in there, and just typed that. I do have a brain! Somewhere...............