many-to-many relationships by Graham Mandeno

[riccifs]

Hi to all,

A couples of months ago I was looking for a good way to handle a many-
to-many relationships.
I finally found this sample db: http://www.accessmvp.com/KDSnell/SampleDBs.htm.
It's simply great! Unfortunately it doesn't work in my case because
instead of using just numeric ID to relate the table I'm make use of
alphanumeric ID.

In the previous:
http://groups.google.it/group/micro...80743?lnk=gst&q=riccifs+many#9d3fc99e39b80743
Graham told me that there was a way to give to the sample db this
capability too.

Does anyone can help me?
Bye, Stefano.

 

[Amy Blankenship]

Could you be more specific in what problems you've been encountering as you
try to get it to work?

 

[riccifs]

Could you be more specific in what problems you've been encountering as you
try to get it to work?

Hi Amy,
when I try to make it works on my db I receive an error, in my
previous chat with Graham he told me that the problems was because I'm
using a PK and FK that are made up of a combination of numbers and
letters.
He said that:

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.<

But I'm still waiting for the answer.

 

[Amy Blankenship]

Hi Amy,
when I try to make it works on my db I receive an error, in my
previous chat with Graham he told me that the problems was because I'm
using a PK and FK that are made up of a combination of numbers and
letters.
He said that:

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 haven't seen the example you are talking about, but as I see it, you have
two options:

1) Add an autonumber field to your tables, and just use that.
2) Go through the code yourself, and look for where your key value is
being concatenated into the code and add the quotes in yourself.

HTH;

Amy

 

[riccifs]

I haven't seen the example you are talking about, but as I see it, you have
two options:

1) Add an autonumber field to your tables, and just use that.
2) Go through the code yourself, and look for where your key value is
being concatenated into the code and add the quotes in yourself.

HTH;

Amy

Hi Amy,
thanks for answered to me, I tried many times to change the code in
the way you suggested in the option #2, but I had no luck!
Could you have a look the Graham's module for me?
If you like to do that in your spare time, above I posted the link to
his sample db.

Hope to read about you.
Bye, Stefano.

P.S.
my e-mail address is still (e-mail address removed)

 

[Amy Blankenship]

Hi Amy,
thanks for answered to me, I tried many times to change the code in
the way you suggested in the option #2, but I had no luck!
Could you have a look the Graham's module for me?
If you like to do that in your spare time, above I posted the link to
his sample db.

If you look in the
Private Sub m_ctlAddCombo_AfterUpdate()
you will see this code:
"Insert Into " & m_sJunctionTable & " (" & m_sMasterFK & ", " & m_sLookupFK
& ") " _
& "VALUES " & "(" & m_Form(m_sMasterPK) & ", " & .Value & ");"

Where it says "(" &, Change that to "('" . Note the single quote after the
paren, but before the close quote. You want another single quote before the
next comma, and another single quote right before the close quote after the
space. Then you need another single quote before the final close paren. In
other words, you want to find the variables inside the VALUES parentheses
and enclose them in single quotes. Those quotes need to be part of the
literal string that is constructed, so they need to be on the INSIDE of the
strings that are being concatenated together.

There is a second expression:

"Delete from " & m_sJunctionTable & " where " & m_sMasterFK & "=" _
& m_Form(m_sMasterPK) & " and " & m_sLookupFK & "=" & .Value & ";"

You will need to repeat the process for that.

If you have problems following these instructions, you might want to do a
search on the web for something like Concatenate VBA expressions.

HTH;

Amy

 

[riccifs]

If you look in the
Private Sub m_ctlAddCombo_AfterUpdate()
you will see this code:
"Insert Into " & m_sJunctionTable & " (" & m_sMasterFK & ", " & m_sLookupFK
& ") " _
& "VALUES " & "(" & m_Form(m_sMasterPK) & ", " & .Value & ");"

Where it says "(" &, Change that to "('" . Note the single quote after the
paren, but before the close quote. You want another single quote before the
next comma, and another single quote right before the close quote after the
space. Then you need another single quote before the final close paren. In
other words, you want to find the variables inside the VALUES parentheses
and enclose them in single quotes. Those quotes need to be part of the
literal string that is constructed, so they need to be on the INSIDE of the
strings that are being concatenated together.

There is a second expression:

"Delete from " & m_sJunctionTable & " where " & m_sMasterFK & "=" _
& m_Form(m_sMasterPK) & " and " & m_sLookupFK & "=" & .Value & ";"

You will need to repeat the process for that.

If you have problems following these instructions, you might want to do a
search on the web for something like Concatenate VBA expressions.

HTH;

Amy

Hi Amy,
I'm still in trouble with this code, that's what I changed by myself
following your instructions:

m_db.Execute "Insert Into " & m_sJunctionTable & " (" & m_sMasterFK &
", " & m_sLookupFK & ") " _
& "VALUES " & "('" & m_Form(m_sMasterPK) & "', '" & .Value & "');"

and for the second statement:

m_db.Execute "Delete from " & m_sJunctionTable & " where " &
m_sMasterFK & " = """"" _
& m_Form(m_sMasterPK) & " and " & m_sLookupFK & "=" & .Value & ";"

But when I just open the form it asks for enter a parameter...
It's only those two part of the module that I have to modify or there
are more?
Could you give to me one more help, please

Thanks anywhere for your interest to my problem.
Stefano.

 

[Amy Blankenship]

Hi Amy,
I'm still in trouble with this code, that's what I changed by myself
following your instructions:

m_db.Execute "Insert Into " & m_sJunctionTable & " (" & m_sMasterFK &
", " & m_sLookupFK & ") " _
& "VALUES " & "('" & m_Form(m_sMasterPK) & "', '" & .Value & "');"

and for the second statement:

m_db.Execute "Delete from " & m_sJunctionTable & " where " &
m_sMasterFK & " = """"" _
& m_Form(m_sMasterPK) & " and " & m_sLookupFK & "=" & .Value & ";"

This doesn't loook at all right. I don't even _see_ that you've added any
single quotes to this expression.

But when I just open the form it asks for enter a parameter...
It's only those two part of the module that I have to modify or there
are more?

It sounds like maybe you've altered something else as well, since you have
to actually _do_ something to get those lines of code to run. What
parameters is it asking for?

 

[riccifs]

This doesn't loook at all right. I don't even _see_ that you've added any
single quotes to this expression.


It sounds like maybe you've altered something else as well, since you have
to actually _do_ something to get those lines of code to run. What
parameters is it asking for?

Hi Amy,
it asks for the PK(s) and I haven't got altered anything else.

Could you fix those two expressions for me? I mean, could you show me
where I have to put the single quotes?

Thanks, Stefano.

 

[Amy Blankenship]

On 9 Feb, 23:06, "Amy Blankenship" <[email protected]>

Hi Amy,
it asks for the PK(s) and I haven't got altered anything else.

Sounds like you have change the field names in the table and haven't changed
the SQL.

Could you fix those two expressions for me? I mean, could you show me
where I have to put the single quotes?

It seems that the basic issue is that you want to do things that require you
to know both SQL and VBA (though not at a very deep level). I don't think
I'd be doing you any favors to rewrite the expressions for you. I gave you
the general principals, and you'd be better off learning how to concatenate
strings and variables together and what constitutes valid SQL in this
situation.

You may find that your local community college has a course that will help
you. I picked up an "Access for Dummies" book when I first started learning
Access.

HTH;

Amy