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.

121205.zip

Hi Shane

The problem here is that you are auto-numbering the records even though some of the records refer to the same member. In other words the record numbering is going to clash with membership numbering. Obviously with small numbers this is manually adjustable but once you have large numbers of records it will get out of hand.

I think what you need to do here is to have an Auto-entered serial number for the record ID and then a different style of auto-enter for membership numbering.

For the updating of the membership number can I suggest:

1. Make a new table occurrence of the membership table, call it MembershipNumberControl.

2. Relate it to your existing membership table via record number using the cartesian join. In the relationship dialog sort the MembershipNumberControl records by Membership number Descending. This ensures that the first related record by this relationship has the highest membership number.

3. Assuming that History records are obtained by duplication such a record will already have a membership number so only new records will need on. So do an auto-enter on membership number

If(Membership Number = "";MembershipNumberControl::Membership Number + 1;Membership Number)

This produces a new membership number one higher than the previous highest but does nothing if there already is a membership number.

I have altered your sample - attached.

121205.zip

Many thanks for your reply.

I'll have a look at your suggestions, and amended file straight away.

Cheers,

Shane :-)

Hello,

Just read your post, and had a look at the amended file that you produced.

Fantastic - that's exactly what i was after.

Many thanks - i appreciate it.

Cheers,

Shane.

:thankyou: