Using Access 2000 Adp with SQL Server 7.0 back-en

I have a form with a subform that represents a log entry with the subform showing notes on the log entry. I have added a pop up form to allow the user to assign the log entry to multiple cost centers if they want to. As the user tabs from the form to the sub-form, the record is added to the parent table and at that point a key, the auto-number, field is assigned. I have an insert trigger that looks at the cost center table and determines if the user has set up any cost centers, if not, it sets up a record with a default cost center. The trigger kicks off at the point that you tab from the form to the sub-form as that is the point of insertion for the new record. The problem is that it losses track of where it is at and suddenly I am looking at an entirely different entry in the database. How do I figure out what that key id on the entry's parent record so as to maintain that value to ensure the record does not flip to a different record?

Thanks for any help you can provide.

Alas, Access front-ends do lose track of the record when you use the server
equivalent of AutoNumber. We avoided this problem in Access front-ends to
several different server databases with a NextID stored procedure, and a
table on the server containing TableName and NextID fields. The stored
procedure would get the NextID, increment and save it in the table, then
return it (all "behind the scenes", without user involvement, of course), so
we were always working with an already-chosen key, not with an Identity or
Serial field.

Larry Linson
Microsoft Access MVP

"Leslee" <(E-Mail Removed)> wrote in message
news:5D83AB2F-9A38-450C-BD45-(E-Mail Removed)...
> Using Access 2000 Adp with SQL Server 7.0 back-end
>
> I have a form with a subform that represents a log entry with the subform
showing notes on the log entry. I have added a pop up form to allow the
user to assign the log entry to multiple cost centers if they want to. As
the user tabs from the form to the sub-form, the record is added to the
parent table and at that point a key, the auto-number, field is assigned. I
have an insert trigger that looks at the cost center table and determines if
the user has set up any cost centers, if not, it sets up a record with a
default cost center. The trigger kicks off at the point that you tab from
the form to the sub-form as that is the point of insertion for the new
record. The problem is that it losses track of where it is at and suddenly
I am looking at an entirely different entry in the database. How do I
figure out what that key id on the entry's parent record so as to maintain
that value to ensure the record does not flip to a different record?
>
> Thanks for any help you can provide.