Linking Forms

[ftbadolato]

OK, I posted a note about this a few days ago, and I've made some
progress, but it still is not working.

I have a form with customer data. I've inserted a subform that tracks
the contact history. So, the PK in the Customer table is CustomerID.
CustomerID is a FK in the ContactHistory table. I have all the
relationships established in the database. On the Data tab of the
subform Link Child Fields is set to CustomerID and Link Master Fields
is set to CustomerID. When I click through the customer records on
the main form, I expect to see the related contact history records in
the subform, but the subform does nothing. However, if I change the
Source Object on the Data tab of the subform to
Query.ContactHistoryVW, I do see the records that I want to see, but
in DataSheet format. I want to see it in form view and be able to
click through those records and add new ones. Any ideas?

 

[ftbadolato]

You can probably figure out what ContactHistoryVW is, but just in
case:

SELECT ContactHistory.ContactHistoryID, ContactHistory.CustomerID,
ContactHistory.Notes, ContactHistory.ContactMethodID,
ContactMethod.Method, ContactHistory.theDate
FROM ContactMethod INNER JOIN ContactHistory ON
ContactMethod.ContactMethodID = ContactHistory.ContactMethodID;

So, this is correct, but I just want it to display like a form, and
not a datasheet. I have ContactID on both forms as hidden fields.

 

[John W. Vinson]

However, if I change the
Source Object on the Data tab of the subform to
Query.ContactHistoryVW, I do see the records that I want to see, but
in DataSheet format. I want to see it in form view and be able to
click through those records and add new ones.

Check the Default View property of the subform. It's probably Datasheet;
change it to Single or Continuous if you prefer that view.


John W. Vinson [MVP]

 

[ftbadolato]

Check the Default View property of the subform. It's probably Datasheet;
change it to Single or Continuous if you prefer that view.

John W. Vinson [MVP]

Tried that....single form does not work and continuous does not either.

 

[John W. Vinson]

OK, I posted a note about this a few days ago, and I've made some
progress, but it still is not working.

I have a form with customer data. I've inserted a subform that tracks
the contact history. So, the PK in the Customer table is CustomerID.
CustomerID is a FK in the ContactHistory table. I have all the
relationships established in the database. On the Data tab of the
subform Link Child Fields is set to CustomerID and Link Master Fields
is set to CustomerID. When I click through the customer records on
the main form, I expect to see the related contact history records in
the subform, but the subform does nothing. However, if I change the
Source Object on the Data tab of the subform to
Query.ContactHistoryVW, I do see the records that I want to see, but
in DataSheet format. I want to see it in form view and be able to
click through those records and add new ones. Any ideas?

Please explain what "does not work".

What's the Recordsource property of the mainform? Post the SQL.
What's the Recordsource property of the subform? Ditto.
What do you mean by Query.ContactHistoryVW? The Query. prefix is meaningless
to me (and I think to Access).

John W. Vinson [MVP]

 

[ftbadolato]

Please explain what "does not work".

What's the Recordsource property of the mainform? Post the SQL.
What's the Recordsource property of the subform? Ditto.
What do you mean by Query.ContactHistoryVW? The Query. prefix is meaningless
to me (and I think to Access).

John W. Vinson [MVP]

When I step through the records on the main form, I expect the records
on the subform to change to be the related records for whatever main
form record I am on. For example, if I am on Customer ABC on the main
form, I expect to see the first contact history record on the sub form
for Customer ABC. Then, I should be able to step through the contact
history records for Customer ABC on the sub form, and add new contact
history if I want.

The Record Source for the main form is just the Customer table.

Well, the Record Source for the sub form should be the ContactHistory
table if this was working properly.

Access has queries and tables, just like any DBMS. when you have both
in an Access database it prefixes them so you can tell the difference
just in case your naming conventions do not help. Below is the
ContactHistoryVW SQL.

SELECT ContactHistory.ContactHistoryID, ContactHistory.CustomerID,
ContactHistory.Notes, ContactHistory.ContactMethodID,
ContactMethod.Method, ContactHistory.theDate
FROM ContactMethod INNER JOIN ContactHistory ON
ContactMethod.ContactMethodID=ContactHistory.ContactMethodID;

 

[John W. Vinson]

When I step through the records on the main form, I expect the records
on the subform to change to be the related records for whatever main
form record I am on. For example, if I am on Customer ABC on the main
form, I expect to see the first contact history record on the sub form
for Customer ABC. Then, I should be able to step through the contact
history records for Customer ABC on the sub form, and add new contact
history if I want.

The Record Source for the main form is just the Customer table.

Well, the Record Source for the sub form should be the ContactHistory
table if this was working properly.

Access has queries and tables, just like any DBMS. when you have both
in an Access database it prefixes them so you can tell the difference
just in case your naming conventions do not help. Below is the
ContactHistoryVW SQL.

Hrm. I routinely avoid using a tablename as a query name so I've never
encountered this (and I've been using Access for a while). Learn something new
every day!

SELECT ContactHistory.ContactHistoryID, ContactHistory.CustomerID,
ContactHistory.Notes, ContactHistory.ContactMethodID,
ContactMethod.Method, ContactHistory.theDate
FROM ContactMethod INNER JOIN ContactHistory ON
ContactMethod.ContactMethodID=ContactHistory.ContactMethodID;

Does this bring up the expected records if you open it directly? Do the
CustomerID fields match?

It's possible that your form is corrupt. If it's not too complex, try junking
it and rebuilding.

John W. Vinson [MVP]

 

[ftbadolato]

OK, got it working.