Hi All,
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,

if you have written a general function to make the change, you need

DoEvents

after the assignment

Hi Crystal,

Thanks for your response. Someone pointed me to a DLookup to get the value and a DoCmd.RunSQL nicely does the increment. So now everything works in only two lines. Strange that the guys who implemented the old system had to resort to such a complicated hack to get this to work. They seem to have a reasonable knowledge of Access and VBA, unlike me who is a total idiot when it comes to those two.

Peace to you and thanks again,

you're welcome, Chris