Unanswered: IDENTITY as a default value for another column in the same table
I have a following table
Client: FirstName, LastName, Groupname ...
where all fields are requested so they do not accept NULL. The field Groupname is unique.
Now two forms - frmClient, frmGroup – are working with this table. In the form frmGroup there are all fields visible while in frmClient just the first two. In order to be able to insert a new line to the table Client from frmClient I was using a value
MS Access: Groupname = –(Autonumber) [Expr 1]
which worked perfectly in Access/MDB. Moving to Access/ADP/SQL Server this trick does not work any longer since the IDENTITY value is probably assigned later after the insertion. It is possible in SQL Server to use something similar
SQL Server: GroupName/Default Value = -(ident_current('Client'))-1
but I do not think that this would be a good solution.
Is there any other easy way how to do the job from [Expr 1]?