Q. This does not make sense whatsoever. Linked form, yet PK is ignored (Access97)

[Jim]

Hi,

I want to know why is it that I have to add extra code to the second
form (or to the first), when the first and second forms are linked by
primary key, if I want the primary key field to be reflected in the
second form ?

This issue comes up when I want to add a new record to the second
form. The second form has its own primary key, but is linked to the
first via the first form's primary key.

I've had a problem with this issue for years, and wonder why there
isn't a patch or some standardized code to be fitted into Access to
alleviate the problem.

I once received a respoonse, which I'll never forget, which I feel is
totally irresponsible to the effect of:

"Well, Access doesn't know what you want to do with that field".

I must have paused for about 5 minutes before I said well thanks
anyway, and hung up on Microsoft Support. Believe it or not.

I mean, what ELSE would I do with it, if I used it to link both
forms/tables together.? Given all that Access does, is this too much
to expect it to fill in the primary key - the field that links both
tables together ?

I need a better answer. One that actually makes sense.

I know that my request is not unreasonable for the simple, fundamental
fact that databases are supposed to provide data look-up without the
user having to redundantly enter data. Right or wrong ?
Well, when I go to update a record as when I have a form with a button
that opens another form, and the new record is created without the
field that links the two tables together in the first place, then
something is wrong.

Please help me understand.
Jim

 

[John Vinson]

Well, when I go to update a record as when I have a form with a button
that opens another form, and the new record is created without the
field that links the two tables together in the first place, then
something is wrong.

I disagree.

Suppose I had a Form for Customers, with a button on it to open a
totally unrelated form for Widgets. Just because I open the Widgets
form from the Customers form, why should Access do anything with the
CustomerID?

The usual way to ACCOMPLISH THE TASK that you describe is to use a
Form with a Subform on it. The linking field is used as the Master
Link Field and the Child Link Field of the subform.

Ok, you choose not to do it the standard way. You want to open a new,
unrelated, independent Form using the FormOpen method. Fine; Access
lets you do that, and even lets you filter the popup form's records -
but since you're doing it all yourself, you must write some additional
VBA code to have new records automatically fill in an ID. As far as
Access is concerned, this *is a freestanding and independent form* -
to have it constrained in what ID's you may enter would be "wrong" in
my opinion!

I guess the conceptual difference is that you consider it a "linked
form". The Access developers (and I, based on experience with what the
Access developers provided) consider it an independent form.

 

[Jim Jones]

I disagree.

Suppose I had a Form for Customers, with a button on it to open a
totally unrelated form for Widgets. Just because I open the Widgets
form from the Customers form, why should Access do anything with the
CustomerID?

It wouldn't. You said Widgets is 'unrelated'.
Just like Access 'knows' the relationships, it must also know what's
not in the relationship.

The usual way to ACCOMPLISH THE TASK that you describe is to use a
Form with a Subform on it. The linking field is used as the Master
Link Field and the Child Link Field of the subform.

And I think Access does a superb on the subforms for this. But I need
a pop up as I have only so much room left.

Ok, you choose not to do it the standard way. You want to open a new,
unrelated, independent Form using the FormOpen method. Fine; Access
lets you do that, and even lets you filter the popup form's records -
but since you're doing it all yourself, you must write some additional
VBA code to have new records automatically fill in an ID.

OK, that goes back to my post earlier today, about "isnt there an
easier way to synch forms for scrolling records . .. . ".
So, I'm asking you now, is that indeed the easiest way that THAT can
be done ? (which is not my real issue as much as the ID thing is).

As far as
Access is concerned, this *is a freestanding and independent form* -
to have it constrained in what ID's you may enter would be "wrong" in
my opinion!

NO. I want to see a fix or upgrade or option that will populate the
Options menu with something like:

Treat all fields in database with same name as linked ?

Or at least on the field level, so if any problems, the single field
can be dealt with, removed from link status, etc.
I can't be the only one that feels this way about the issue.

I guess the conceptual difference is that you consider it a "linked
form". The Access developers (and I, based on experience with what the
Access developers provided) consider it an independent form.

And that's about the only thing that disappoints me about Access.
But it's a pretty big thing as I see it.

Anyway, it's not causing an immediate problem as I struggle to learn
the complicated VBA language, which I'll not master any time soon.

Thanks.
Jim

 

[Neil]

Jim,

There is an add-in to do what you want. It's called a sub form control. It
is not the Access developers fault that you choose to ignore it?? I agree
with John with the 2 forms being independant as there is a lot of events
that can happen that need to be checked when linking forms this way. for
e.g. adding a record to the 'child' form when no record in the 'parent' form
causes lost records in the child table. Moving from one record to the next
in the parent form would need a refresh on the child form, but what if this
record cant be saved because the user hasn't entered data incorrectly? These
are just two things, I dont think a Wizard or add-in would be able to do
everything you want it to. That is why they made it possible to use VB and
add your own code. And is it the Access developers fault that you dont want
to learn VBA?

Neil.

 

[Jim Jones]

Jim,

There is an add-in to do what you want. It's called a sub form control. It
is not the Access developers fault that you choose to ignore it?? I agree
with John with the 2 forms being independant as there is a lot of events
that can happen that need to be checked when linking forms this way. for
e.g. adding a record to the 'child' form when no record in the 'parent' form
causes lost records in the child table. Moving from one record to the next
in the parent form would need a refresh on the child form, but what if this
record cant be saved because the user hasn't entered data incorrectly? These
are just two things, I dont think a Wizard or add-in would be able to do
everything you want it to. That is why they made it possible to use VB and
add your own code. And is it the Access developers fault that you dont want
to learn VBA?

Neil.

Neil,

Just like a database fundamental that says redundant entering of data
is bad design, please re-read my previous post, because I can tell you
didn't quite get the gist of my original thread, and then possibly,
due to "record integrity", it may have thrown off the meaning of
John's response to you, and subsequently my reqponse to him.

The following records must be placed in the error file:

"Access developers fault"
"refuse to learn VBA"

Jim

 

[Duncan Bachen]

On Fri, 19 Mar 2004 08:05:13 GMT, Jim Jones >NO. I want to see a fix
or upgrade or option that will populate the

Options menu with something like:

Treat all fields in database with same name as linked ?

Or at least on the field level, so if any problems, the single field
can be dealt with, removed from link status, etc.
I can't be the only one that feels this way about the issue.

While you might not be the only one who like to see that as a feature,
it's not as big of an issue that MS might deal with it.

Part of this has to do with their previous precedent and how they want
to handle the independent forms.

And that's about the only thing that disappoints me about Access.
But it's a pretty big thing as I see it.

Anyway, it's not causing an immediate problem as I struggle to learn
the complicated VBA language, which I'll not master any time soon.

Yup, thats the biggest hurdle. You're in an interesting point of your
learning right now. You know your application needs to have this form
popup, but you aren't quite sure how to implement it for keeping
things in sync. Once you get an idea of how to manage it, it'll start
to make sense why it's currently setup that way -- flexibility.



-D

 

[John Vinson]

It wouldn't. You said Widgets is 'unrelated'.
Just like Access 'knows' the relationships, it must also know what's
not in the relationship.

ok... fair enough. You might have half a dozen related forms, though.

And I think Access does a superb on the subforms for this. But I need
a pop up as I have only so much room left.

Tab Pages are your friends here! You could have a Tab control with
multiple pages, a subform on each page.

OK, that goes back to my post earlier today, about "isnt there an
easier way to synch forms for scrolling records . .. . ".
So, I'm asking you now, is that indeed the easiest way that THAT can
be done ? (which is not my real issue as much as the ID thing is).

Passing the ID in the OpenArgs argument of the OpenForm method, and
setting the Default property of the ID field in the Open event, is
probably about the best approach for a popup form.

NO. I want to see a fix or upgrade or option that will populate the
Options menu with something like:

Treat all fields in database with same name as linked ?

:-{(

That would break SEVERAL of my applications, and I'd be EXTREMELY
upset if it were enforced; if it were an option, I'd never use it but
I guess I wouldn't object. You may want to go to Microsoft's website
and put the idea in as a suggestion; if it's as popular as you expect,
it might even get implemented at some point.

Or at least on the field level, so if any problems, the single field
can be dealt with, removed from link status, etc.
I can't be the only one that feels this way about the issue.


And that's about the only thing that disappoints me about Access.
But it's a pretty big thing as I see it.

Anyway, it's not causing an immediate problem as I struggle to learn
the complicated VBA language, which I'll not master any time soon.

It's managable, but I agree... it's a steep and rocky learning curve!
Don't hesitate to ask for help here, and to keep making your
thought-provoking suggestions!