Using Class Modules for One-Many Relationship

[Shannon Rotz]

I'm just learning how to use class modules in Access (2000 or XP). So far,
I've created two classes, with the data manipulation for the corresponding
tables encapsulated inside each of them

Member
Membership

The problem is that one Member can have many Memberships, and I'm having a
problem figuring out how to relate them. Ideally I'd like my calling code
to be able to say:
Member.Membersh ips(x).Update
or something like that. There are also cases where I need to update all of
a Member's memberships at once (as in the case of a death, for instance),
and I was thinking I could set up up to say
Member.Membersh ips.UpdateAll
or similar.

I'm getting the impression that in order to make that work, I need a class
called Memberships in the middle, with a collection. Is that correct? Can
someone provide me with some pointers on what code I need, or at least some
reading material which will help me wrap my head around this?

Thanks in advance.

Shannon Rotz
Vancouver, BC Canada

[rkc]

"Shannon Rotz" <sr***@shaw.c a> wrote in message
news:3o%3d.4848 31$gE.32370@pd7 tw3no...

I'm just learning how to use class modules in Access (2000 or XP). So far, I've created two classes, with the data manipulation for the corresponding
tables encapsulated inside each of them

Member
Membership

The problem is that one Member can have many Memberships, and I'm having a
problem figuring out how to relate them. Ideally I'd like my calling code
to be able to say:
Member.Membersh ips(x).Update
or something like that. There are also cases where I need to update all of a Member's memberships at once (as in the case of a death, for instance),
and I was thinking I could set up up to say
Member.Membersh ips.UpdateAll
or similar.

I'm getting the impression that in order to make that work, I need a class
called Memberships in the middle, with a collection. Is that correct? Can someone provide me with some pointers on what code I need, or at least some reading material which will help me wrap my head around this?

My guess is that what you need is a vba.collection as a part of your
Member class that holds a collection of Membership classes.

Or,

Depending on the makeup of your Membership class, you may
only need an array that holds the primary keys of the Membership
records related to each member.

[Albert D. Kallal]

Well, you can't use the class objects to "build" the relationships for you.

A class object is just a "bunch" of code stuck together, and makes it easer
to code with. So, it don't really help much in terms of a relationship
between a master and child table.

However, you can certainly make a class object that lets you "manipulate " a
member.

dim MyMember as new clsMemberShip
MyMember.Member ID = 123

MyMember.SetAll MembershipsStat us = "InActive"

So, the above code would load a membership person with key id of 123.

Then, to update all the memberships to status to inactive, then you could
use the 2nd line of code.

However, the code in the class object will simply be a bunch of sql
statements, or code that updates the child records.

It is not clear in your example what you mean by "x" in:

Member.Membersh ips(x).Update

I mean, you have to identify each child record some how...likely the key id
field of the child records is the best to use here? It is not the fact that
you want to use "x", but how did x get set to what is the issue here? What,
when, how did "x" get set to select what child record (memberships) to work
on is the issue here.

MyMember.Member ID = 123
MyMember.Member ShipID = 1388
MyMember.Member ShipStats = "Active"

In the above example, I set the Membership status to Active for the
membership record with a key id of 1388.
The code for the property MemberShipID would look like:

Public Property LET (lngMemberShipI D as long)

m_MemberShipID = lngMemberShipID

end Propeirty
The code behind the MembershipStatu s method could look like:

Public Property LET (strStatus as string)

dim sql as string

sql = "update tblMemberships set Status = '" & strStatus & "' where ID =
" & m_MembershipID
currentdb.Execu te sql
end Property

I can't really say that using a class object JUST to work with a one to many
relationship is much help. You can't use class objects in sql, or really
very much in a report. However, a class object certainly can be handy if you
have a LOT of code you are writing that must manipulate, or change the
status of a membership records. If you don't have a lot of code you are
writing to update the membership stuff, then the class object don't help
much.

You can read about "when" and "when not" to use a class object here:

I also give some example usages.
http://www.attcanada.net/%7ekallal.m.../WhyClass.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.attcanada.net/~kallal.msn

[Lyle Fairfield]

"Albert D. Kallal" <Pl************ *******@msn.com > wrote in
news:zi14d.4673 80$M95.299290@p d7tw1no:

A class object is just a "bunch" of code stuck together, and makes it
easer to code with.

Oh!

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.