Subform/Datasheet lookup problem

[Larry Marshall]

I have struggled for a while on this and can't figure it out.

I have a subform which is a child in a parent/child relationship. The
subform is a datasheet displaying a date and a foreign key. I want to
use the foreign key to do a lookup into it's master record and
populate a field in the subform datasheet with a description from the
master record. I also do not want the foreign key to display.

I have tried using a combobox but can't get it to work.

Any advice?

TIA - Larry

 

[Steve Schapel]

Larry,

Base the subform on a query which combines the parent and child
tables, and then includes the parent field which you want on your
form. You can then have a textbox on your subform which directly
displays the data you want. As regards the foreign key, it doesn't
actually need to be represented on the subform at all... but in such a
case I normally do put it there, and set its Visible property to No.

- Steve Schapel, Microsoft Access MVP

 

[John Vinson]

I have struggled for a while on this and can't figure it out.

I have a subform which is a child in a parent/child relationship. The
subform is a datasheet displaying a date and a foreign key. I want to
use the foreign key to do a lookup into it's master record and
populate a field in the subform datasheet with a description from the
master record. I also do not want the foreign key to display.

I have tried using a combobox but can't get it to work.

Could you post some more details? What are the Recordsource properties
of the form and the subform (post the SQL view)? What specific
problems did you have with the combo box?

I would expect that you could use a Combo Box based on a query of the
master table, with the key field and the description; use the key
field as the Bound Column; the foreign key as the Control Source; and
the description as the first (only) non-zero width field. Does this
not work?

 

[Larry Marshall]

Could you post some more details? What are the Recordsource properties

of the form and the subform (post the SQL view)? What specific
problems did you have with the combo box?

The form recordsource SQL probably doesn't tell you much:
SELECT *
FROM Property
ORDER BY [DateFiled], [chapter], [CaseID];

The subform source is a child table, Mailings, with the Link Parent
and Link Child fields = PropKey. PropKey is the primary key of the
Property table and PropKey in Mailings is the foreign key.

The joining of the two tables works fine in the form and subform work
fine.

The table associated with the subform contains a field
(MailCampaignDetail) which is a foreign key to another table's (also
called MailCampaignDetail) primary key, MD_Key.

Here is a quick sketch of the tables:

Property Mailings MailCampaignDetail
PropKey PK PropKey FK MD_Key PK
MailCampaignDetail FK MD_Descr
MailDate

I would expect that you could use a Combo Box based on a query of the
master table, with the key field and the description; use the key
field as the Bound Column; the foreign key as the Control Source; and
the description as the first (only) non-zero width field. Does this
not work?

I want the subform, whose recordsource is Mailings, to display
MailDate and MD_Descr. Right now the combo box displays nothing. The
rowsource for the combobox specifies:

SELECT MailCampaignDetail.MD_Descr
FROM MailCampaignDetail
WHERE ((([Mailings].[MailCampaignDetail])=[MailCampaignDetail].[MD_Key]));

The Bound Column seems like it expects only a number value rather than
a field name. So I am still lost, nothing displays.

Larry

 

[John Vinson]

I want the subform, whose recordsource is Mailings, to display
MailDate and MD_Descr. Right now the combo box displays nothing. The
rowsource for the combobox specifies:

SELECT MailCampaignDetail.MD_Descr
FROM MailCampaignDetail
WHERE ((([Mailings].[MailCampaignDetail])=[MailCampaignDetail].[MD_Key]));

The Bound Column seems like it expects only a number value rather than
a field name. So I am still lost, nothing displays.

It DOES expect a number value, since MailCampaignDetail is a
(numeric!) foreign key field.

Try instead setting the RowSource to

SELECT MailCampaignDetail.MD_KEY, MailCampaignDetail.MD_Descr
FROM MailCampaignDetail
ORDER BY MD_Descr;

Set the combo's ColumnCount property to 2; its Bound Column to 1 (so
that the value of MD_KEY in the combo will be stored in the
MailCampaignDetail foreign key field in Mailings); and the Column
Widths property to

0;1.25

so that the MD_KEY value will be stored but not displayed, and the
description will be displayed in a 1.25" wide field (obviously set
this to something appropriate for your case).