By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,470 Members | 924 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,470 IT Pros & Developers. It's quick & easy.

Using Class Modules for One-Many Relationship

P: n/a
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.Memberships(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.Memberships.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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
rkc

"Shannon Rotz" <sr***@shaw.ca> wrote in message
news:3o%3d.484831$gE.32370@pd7tw3no...
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.Memberships(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.Memberships.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.


Nov 13 '05 #2

P: n/a
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.MemberID = 123

MyMember.SetAllMembershipsStatus = "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.Memberships(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.MemberID = 123
MyMember.MemberShipID = 1388
MyMember.MemberShipStats = "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 (lngMemberShipID as long)

m_MemberShipID = lngMemberShipID

end Propeirty
The code behind the MembershipStatus 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.Execute 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
Nov 13 '05 #3

P: n/a
"Albert D. Kallal" <Pl*******************@msn.com> wrote in
news:zi14d.467380$M95.299290@pd7tw1no:

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.
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.