Q. I need to link from one field in a subform to a field in a different subform

[Jim]

Hi,

I have a main form, and 2 subforms.
I need to link from one field in one of the subforms to a field in the
other subform to establish my master/child links on a different set of
fields.

The way Access has it now, it assumes that you only need to link to
the main form's tables.

But, I have a main form/table
A second form/table
And a third form/table.

Each of the 3 tables has its own primary keys.

OK,

No, the way I've had it set up is

Main form

Subform within the main form

Subform within the Subform.

I'm not too crazy about that way, which seems to work, but which seems
to limit my flexibility.

How do I do it, where both of the subforms are on "the same plane" ?

Thanks,
Jim

 

[John Vinson]

Hi,

I have a main form, and 2 subforms.
I need to link from one field in one of the subforms to a field in the
other subform to establish my master/child links on a different set of
fields.

The way Access has it now, it assumes that you only need to link to
the main form's tables.

It's not clear from the documentation, and the Master Link Field
wizard won't offer the option, but you can do this if you set the
Master Link Field to the *fully qualified* control name: e.g.

[Forms]![mainform]![Sub1].Form![controlname]

where Sub1 is the name of the Subform control (not the form within
that control). FWIW this even works if the first subform is continuous
or even datasheet; the row that has the focus will be the master
value.

 

[Jim Jones]

Hi,

I have a main form, and 2 subforms.
I need to link from one field in one of the subforms to a field in the
other subform to establish my master/child links on a different set of
fields.

The way Access has it now, it assumes that you only need to link to
the main form's tables.

It's not clear from the documentation, and the Master Link Field
wizard won't offer the option, but you can do this if you set the
Master Link Field to the *fully qualified* control name: e.g.

[Forms]![mainform]![Sub1].Form![controlname]

where Sub1 is the name of the Subform control (not the form within
that control). FWIW this even works if the first subform is continuous
or even datasheet; the row that has the focus will be the master
value.

John,

It's not working.
The way it should makes sense to me, but it doesn't work.

Here is exactly what I have:

Link Child Fields:
[Forms]![CustomerData]![sbfService].Form![UniqueCarID]

Link Master Fields:
[Forms]![CustomerData]![sbfCustomerCarData].Form![UniqueCarId]

The "sbf" in each case is the name of the Suboform control.

Thanks,
Jim

 

[Jim Jones]

Hi,

I have a main form, and 2 subforms.
I need to link from one field in one of the subforms to a field in the
other subform to establish my master/child links on a different set of
fields.

The way Access has it now, it assumes that you only need to link to
the main form's tables.

It's not clear from the documentation, and the Master Link Field
wizard won't offer the option, but you can do this if you set the
Master Link Field to the *fully qualified* control name: e.g.

[Forms]![mainform]![Sub1].Form![controlname]

where Sub1 is the name of the Subform control (not the form within
that control). FWIW this even works if the first subform is continuous
or even datasheet; the row that has the focus will be the master
value.

John,

It's not working.
The way it should makes sense to me, but it doesn't work.

Here is exactly what I have:

Link Child Fields:
[Forms]![CustomerData]![sbfService].Form![UniqueCarID]

Link Master Fields:
[Forms]![CustomerData]![sbfCustomerCarData].Form![UniqueCarId]

The "sbf" in each case is the name of the Suboform control.

Thanks,
Jim

Sorry John, I guess I should tell you what it IS doing.

When I come out of design view, it pops up a parameter box and just
displays a blank white area, where the subform should be.

Thanks,
Jim

 

[John Vinson]

It's not working.
The way it should makes sense to me, but it doesn't work.

Here is exactly what I have:

Link Child Fields:
[Forms]![CustomerData]![sbfService].Form![UniqueCarID]

That's the problem. The Link Child Fields should be just

[UniqueCarId]

It must be the name *of a field* - the foreign key field in the
subform's Recordsource to be exact.

Link Master Fields:
[Forms]![CustomerData]![sbfCustomerCarData].Form![UniqueCarId]

The "sbf" in each case is the name of the Suboform control.

 

[Jim Jones]

It's not working.
The way it should makes sense to me, but it doesn't work.

Here is exactly what I have:

Link Child Fields:
[Forms]![CustomerData]![sbfService].Form![UniqueCarID]

That's the problem. The Link Child Fields should be just

No, I'm afraid it's still doing the same exact thing.

Jim

[UniqueCarId]

It must be the name *of a field* - the foreign key field in the
subform's Recordsource to be exact.

Link Master Fields:
[Forms]![CustomerData]![sbfCustomerCarData].Form![UniqueCarId]

The "sbf" in each case is the name of the Suboform control.

 

[Jim Jones]

It's not working.
The way it should makes sense to me, but it doesn't work.

Here is exactly what I have:

Link Child Fields:
[Forms]![CustomerData]![sbfService].Form![UniqueCarID]

That's the problem. The Link Child Fields should be just

No, I'm afraid it's still doing the same exact thing.

Jim

[UniqueCarId]

It must be the name *of a field* - the foreign key field in the
subform's Recordsource to be exact.

Link Master Fields:
[Forms]![CustomerData]![sbfCustomerCarData].Form![UniqueCarId]

The "sbf" in each case is the name of the Suboform control.

John

Part of my problem is that "CustomerNumber" is not being recorded in
teh Service table, even though I clearly refer to that subform field
being populated with the proper number.

It's just not getting recorded in table Service, where UniqueCarID IS
being recorded. !

I can't figure this out for anything.

Is it possible I can have someone like you look at this project ?
How could I send it to you?

Thanks,
Jim

 

[John Vinson]

Part of my problem is that "CustomerNumber" is not being recorded in
teh Service table, even though I clearly refer to that subform field
being populated with the proper number.

Why SHOULD it be!? The value in the master link field gets copied to
the child link field in a new record on the subform. That's ALL that
gets copied - other fields don't.

The Customer isn't getting serviced (I hope... living in an
agricultural area, I know another meaning for that term!); the *car*
is getting serviced. Surely you have a link from the Car record to the
Customer table? That's the only place the customerID should be stored
I'd think.

It's just not getting recorded in table Service, where UniqueCarID IS
being recorded. !

If you really need both fields copied, use both fields in the
Master/Child Link Fields, separated by semicolons.

 

[Jim Jones]

Why SHOULD it be!? The value in the master link field gets copied to
the child link field in a new record on the subform. That's ALL that
gets copied - other fields don't.

OK, how do I get it to copy over the present Customer Number, and
update the Service table as well as the Service Details table with the
same Customer Number?

I figure Access should fill this in - I mean what else would I do with
a field called CustomerNumber that I've used throughout the database,
orther than use it to keep my records straight.

If I was able to make master/child links on more than one field, it
would be no problem.

The Customer isn't getting serviced (I hope... living in an
agricultural area, I know another meaning for that term!); the *car*
is getting serviced. Surely you have a link from the Car record to the
Customer table? That's the only place the customerID should be stored
I'd think.

No, it should be in all subsequent records/tables/forms.
Why? Because I'm expreience problems now, where because since it's
not the case, my Service Notes table is not being updated with the
proper CustomerNumber, and record details are being copied to each
record in Service Details.

Jim

 

[Jim Jones]

John,

The Semicolon thing works. I had no idea about it.
Those are the sorts of things I can't find out unless I ask
here.

I'm still having problems, but at least I'm inching along in the
right direction.

OK, this is where I am:

Mainform
Subform
Subform (within subform)
Cmdbutton on SFwithinSF opens pop up form
called Service Details. It opens ok and
picks up service ticket and customer
number from SfwithinSf, but seems to ignore
it, as ServiceDetails now replicates the
ServiceDetails record for every customer,
as if it ignores the one-to-many relationship.

CustomerNumber will still not be recorded in
ServiceDetails table (this is the 4th and
newest table, which hands off of "Service")
Please help; Thanks for now . . . .
Jim

 

[John Vinson]

OK, how do I get it to copy over the present Customer Number, and
update the Service table as well as the Service Details table with the
same Customer Number?

You're not thinking relationally.

The whole POINT of a relational database is that you DO NOT STORE
INFORMATION REDUNDANTLY.

I'm guessing here because you've posted details of your *forms* but
not the important information - the details of your *tables*. Tables
are fundamental; forms are just tools, windows to let you see and
manage data. More inline.

I figure Access should fill this in - I mean what else would I do with
a field called CustomerNumber that I've used throughout the database,
orther than use it to keep my records straight.

If you assume that every table must contain all the information (or
even any substantial part of it) in every other table, you're missing
the point. You can use Queries to link tables together and pull
information from multiple tables onto the same screen or the same
sheet of paper. If a ServiceRecord contains information about an
automobile, it *NEED NOT AND SHOULD NOT* contain information about
that car's owner! The owner's name and contact information would be in
the Customer table (and only in the Customer table); the CustomerID
would be in the vehicles table as a link; the Service table should
have a link to the table of automobiles because that's what you need
to know - which car is being worked on.

If you need ServiceRecord information in conjunction with Customer
information (and I realize perfectly well that you will), you can use
a Query linking Customers to Automobiles to ServiceRecords. There you
have it - all the fields in all three tables, right there, take your
pick!

If I was able to make master/child links on more than one field, it
would be no problem.

You can make master/child link fields on up to TEN fields.

No, it should be in all subsequent records/tables/forms.
Why? Because I'm expreience problems now, where because since it's
not the case, my Service Notes table is not being updated with the
proper CustomerNumber, and record details are being copied to each
record in Service Details.

You'll have to post the desrciptions of these tables and how they're
related for me to see what's going on. Note that a ServiceNotes table
will NOT magically get a CustomerNumber, and record details should not
be getting copied *anywhere* - could you explain?

 

[John Vinson]

Cmdbutton on SFwithinSF opens pop up form
called Service Details. It opens ok and
picks up service ticket and customer
number from SfwithinSf, but seems to ignore
it, as ServiceDetails now replicates the
ServiceDetails record for every customer,
as if it ignores the one-to-many relationship.

Please post the Recordsource of this popup form, and the VBA code in
the command button's Click event.

 

[Jim Jones]

Please post the Recordsource of this popup form, and the VBA code in
the command button's Click event.

The Recordsource for the form is "ServiceNotes' , the name of the
table which hangs off of Service.

This statement is how the form field gets the Service Ticket number:
=[Forms]![CustomerData]![CustomerCarData].[Form]![Service].[Form]![ServiceTicket]

This statement is how the form field gets the CustomerNumber:
=[Forms]![CustomerData]![CustomerCarData].[Form]![Service].[Form]![CustomerNumber]


OK, The VBA code in the command button to open the pop-up is:

Private Sub cmdServiceDetails_Click()
On Error GoTo Err_cmdServiceDetails_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Service Details"

stLinkCriteria = "[CustomerNumber]=" & Me![CustomerNumber]

***********************
* I added the line directly above. When I did that, all it does now
* appear to delete the record. But it actually enters it in the
* Service Notes table, with no corresponding CustomerNumber
* This comment 'box' is not included in the code
* Without the statement above, the form will accept input, and make
* multiple entries in the ServiceNotes table, without CustomerNumber
***********************

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdServiceDetails_Click:
Exit Sub

Err_cmdServiceDetails_Click:
MsgBox Err.Description
Resume Exit_cmdServiceDetails_Click

End Sub



Thanks,
Jim

 

[Jim Jones]

You're not thinking relationally.

The whole POINT of a relational database is that you DO NOT STORE
INFORMATION REDUNDANTLY.

Right. I'm just trying to open this form, to the record, based on the
record presently displayed on the Service subform.

I'm guessing here because you've posted details of your *forms* but
not the important information - the details of your *tables*. Tables
are fundamental; forms are just tools, windows to let you see and
manage data. More inline.


If you assume that every table must contain all the information (or
even any substantial part of it) in every other table, you're missing
the point. You can use Queries to link tables together and pull
information from multiple tables onto the same screen or the same
sheet of paper. If a ServiceRecord contains information about an
automobile, it *NEED NOT AND SHOULD NOT* contain information about
that car's owner! The owner's name and contact information would be in
the Customer table (and only in the Customer table); the CustomerID
would be in the vehicles table as a link; the Service table should
have a link to the table of automobiles because that's what you need
to know - which car is being worked on.

Precisely. That's how it is set up.

If you need ServiceRecord information in conjunction with Customer
information (and I realize perfectly well that you will), you can use
a Query linking Customers to Automobiles to ServiceRecords. There you
have it - all the fields in all three tables, right there, take your
pick!


You can make master/child link fields on up to TEN fields.

Yes, you explained how, with the comma. But my pop up form's table
will still not reflect the record properly because the Service Ticket
number and Customer number fields are still 0.

You'll have to post the desrciptions of these tables and how they're
related for me to see what's going on. Note that a ServiceNotes table
will NOT magically get a CustomerNumber, and record details should not
be getting copied *anywhere* - could you explain?

Is it ok if I attach a graphic of my table relationships?
By the way, I hope this isn't too late, but all my tables are linked.

OK?
Jim

 

[John Vinson]

Is it ok if I attach a graphic of my table relationships?

If you can post a link to a website that would be ok; or you can type
the relationships into a message with a syntax like

tblCustomer --1:m-- tblAutos
tblAutos --1:m-- tblServiceRecords

By the way, I hope this isn't too late, but all my tables are linked.

Well, all tables should usually be linked to something - but you
certainly should NOT have all tables linked to all the other tables if
that's what you mean!

 

[Jim Jones]

If you can post a link to a website that would be ok; or you can type
the relationships into a message with a syntax like

tblCustomer --1:m-- tblAutos
tblAutos --1:m-- tblServiceRecords


Well, all tables should usually be linked to something - but you
certainly should NOT have all tables linked to all the other tables if
that's what you mean!

No! I mean it's like a split database.

OK ?

 

[Jim Jones]

If you can post a link to a website that would be ok; or you can type
the relationships into a message with a syntax like

tblCustomer --1:m-- tblAutos
tblAutos --1:m-- tblServiceRecords


Well, all tables should usually be linked to something - but you
certainly should NOT have all tables linked to all the other tables if
that's what you mean!

John,

Here it is in a nutshell. The following is how it's laid out. Does it
make sense the way I explained it?


CustomerData table
(PK) CustomerNumber
Address
phone

CustomerCarData table
(m) CustomerNumber
(PK) UniqueCarId
Car Description

Service table
(PK) ServiceTicket
(m) UniqueCarId

ServiceDetails table
(m) ServiceTicket
qty
part desc.
cost
ext.
labor

Thanks
Jim

 

[John Vinson]

On Mon, 01 Mar 2004 20:27:59 GMT, Jim Jones

Sorry I misunderstood the "linked" comment. Yes, linked tables are
fine - note that the relationships must be established (with
relational integrity enforced) in the backend, the database which
actually contains the tables.

John,

Here it is in a nutshell. The following is how it's laid out. Does it
make sense the way I explained it?


CustomerData table
(PK) CustomerNumber
Address
phone

CustomerCarData table
(m) CustomerNumber
(PK) UniqueCarId
Car Description

Service table
(PK) ServiceTicket
(m) UniqueCarId

ServiceDetails table
(m) ServiceTicket
qty
part desc.
cost
ext.
labor

But... but... you're complaining that the CustomerNumber isn't
automatically updating in the Service table. There ISN'T a
CustomerNumber in the Service table!

Clearly I've misunderstood the problem. Could you re-explain?

 

[Jim Jones]

On Mon, 01 Mar 2004 20:27:59 GMT, Jim Jones

Sorry I misunderstood the "linked" comment. Yes, linked tables are
fine - note that the relationships must be established (with
relational integrity enforced) in the backend, the database which
actually contains the tables.

CustomerNumber (using the comma, in the subform, like
you explained to get this value in here)

But... but... you're complaining that the CustomerNumber isn't
automatically updating in the Service table. There ISN'T a
CustomerNumber in the Service table!

Sorry, I fixed that now. See with comment.

Clearly I've misunderstood the problem. Could you re-explain?

It's as I explained earlier. The ServiceNotes table gets updated
without CustomerNumber or UniqueCarID, as are shown on my pop-up form.
(i think i need code in the pop up form, maybe on a beforeupdate? or
afterupdate? I can't tell - to 'force' the presently displayed
customernumber into the ServiceNotes table?)

I thought the problem was that the ServiceNotes table might need and
autofield for a primary key. So, I added one, set it to primary, and
no difference.

Actually, this sort of problem, with the Service Form/table has been a
problem in a similar way, since the beginning - hence my suspicion
earlier, about more than one field needing to be linked.

Jim

 

[John Vinson]

It's as I explained earlier. The ServiceNotes table gets updated
without CustomerNumber or UniqueCarID, as are shown on my pop-up form.

OH.

You were saying a Subform (or maybe you weren't and I was just reading
Subform).

No, if you use a Popup form THEY WILL NOT MAGICALLY FILL IN. You'll
need to use VBA code in the popup form's Open event to fill them in.

BUt my table structure question still remains, and has not been
answered:

WHY should the CustomerNumber be in the ServiceNotes table AT ALL!!?

You don't need it there. You know which ServiceRecord the note
pertains to, it's there in the foreign key; you know which car the
ServiceRecord pertains to, using its foreign key; and you know which
customer owns that car, using ITS foreign key.

The ServiceNote DOES NOT AFFECT the customer, directly. It does,
indirectly, but you have all the links you need to connect them. I
would assert that it is improper table design to have the
CustomerNumber field in *either* the ServiceRecords table *or* the
ServiceNotes table, since the identity of the customer is not a proper
attribute of either a Service entity or a Notes entity!