Access 2002 (membership/subscription records)

[Frank]

My DB has 2 tables [members] [subs] which record personal/subs details. They
are related by [membership no.] and Enforce referential integrity, Cascade
update related fields and Cascade delete related records are checked. So far
so good. In the [members] table view I can see the personal details, and by
clicking the cross to the left of each record the subscription details become
visible.

When members leave I need to archive their personal/subs details.
Copying/pasting a new table [x-members] - based on the structure of [members]
- allows me to cut/paste a record from the [members] table, but won’t carry
across the [subs] data, even if I create an [x-subs] table and relate it to
[x-members].

If it's possible then help on how to archive a record so that it displays
the same as the ‘live’ tables would be much appreciated, but please keep it
simple – I am no expert!!

Thanks
Frank

 

[Jeanette Cunningham]

Frank,
there is a trick to show members who have left without going to all the
bother of moving them to a different table.
You add an extra field to the members table. Call the field Inactive and
make it a Yes/No field, set its default value to False.
Whenever you add a new member, their Inactive field is automatically set to
false.
Members who leave get their Inactive field marked as true.
When you want your form to show active members, you include all members
where Inactive = false.
When you want to show members who left, you show all members who are
inactive (=True)
To users this looks like 2 different forms and 2 different tables, but it
makes your job as a developer easier as you only have one table and one form
to worry about.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Frank]

Hi Jeanette

Apologies for the delay in responding to your suggestion. Firstly, it's
brilliant!! Secondly it works and does exactly what I want. And thirdly, you
explained it in such a way that even a wallaby like me could understand. Huge
thanks!!

Frank (UK - Suffolk)

Frank,
there is a trick to show members who have left without going to all the
bother of moving them to a different table.
You add an extra field to the members table. Call the field Inactive and
make it a Yes/No field, set its default value to False.
Whenever you add a new member, their Inactive field is automatically set to
false.
Members who leave get their Inactive field marked as true.
When you want your form to show active members, you include all members
where Inactive = false.
When you want to show members who left, you show all members who are
inactive (=True)
To users this looks like 2 different forms and 2 different tables, but it
makes your job as a developer easier as you only have one table and one form
to worry about.


Jeanette Cunningham -- Melbourne Victoria Australia

My DB has 2 tables [members] [subs] which record personal/subs details.
They
are related by [membership no.] and Enforce referential integrity, Cascade
update related fields and Cascade delete related records are checked. So
far
so good. In the [members] table view I can see the personal details, and
by
clicking the cross to the left of each record the subscription details
become
visible.

When members leave I need to archive their personal/subs details.
Copying/pasting a new table [x-members] - based on the structure of
[members]
- allows me to cut/paste a record from the [members] table, but won't
carry
across the [subs] data, even if I create an [x-subs] table and relate it
to
[x-members].

If it's possible then help on how to archive a record so that it displays
the same as the 'live' tables would be much appreciated, but please keep
it
simple - I am no expert!!

Thanks
Frank

 

[justin]

414080