Ken Snell help!

  • Thread starter Thread starter riccifs
  • Start date Start date
thanks, Doug.
Click to expand...

Many thanks Doug for your help, now I found them!

I have one more question to ask for.
I'd like to use the sample database called "Easy Maintenance of "Many-
To-Many" Data with a Form", but I'm having some problems to make it
works on my db.
I imported and copied all the neccesary from the sample to my db but
it doesn't seem work properly.
What I have to set about tables relationships on my db?
On my db, I have all relationships on a fields that are NOT primary
keys but only field indexed with no duplicate. Could be that the
problems?
In other words I haven't use the autonumber field to make the
relationships, but I preferred to use fields with random numbers.

Hope to receive an hand to someone,
Bye,
Stefano.
 
I've sent a note to Graham Mandeno, the author of that sample database, and
asked him to reply to your questions here in the newsgroup.
 
Hi Stefano

I am the author of that sample database, so maybe I can help out here :-)

I assume you understand the principles of setting up a many-to-many
relationship using a junction table, so I won't explain that.

Even though a many-to-many relationship is usually symmetrical, from the
point of view of one of the sides it is not. For example, in the sample DB
there is a form based on the "People" table which lists the Clubs that
person belongs to (traditionally this would be done with a subform). In this
example I call the People table the "Master table" and Clubs the "Lookup
table".

In another form, based on Clubs, the relationship is reversed.

To use the MtoMListHandler, you just need to create on your form five
controls:
one listbox to list the related items from the "lookup table"
one combo box to select new items to add to the list
two command buttons: one to add new items and one to delete items from
the list
one rectangle - any size, any position (explained below**)

Now, you just need some simple code in your form. This is from the frmClubs
form, where the control lists the members of the given club:

========== start code ==============
Private MemberList As MtoMListHandler

Private Sub Form_Load()
Set MemberList = New MtoMListHandler
With MemberList
' this is the name of the listbox control
Set .OptionList = lstMembers
' this is the name of the "Add" button
Set .AddButton = cmdAddMember
' this is the name of the "Delete" button
Set .DeleteButton = cmdDeleteMember
' this is the name of the combo box
Set .AddCombo = cboAddMember
' this is the name of the rectangle
Set .ComboMask = boxMaskMember
' set the name of the lookup table
.LookupTable = "People"
' set the name of the junction table
.JunctionTable = "People_Clubs"
' set the name of the PK of the "master table"
.MasterPK = "ClubID"
' set the name of the PK of the "lookup table"
.LookupPK = "PersonID"
' set the name of the field in the JT that is related to the master table
.MasterFK = "ClubFK"
' set the name of the field in the JT that is related to the lookup table
.LookupFK = "PersonFK"
' this is the name of a field in the lookup table that contains text to be
displayed in the list and combo boxes
' in this case, it is an expression comprising two fields:
.LookupText = "FirstName & ' ' & LastName"
End With
End Sub
============= end code ======================

This is all the code you need. Everything else is taken care of by the
MtoMListHandler class.

You say in your case that one or both of the "one side" keys is not the PK,
but another field allowing no duplicates. I don't see any reason why that
should not work. Just be sure to specify that field, not the PK, to the
MasterPK and/or LookupPK properties when you set up the class.

If you are still having trouble, post back with more information about the
structure of your three tables - field names and data types, and how the
relationships are set up.
 
Hi Stefano

I am the author of that sample database, so maybe I can help out here :-)

I assume you understand the principles of setting up a many-to-many
relationship using a junction table, so I won't explain that.

Even though a many-to-many relationship is usually symmetrical, from the
point of view of one of the sides it is not. For example, in the sample DB
there is a form based on the "People" table which lists the Clubs that
person belongs to (traditionally this would be done with a subform). In this
example I call the People table the "Master table" and Clubs the "Lookup
table".

In another form, based on Clubs, the relationship is reversed.

To use the MtoMListHandler, you just need to create on your form five
controls:
one listbox to list the related items from the "lookup table"
one combo box to select new items to add to the list
two command buttons: one to add new items and one to delete items from
the list
one rectangle - any size, any position (explained below**)

Now, you just need some simple code in your form. This is from the frmClubs
form, where the control lists the members of the given club:

========== start code ==============
Private MemberList As MtoMListHandler

Private Sub Form_Load()
Set MemberList = New MtoMListHandler
With MemberList
' this is the name of the listbox control
Set .OptionList = lstMembers
' this is the name of the "Add" button
Set .AddButton = cmdAddMember
' this is the name of the "Delete" button
Set .DeleteButton = cmdDeleteMember
' this is the name of the combo box
Set .AddCombo = cboAddMember
' this is the name of the rectangle
Set .ComboMask = boxMaskMember
' set the name of the lookup table
.LookupTable = "People"
' set the name of the junction table
.JunctionTable = "People_Clubs"
' set the name of the PK of the "master table"
.MasterPK = "ClubID"
' set the name of the PK of the "lookup table"
.LookupPK = "PersonID"
' set the name of the field in the JT that is related to the master table
.MasterFK = "ClubFK"
' set the name of the field in the JT that is related to the lookup table
.LookupFK = "PersonFK"
' this is the name of a field in the lookup table that contains text to be
displayed in the list and combo boxes
' in this case, it is an expression comprising two fields:
.LookupText = "FirstName & ' ' & LastName"
End With
End Sub
============= end code ======================

This is all the code you need. Everything else is taken care of by the
MtoMListHandler class.

You say in your case that one or both of the "one side" keys is not the PK,
but another field allowing no duplicates. I don't see any reason why that
should not work. Just be sure to specify that field, not the PK, to the
MasterPK and/or LookupPK properties when you set up the class.

If you are still having trouble, post back with more information about the
structure of your three tables - field names and data types, and how the
relationships are set up.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand




Many thanks Doug for your help, now I found them!
Click to expand...
I have one more question to ask for.
I'd like to use the sample database called "Easy Maintenance of "Many-
To-Many" Data with a Form", but I'm having some problems to make it
works on my db.
I imported and copied all the neccesary from the sample to my db but
it doesn't seem work properly.
What I have to set about tables relationships on my db?
On my db, I have all relationships on a fields that are NOT primary
keys but only field indexed with no duplicate. Could be that the
problems?
In other words I haven't use the autonumber field to make the
relationships, but I preferred to use fields with random numbers.
Click to expand...
Hope to receive an hand to someone,
Bye,
Stefano.
Click to expand...
Click to expand...

Hi Graham,
many thanks for your help.
Unfortunately I am still having problems so I will try to describe you
my tables relationships.

tblClubs (Left tbl)
------------
ClubID (Primary key autonumber)
Code_Clubs (Random alphanumeric field, indexed with no duplicate)
ClubName
ClubAddress
ClubCity
ClubPhone

tblPeople (Right tbl)
------------
PersonID (Primary key autonumbe)
Code_People (Random alphanumeric field, indexed with no duplicate)
FristName
LastName

tblPeople_Clubs (Junction tbl)
------------
People_ClubsID (Primary key autonumbe)
Code_PeopleFK
Code_ClubsFK

My relationships are made in this way:
Code_Clubs onetomany Code_ClubsFK
Code_People onetomany Code_PeopleFK

In other word for each tables, included the junction one, I set a
primary key autonumber and a code field like KP251456. After that I
used that random field to make the relationships and not the autonumbe
filed.

Where I am making mistake? and if not, how can I still use your sample
db?
I hope you can help me....
Bye,
Stefano.
 
I've sent a note to Graham Mandeno, the author of that sample database, and
asked him to reply to your questions here in the newsgroup.
Click to expand...

Hi Ken,
I really appreciate your interest to my problems!

Bye,
Stefano.
 
Hi Stefano

I assume that your relationships are on text fields (Code_Clubs and
Code_People) and I suspect that is the cause of the problem. The code in
MtoMListHandler does not enclose key values in quotes when it creates SQL
commands, so with alphanumeric values the syntax would be invalid. For
example, to delete a junction table record the generated SQL would look like
this:

Delete from tblPeople_Clubs where Code_ClubsFK=ABC12 and
Code_PeopleFK=DEF34;

Clearly the "ABC12" and "DEF34" values need to be enclosed in quotes.

When I have some spare time available (?!!) I will make some modifications
to allow for text keys, but in the mean time I cannot see what is the
purpose of your extra text keys.

I suggest you change your junction table as follows:

tblPeople_Clubs
------------
PeopleFK numeric, long
ClubsFK numeric, long

And your relationships as follows:

tblClubs.PersonID (one) - tblPeople_Clubs.PeopleFK (many)
tblClubs.ClubID (one) - tblPeople_Clubs.ClubsFK (many)

You do not need an additional autonumber PK in tblPeople_Clubs, unless that
table is the "one" side of another relationship, so make PeopleFK+ClubsFK a
composite primary key. If you DO need the autonumber PK, then create a
secondary index on PeopleFK+ClubsFK and disallow duplicates. This will
ensure that you cannot "accidentally" get two records for the same
Person/Club pair in your junction table.

Then everything should work as planned!
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

[snip]
 
Hi Stefano

I assume that your relationships are on text fields (Code_Clubs and
Code_People) and I suspect that is the cause of the problem. The code in
MtoMListHandler does not enclose key values in quotes when it creates SQL
commands, so with alphanumeric values the syntax would be invalid. For
example, to delete a junction table record the generated SQL would look like
this:

Delete from tblPeople_Clubs where Code_ClubsFK=ABC12 and
Code_PeopleFK=DEF34;

Clearly the "ABC12" and "DEF34" values need to be enclosed in quotes.

When I have some spare time available (?!!) I will make some modifications
to allow for text keys, but in the mean time I cannot see what is the
purpose of your extra text keys.

I suggest you change your junction table as follows:

tblPeople_Clubs
------------
PeopleFK numeric, long
ClubsFK numeric, long

And your relationships as follows:

tblClubs.PersonID (one) - tblPeople_Clubs.PeopleFK (many)
tblClubs.ClubID (one) - tblPeople_Clubs.ClubsFK (many)

You do not need an additional autonumber PK in tblPeople_Clubs, unless that
table is the "one" side of another relationship, so make PeopleFK+ClubsFK a
composite primary key. If you DO need the autonumber PK, then create a
secondary index on PeopleFK+ClubsFK and disallow duplicates. This will
ensure that you cannot "accidentally" get two records for the same
Person/Club pair in your junction table.

Then everything should work as planned!
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


[snip]
Hi Graham,
many thanks for your help.
Unfortunately I am still having problems so I will try to describe you
my tables relationships.
Click to expand...
tblClubs (Left tbl)
------------
ClubID (Primary key autonumber)
Code_Clubs (Random alphanumeric field, indexed with no duplicate)
ClubName
ClubAddress
ClubCity
ClubPhone
Click to expand...
tblPeople (Right tbl)
------------
PersonID (Primary key autonumbe)
Code_People (Random alphanumeric field, indexed with no duplicate)
FristName
LastName
Click to expand...
tblPeople_Clubs (Junction tbl)
Click to expand...
My relationships are made in this way:
Code_Clubs onetomany Code_ClubsFK
Code_People onetomany Code_PeopleFK
Click to expand...
In other word for each tables, included the junction one, I set a
primary key autonumber and a code field like KP251456. After that I
used that random field to make the relationships and not the autonumbe
filed.
Click to expand...
Where I am making mistake? and if not, how can I still use your sample
db?
I hope you can help me....
Bye,
Stefano.
Click to expand...
Click to expand...

Hi Graham,

I'm really thanks for your help.
I think you right. The problem is because I made up my relationships
using text keys and not numeric ones.
Until you won't make some modifications to allow for text keys, I'd
like to follow your second suggestion to make PeopleFK+ClubsFK a
composite primary key and to create a secondary index on PeopleFK
+ClubsFK and disallow duplicates.
But I don't how to do! Could you explain to me how to realize
something like that?
In fact my really need was to ensure that "accidentally" get two
records for the same Person/Club pair in your junction table.

Bye,
Stefano.
 
Hi Stefano
I'd like to follow your second suggestion to make PeopleFK+ClubsFK a
composite primary key and to create a secondary index on PeopleFK
+ClubsFK and disallow duplicates.
But I don't how to do! Could you explain to me how to realize
something like that?
Click to expand...

You don't need to do both. EITHER you (a) make PeopleFK+ClubsFK a composite
primary key OR you (b) create a secondary index on PeopleFK+ClubsFK and
disallow duplicates. You only need to do (b) if you also need an autonumber
PK to form the "one" side of another relationship.

To do (a):
Select both fields (PeopleFK+ClubsFK) in design view and click on the PK
button (with the "key" icon) on the toolbar.

To do (b):
1) Click on the "indexes" button on the toolbar ("lightning" icon)
2) In the first blank row, enter "Unique1" (or anything else you like) as
the index name
3) In the "Field name" dropdown, select PeopleFK
4) In the properties below, set Unique to "Yes"
5) In the "Field Name" cell below PeopleFK, select ClubsFK

Unless you have some other compelling reason for keeping them, I also
suggest you get rid of the random alphanumeric fields "Code_Clubs" and
"Code_People".
 
Hi Stefano
I'd like to follow your second suggestion to make PeopleFK+ClubsFK a
composite primary key and to create a secondary index on PeopleFK
+ClubsFK and disallow duplicates.
But I don't how to do! Could you explain to me how to realize
something like that?
Click to expand...

You don't need to do both. EITHER you (a) make PeopleFK+ClubsFK a composite
primary key OR you (b) create a secondary index on PeopleFK+ClubsFK and
disallow duplicates. You only need to do (b) if you also need an autonumber
PK to form the "one" side of another relationship.

To do (a):
Select both fields (PeopleFK+ClubsFK) in design view and click on the PK
button (with the "key" icon) on the toolbar.

To do (b):
1) Click on the "indexes" button on the toolbar ("lightning" icon)
2) In the first blank row, enter "Unique1" (or anything else you like) as
the index name
3) In the "Field name" dropdown, select PeopleFK
4) In the properties below, set Unique to "Yes"
5) In the "Field Name" cell below PeopleFK, select ClubsFK

Unless you have some other compelling reason for keeping them, I also
suggest you get rid of the random alphanumeric fields "Code_Clubs" and
"Code_People".
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...

Many thanks again for your handy hints, Graham!
However, I will be more than thank to you if you could post me back
the modified code you were talking about.
It will be great for me to use it on my db just leaving my
relationships as they are now.

Hope to read your news as soon.
Bye and in any case many thanks for all!
Stefano.
 
Back
Top