Record-specific calculated text box

[David Grant]

I have a text box in the detail section of a form, whose control source is a
hidden combo-box that queries a table not included in the form's record
source. It queries that table using the primary key from the form's record
set which is linked to that table.

Requerying cboHidden requeries ALL instances of cboHidden, and then the text
box for ALL records updates to an identical value, I assume because
cboHidden refers to every instance of it on the form, not just the one
specific to that record. How do I change the behavior such that these behave
locally to that record?

Thanks,

David

 

[Guest]

David:

There seems to be a fundamental contradiction here. If the other table is
referenced by means of the primary key of the form's underlying table that
implies that there can be more than one row referenced in the other table,
i.e. its related many-to-one to the form's table. However, you seem to want
to show a single value in the text box.

If you want to show multiple values from another table which is related
many-to-one to the form's table then the appropriate control would be a
subform control whose source object would be a form based on the referencing
(many-side) table and whose LinkMasterFields and LinkChildFields properties
would be the primary/foreign key columns on which the table's are related.

If, on the other hand, you want to show a single value from another table
which is related one-to-many to the form's table than this can be done in a
text box by means of the DLookup function in its ControlSource, e.g. in an
Orders form to show a customer's name:

=DLookup("Customer", "Customers", "CustomerID = " & [CustomerID])

CustomerID in this case is a foreign keys in the Orders form's underlying
table of course, not its primary key, it being the latter in the case of the
Customers table.

Ken Sheridan
Stafford, England

 

[David Grant]

I neglected to mention it is a one-to-one relationship between the two
tables.

tblLocation (one-to-one) => tblInterconnect (one-to-one) => tblLocation_01

Dlookup is proving to be too slow as there are many records being displayed
on the form at once. If I can do a query once in a listbox or combobox and
then find the corresponding values in VBA or something, would it be faster?

David:

There seems to be a fundamental contradiction here. If the other table is
referenced by means of the primary key of the form's underlying table that
implies that there can be more than one row referenced in the other table,
i.e. its related many-to-one to the form's table. However, you seem to
want
to show a single value in the text box.

If you want to show multiple values from another table which is related
many-to-one to the form's table then the appropriate control would be a
subform control whose source object would be a form based on the
referencing
(many-side) table and whose LinkMasterFields and LinkChildFields
properties
would be the primary/foreign key columns on which the table's are related.

If, on the other hand, you want to show a single value from another table
which is related one-to-many to the form's table than this can be done in
a
text box by means of the DLookup function in its ControlSource, e.g. in an
Orders form to show a customer's name:

=DLookup("Customer", "Customers", "CustomerID = " & [CustomerID])

CustomerID in this case is a foreign keys in the Orders form's underlying
table of course, not its primary key, it being the latter in the case of
the
Customers table.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

I neglected to mention it is a one-to-one relationship between the two
tables.

tblLocation (one-to-one) => tblInterconnect (one-to-one) => tblLocation_01

Dlookup is proving to be too slow as there are many records being displayed
on the form at once. If I can do a query once in a listbox or combobox and
then find the corresponding values in VBA or something, would it be faster?

If it's a one to one relationship, between primary keys, RI enforced, a Query
joining the two tables will still be updateable and will display all the
fields. If some Locations lack an Interconnect, use an Outer Join.

Probably - haven't tried this so I'm not certain - the three-table join should
still be updateable.

John W. Vinson [MVP]