Auto generate membership number - problem

Hello,

I'm currently creating a membership database.

When updating a data for a member, the database creates a scrollable 'history' of all the changes that have taken place for that member. This is like a snapshot of what the member's info was when info was updated/changed. (This needs to be like this for the purposes of the database - the users need to have this history data available).

I also have an auto-generating membership number, for when a new member is added.

However, the problem i am experiencing, is when i update a member's info and the database creates a new 'history snapshot' - it gives the new history the next membership number, rather than using it's original one.

Can anybody suggest a way that i can make the database use exsisting membership numbers when updating member info, and only use a new membership number when a new member is first added..?

I have attached a copy of the database.

I have put in two example members - The existing membership numbers for these are 1 and 2 (should be just 1), and 3 (should be membership number 2).

Many thanks for your time.

Kindest Regards,

Shane.

061205.zip