I have a db with various tables including one called Policy Details. Users use a form to enter new records etc. All the policies have a reference number which acts as the primary key and which is usually generated by a separate application system.
There are rare occasions (but they do happen about once a month) where that second application system is slow or back logged and so we record records using a 'Dummy' reference, which we go back and change later.
The dummy reference has a format (eg 00001T or 00002T) but is up to the user to input it/think it up.
I want to add a button on to the form that will check for the user whether the reference is already in use and have a simple error message to say 'sorry, already in use' at that point when the user has done the availability check. As it stands, they fill in all the details, and when it comes to saving the record, there is an error message saying the changes requested are not successful as would create duplicate values in the index or primary keys.
Any clues as to the coding behind this? Or even a snazzier tool that will (when selected) in put the next available Dummy reference. I have very little expectation of the latter, but the former would be brilliant.
The Text Box is called [Risk Reference]
The relevant table is [Policy Details]
Many thanks!
There are rare occasions (but they do happen about once a month) where that second application system is slow or back logged and so we record records using a 'Dummy' reference, which we go back and change later.
The dummy reference has a format (eg 00001T or 00002T) but is up to the user to input it/think it up.
I want to add a button on to the form that will check for the user whether the reference is already in use and have a simple error message to say 'sorry, already in use' at that point when the user has done the availability check. As it stands, they fill in all the details, and when it comes to saving the record, there is an error message saying the changes requested are not successful as would create duplicate values in the index or primary keys.
Any clues as to the coding behind this? Or even a snazzier tool that will (when selected) in put the next available Dummy reference. I have very little expectation of the latter, but the former would be brilliant.
The Text Box is called [Risk Reference]
The relevant table is [Policy Details]
Many thanks!