This is the scenario: a claim on a specific contract comes in. The claim is entered as a row in a SQL Server database table. The front-end is an Access Form. The claim PK is the authorisation number for repairs - many claims are rejected, hence the need for the authorisation number which is the go-ahead for the repairer.
As they tab from control field to control field the Claim ID must show when they enter they claim ID field.
There is a table which has two colums: the ID (which will never go beyond 1) and the Claim PK. I have written a small Transact SQL stored procedure which reads the claim ID and returns it as ClaimID and then increments it by one and writes it back.
How do I get this value to display in the ClaimID control on entering it? I know it will have to be an event on entering the control field. I can put the data in the control field using VBA (or whatever it is), but how do I get it from the stored procedure? Functions do not want to change the database (the increment) and Macros don't support a query first getting a value and then updating the row.
I've seen a terrible hack in which the value is loaded into a form, transferred from the form to where it is needed, the table row deleted and re-inserted with the incremented value. There must be someway a bit more elegant.
Thanks very much,