How to lookup the corresponding field?

[Sam Kuo]

Hi,

I have a table tblCategory with 2 fields: CategoryID and Category
and another table tblType with 3 fields: CategoryID, Category and Type
and a form frmType based on tblType

Category is auto number, and both Category and Type are text.

I've managed to have the CategoryID field in tblType display Category values
instead of CategoryID values by setting its RowSource property to SELECT
DISTINCTROW [CategoryID], [Category] FROM tblCategory ORDER BY [Category];

But the CategoryID textbox in frmType still displays CategoryID values, not
the Category values as it does in tblType! Can I make the CategoryID textbox
lookup the corresponding Category values?

Many thanks for any help

 

[Marvin]

It sounds like the CategoryID textbox is unbound control and you aren't using
a query to combine the two datasources for your form.

In that case, you might try using a Domain Aggregate Function as the Control
Source for the textbox. e.g.

 

[Marvin]

Part 2:
(Continued)
e.g.
In the control source box for the textbox on the form, insert something like
=DFirst("F1","table1","ID=" & [ID])
That will pull the value in the F1 field corresponding to the current
record into the textbox.

Note: It's not a very efficient way to display the associated data. It would
probably be more efficiaent to bind the control.

Good Luck,

Marvin

 

[Sprinks]

Sam,

You have what is a very common misconception about what should be *stored*
in a table, and what is *displayed* on a form.

Each table should define aspects of a single entity. For example, a
category is completely defined by the tblCategory table--each has a unique
numeric ID (the Primary Key), and a text description. Related tables such as
tblType need only a numeric field in which to store the ID, called a Foreign
Key. You do not need nor want to duplicate the category text field in the
tblType table.

Presumably, you have done so because you want to display this useful
information on your form. You can do this in three ways. The first is by
including the text field in the RowSource of a combo box, and setting the
ColumnWidth property of the first column (the primary key) to 0". The combo
box will display the first non-zero column width after the user makes a
selection. This is what is happening in the first situation you described.
The second way is display the primary key in the combo box by setting its
width to something other than 0", and displaying the second column in another
textbox by using the Column property of the combo box, setting its Control
Source to:

=MyComboBox.Column(1)

1 is used because the columns are numbered starting with zero.

The third way is to base your form on a query based on both tables. Include
a link between the primary key in Category and its related foreign key in
tblType. Then include the text field from Category in the query. Once you
base your form on the query, you can place this informational text field.

The CategoryID textbox in frmType displays the ID because textboxes merely
display the field to which they are bound (the ControlSource). Combo boxes
display the first non-zero-width column regardless of which is the Bound
Column.

You can use any of the 3 methods above to display the text on frmType.

Hope that helps.
Sprinks

 

[Sam Kuo]

Thanks Marvin, but could you please explain a bit more about what each of the
item is?
I try to modify your line to suit my application, but it seems I don't
really understand how the line works...

=DFirst("Category","tblType","CategoryID=" & [TypeID])

What have I done wrong here??

Regards,
Sam

Part 2:
(Continued)
e.g.
In the control source box for the textbox on the form, insert something like
=DFirst("F1","table1","ID=" & [ID])
That will pull the value in the F1 field corresponding to the current
record into the textbox.

Note: It's not a very efficient way to display the associated data. It would
probably be more efficiaent to bind the control.

Good Luck,

Marvin

Hi,

I have a table tblCategory with 2 fields: CategoryID and Category
and another table tblType with 3 fields: CategoryID, Category and Type
and a form frmType based on tblType

Category is auto number, and both Category and Type are text.

I've managed to have the CategoryID field in tblType display Category values
instead of CategoryID values by setting its RowSource property to SELECT
DISTINCTROW [CategoryID], [Category] FROM tblCategory ORDER BY [Category];

But the CategoryID textbox in frmType still displays CategoryID values, not
the Category values as it does in tblType! Can I make the CategoryID textbox
lookup the corresponding Category values?

Many thanks for any help

 

[Sam Kuo]

Thanks Sprinks, that is very useful.

But I was hoping to use the Category textbox still (instead of combo box)
because I just want it to display the value, and not to give user the choice
of picking a different value from the Category combo box list.

I thought Marvin's approach of Domain Aggregate Function would do just that,
but I guess I don't really understand the line and hence unable to amend it
to suit my situation. Maybe you can help?

'Marvin' wrote:
e.g.
In the control source box for the textbox on the form, insert something like
=DFirst("F1","table1","ID=" & [ID])

I rewrite it as so for my application, but it seems wrong...
=DFirst("Category","tblType","CategoryID=" & [TypeID])

Regards,
Sam

Sam,

You have what is a very common misconception about what should be *stored*
in a table, and what is *displayed* on a form.

Each table should define aspects of a single entity. For example, a
category is completely defined by the tblCategory table--each has a unique
numeric ID (the Primary Key), and a text description. Related tables such as
tblType need only a numeric field in which to store the ID, called a Foreign
Key. You do not need nor want to duplicate the category text field in the
tblType table.

Presumably, you have done so because you want to display this useful
information on your form. You can do this in three ways. The first is by
including the text field in the RowSource of a combo box, and setting the
ColumnWidth property of the first column (the primary key) to 0". The combo
box will display the first non-zero column width after the user makes a
selection. This is what is happening in the first situation you described.
The second way is display the primary key in the combo box by setting its
width to something other than 0", and displaying the second column in another
textbox by using the Column property of the combo box, setting its Control
Source to:

=MyComboBox.Column(1)

1 is used because the columns are numbered starting with zero.

The third way is to base your form on a query based on both tables. Include
a link between the primary key in Category and its related foreign key in
tblType. Then include the text field from Category in the query. Once you
base your form on the query, you can place this informational text field.

The CategoryID textbox in frmType displays the ID because textboxes merely
display the field to which they are bound (the ControlSource). Combo boxes
display the first non-zero-width column regardless of which is the Bound
Column.

You can use any of the 3 methods above to display the text on frmType.

Hope that helps.
Sprinks

Hi,

I have a table tblCategory with 2 fields: CategoryID and Category
and another table tblType with 3 fields: CategoryID, Category and Type
and a form frmType based on tblType

Category is auto number, and both Category and Type are text.

I've managed to have the CategoryID field in tblType display Category values
instead of CategoryID values by setting its RowSource property to SELECT
DISTINCTROW [CategoryID], [Category] FROM tblCategory ORDER BY [Category];

But the CategoryID textbox in frmType still displays CategoryID values, not
the Category values as it does in tblType! Can I make the CategoryID textbox
lookup the corresponding Category values?

Many thanks for any help

 

[Marvin]

Sam,

‘DFirst’ pulls the first matching item based on the criteria provided, the
syntax being:
DFirst(expr, domain, [criteria])
In the example I gave {=DFirst("F1","table1","ID=" & [ID]} :

F1 is the name of the field in the (other) table from which you want to pull
the value associated with the ID of the item in the form’s data source.

Table1 is the name of the (other) table from which you want to pull the
value associated with the ID of the item in the form’s data source.

The ID in quotation marks (i.e. “ID=â€) is the ID field in the form’s data
source.

The ID in brackets ([ID]) refers to the ID field of the form’s data source.

In your example:

=DFirst("Category","tblType","CategoryID=" & [TypeID])

Category would be the name of a field in a table (or query) which holds the
value you want to show when it’s CategoryID field is the same as the TypeID
for the current record of the data in the form.

In a sense, what your statement says is:

“Show me, in this textbox, the Category in the table, tblType, where the
CategoryID in the tblType table is the same as the TypeID of the record in
the table (for this form) I am currently looking at.â€

I hope that helps more,

Marvin

Thanks Marvin, but could you please explain a bit more about what each of the
item is?
I try to modify your line to suit my application, but it seems I don't
really understand how the line works...

=DFirst("Category","tblType","CategoryID=" & [TypeID])

What have I done wrong here??

Regards,
Sam

Part 2:
(Continued)
e.g.
In the control source box for the textbox on the form, insert something like
=DFirst("F1","table1","ID=" & [ID])
That will pull the value in the F1 field corresponding to the current
record into the textbox.

Note: It's not a very efficient way to display the associated data. It would
probably be more efficiaent to bind the control.

Good Luck,

Marvin

Hi,

I have a table tblCategory with 2 fields: CategoryID and Category
and another table tblType with 3 fields: CategoryID, Category and Type
and a form frmType based on tblType

Category is auto number, and both Category and Type are text.

I've managed to have the CategoryID field in tblType display Category values
instead of CategoryID values by setting its RowSource property to SELECT
DISTINCTROW [CategoryID], [Category] FROM tblCategory ORDER BY [Category];

But the CategoryID textbox in frmType still displays CategoryID values, not
the Category values as it does in tblType! Can I make the CategoryID textbox
lookup the corresponding Category values?

Many thanks for any help

 

[Sprinks]

Sam,

First of all, it is not clear why your tblType table has both a CategoryID
and a Category field. Categories are defined in the Category table; all you
need is a foreign key in which to store the CategoryID.

To *display* the Category text associated with the ID, base your form on a
query that includes the CategoryID from your tblType table, and the Category
field from tblCategory, linked by the common field. DO NOT include the
CategoryID from tblCategories in your query.

Thanks Sprinks, that is very useful.

But I was hoping to use the Category textbox still (instead of combo box)
because I just want it to display the value, and not to give user the choice
of picking a different value from the Category combo box list.

I thought Marvin's approach of Domain Aggregate Function would do just that,
but I guess I don't really understand the line and hence unable to amend it
to suit my situation. Maybe you can help?

'Marvin' wrote:
e.g.
In the control source box for the textbox on the form, insert something like
=DFirst("F1","table1","ID=" & [ID])

I rewrite it as so for my application, but it seems wrong...
=DFirst("Category","tblType","CategoryID=" & [TypeID])

Regards,
Sam

Sam,

You have what is a very common misconception about what should be *stored*
in a table, and what is *displayed* on a form.

Each table should define aspects of a single entity. For example, a
category is completely defined by the tblCategory table--each has a unique
numeric ID (the Primary Key), and a text description. Related tables such as
tblType need only a numeric field in which to store the ID, called a Foreign
Key. You do not need nor want to duplicate the category text field in the
tblType table.

Presumably, you have done so because you want to display this useful
information on your form. You can do this in three ways. The first is by
including the text field in the RowSource of a combo box, and setting the
ColumnWidth property of the first column (the primary key) to 0". The combo
box will display the first non-zero column width after the user makes a
selection. This is what is happening in the first situation you described.
The second way is display the primary key in the combo box by setting its
width to something other than 0", and displaying the second column in another
textbox by using the Column property of the combo box, setting its Control
Source to:

=MyComboBox.Column(1)

1 is used because the columns are numbered starting with zero.

The third way is to base your form on a query based on both tables. Include
a link between the primary key in Category and its related foreign key in
tblType. Then include the text field from Category in the query. Once you
base your form on the query, you can place this informational text field.

The CategoryID textbox in frmType displays the ID because textboxes merely
display the field to which they are bound (the ControlSource). Combo boxes
display the first non-zero-width column regardless of which is the Bound
Column.

You can use any of the 3 methods above to display the text on frmType.

Hope that helps.
Sprinks

Hi,

I have a table tblCategory with 2 fields: CategoryID and Category
and another table tblType with 3 fields: CategoryID, Category and Type
and a form frmType based on tblType

Category is auto number, and both Category and Type are text.

I've managed to have the CategoryID field in tblType display Category values
instead of CategoryID values by setting its RowSource property to SELECT
DISTINCTROW [CategoryID], [Category] FROM tblCategory ORDER BY [Category];

But the CategoryID textbox in frmType still displays CategoryID values, not
the Category values as it does in tblType! Can I make the CategoryID textbox
lookup the corresponding Category values?

Many thanks for any help

 

[Sam Kuo]

Thanks Sprinks. Once again, much appreciated.

Sam,

First of all, it is not clear why your tblType table has both a CategoryID
and a Category field. Categories are defined in the Category table; all you
need is a foreign key in which to store the CategoryID.

To *display* the Category text associated with the ID, base your form on a
query that includes the CategoryID from your tblType table, and the Category
field from tblCategory, linked by the common field. DO NOT include the
CategoryID from tblCategories in your query.

Thanks Sprinks, that is very useful.

But I was hoping to use the Category textbox still (instead of combo box)
because I just want it to display the value, and not to give user the choice
of picking a different value from the Category combo box list.

I thought Marvin's approach of Domain Aggregate Function would do just that,
but I guess I don't really understand the line and hence unable to amend it
to suit my situation. Maybe you can help?

'Marvin' wrote:
e.g.
In the control source box for the textbox on the form, insert something like
=DFirst("F1","table1","ID=" & [ID])

I rewrite it as so for my application, but it seems wrong...
=DFirst("Category","tblType","CategoryID=" & [TypeID])

Regards,
Sam

Sam,

You have what is a very common misconception about what should be *stored*
in a table, and what is *displayed* on a form.

Each table should define aspects of a single entity. For example, a
category is completely defined by the tblCategory table--each has a unique
numeric ID (the Primary Key), and a text description. Related tables such as
tblType need only a numeric field in which to store the ID, called a Foreign
Key. You do not need nor want to duplicate the category text field in the
tblType table.

Presumably, you have done so because you want to display this useful
information on your form. You can do this in three ways. The first is by
including the text field in the RowSource of a combo box, and setting the
ColumnWidth property of the first column (the primary key) to 0". The combo
box will display the first non-zero column width after the user makes a
selection. This is what is happening in the first situation you described.
The second way is display the primary key in the combo box by setting its
width to something other than 0", and displaying the second column in another
textbox by using the Column property of the combo box, setting its Control
Source to:

=MyComboBox.Column(1)

1 is used because the columns are numbered starting with zero.

The third way is to base your form on a query based on both tables. Include
a link between the primary key in Category and its related foreign key in
tblType. Then include the text field from Category in the query. Once you
base your form on the query, you can place this informational text field.

The CategoryID textbox in frmType displays the ID because textboxes merely
display the field to which they are bound (the ControlSource). Combo boxes
display the first non-zero-width column regardless of which is the Bound
Column.

You can use any of the 3 methods above to display the text on frmType.

Hope that helps.
Sprinks

:

Hi,

I have a table tblCategory with 2 fields: CategoryID and Category
and another table tblType with 3 fields: CategoryID, Category and Type
and a form frmType based on tblType

Category is auto number, and both Category and Type are text.

I've managed to have the CategoryID field in tblType display Category values
instead of CategoryID values by setting its RowSource property to SELECT
DISTINCTROW [CategoryID], [Category] FROM tblCategory ORDER BY [Category];

But the CategoryID textbox in frmType still displays CategoryID values, not
the Category values as it does in tblType! Can I make the CategoryID textbox
lookup the corresponding Category values?

Many thanks for any help

 

[Sam Kuo]

Thanks Marvin. Once again, much appreciated.

Sam,

‘DFirst’ pulls the first matching item based on the criteria provided, the
syntax being:
DFirst(expr, domain, [criteria])
In the example I gave {=DFirst("F1","table1","ID=" & [ID]} :

F1 is the name of the field in the (other) table from which you want to pull
the value associated with the ID of the item in the form’s data source.

Table1 is the name of the (other) table from which you want to pull the
value associated with the ID of the item in the form’s data source.

The ID in quotation marks (i.e. “ID=â€) is the ID field in the form’s data
source.

The ID in brackets ([ID]) refers to the ID field of the form’s data source.

In your example:

=DFirst("Category","tblType","CategoryID=" & [TypeID])

Category would be the name of a field in a table (or query) which holds the
value you want to show when it’s CategoryID field is the same as the TypeID
for the current record of the data in the form.

In a sense, what your statement says is:

“Show me, in this textbox, the Category in the table, tblType, where the
CategoryID in the tblType table is the same as the TypeID of the record in
the table (for this form) I am currently looking at.â€

I hope that helps more,

Marvin

Thanks Marvin, but could you please explain a bit more about what each of the
item is?
I try to modify your line to suit my application, but it seems I don't
really understand how the line works...

=DFirst("Category","tblType","CategoryID=" & [TypeID])

What have I done wrong here??

Regards,
Sam

Part 2:
(Continued)
e.g.
In the control source box for the textbox on the form, insert something like
=DFirst("F1","table1","ID=" & [ID])
That will pull the value in the F1 field corresponding to the current
record into the textbox.

Note: It's not a very efficient way to display the associated data. It would
probably be more efficiaent to bind the control.

Good Luck,

Marvin

:

Hi,

I have a table tblCategory with 2 fields: CategoryID and Category
and another table tblType with 3 fields: CategoryID, Category and Type
and a form frmType based on tblType

Category is auto number, and both Category and Type are text.

I've managed to have the CategoryID field in tblType display Category values
instead of CategoryID values by setting its RowSource property to SELECT
DISTINCTROW [CategoryID], [Category] FROM tblCategory ORDER BY [Category];

But the CategoryID textbox in frmType still displays CategoryID values, not
the Category values as it does in tblType! Can I make the CategoryID textbox
lookup the corresponding Category values?

Many thanks for any help