Massive form/subform problem

[David Gunnarsson]

I'm using Access as a front-end to a MySql database, through ODBC.

I have a form set up for entering information about projects and that
form has, among other things, a subform where you can select people
associated with the project, from another table.

When I enter a new record and assign people to a project everything is
fine. Then when I change the id of the record (project id) and save,
the subform goes blank and people are still assigned to the old
project id. There are also other subforms on the main form that go
blank as well, so this seems to be a general problem conerned with
linking the subforms correctly to the main form.

I did not create this originally, I am not too familiar with Access
and I couldn't find a similiar problem in this group. I desparately
need too solve this problem so if anyone has any thoughts on it, I
would be eternally grateful.

Dave

[Albert D. Kallal]

"David Gunnarsson" <da****@hi.is > wrote in message
news:25******** *************** ***@posting.goo gle.com...

When I enter a new record and assign people to a project everything is
fine. Then when I change the id of the record (project id) and save,
the subform goes blank and people are still assigned to the old
project id.
The above sounds correct..right? You enter some people to a given project,
and save, and those people are thus (still) assigned to the project id? Is
not the above standard approach? Where else would the people be assed to?
There are also other subforms on the main form that go
blank as well, so this seems to be a general problem conerned with
linking the subforms correctly to the main form.

Hum, it sounds like the relations between the tables are not setup
correctly. Two things I would check:

1) Do the linked tables expose a timestamp field to ms-access (if the
ms-access forms are based on queries, then again, make sure a timestamp
field is exposed).

2) Are the link master, and link child fields of the same data time, and
are they setup correctly in the sub-form properties? (this setting is
critical, as it is how the relation between two tables is actually set. You
don't even have to setup a relation between the tables on the database side
(you should do this however!!!). Anyway, even after you build and setup the
relation between the tables, you still MUST set the master/child settings in
the sub-form. This is how the actual field used in the child table for the
relation is set. So, ask yourself, when you add a child record, how does it
know what parent record it belongs to? (answer: some value in the child
table is set to the key id of the parent record, and the link master/child
settings will do this for you). Of couse, this link master deal only works
for a sub-form. If you in fact launch a seperate form, then you have to use
code to setup the value for the field in the relaton.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.attcanada.net/~kallal.msn

[David Gunnarsson]

Thank so much for the quick reply! However, I think items nr. 1 and 2
that you mentioned are ok - but then again I am not an Access guru, so
I'm not sure.

But the thing that I want to happen, when I change the project id, is
that the people assignments are updated to reflect this new id - is that
possible?

Personally I think this setup is kind of stupid, the project should have
auto incrementing id's and then this wouldn't be a problem. I may just
have to go ahead and change that in the end.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

Sounds like you need "cascading update". That is a feature of the database
engine, and I don't know whether MySQL provides it. That's the reseach you
need to do -- can you do it, and how, in MySQL.

Larry Linson
Microsoft Access MVP

"David Gunnarsson" <da****@hi.is > wrote in message
news:41******** **************@ news.newsgroups .ws...

Thank so much for the quick reply! However, I think items nr. 1 and 2
that you mentioned are ok - but then again I am not an Access guru, so
I'm not sure.

But the thing that I want to happen, when I change the project id, is
that the people assignments are updated to reflect this new id - is that
possible?

Personally I think this setup is kind of stupid, the project should have
auto incrementing id's and then this wouldn't be a problem. I may just
have to go ahead and change that in the end.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Gunnarsson]

It looks like I can, so now I just have to figure out how :) Thank you
very much for the tip and all your previous help, it was greatly
appreciated!

David

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!