RE: How to lookup the corresponding field?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Sam Kuo (sam_cy_kuo_at_yahoo.com.(donotspam))
Date: 03/22/05 

Date: Tue, 22 Mar 2005 04:01:03 -0800

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

"Sprinks" wrote:

> 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" wrote:
>
> > 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
> >
> >
> >
> >
> >

Relevant Pages

  • RE: search for file path, please help
    ... "ListBox1" is populated with every file in that sub-directory. ... in the BkLocation textbox. ... The BkLocation text box would display something like ...
    (microsoft.public.access.formscoding)
  • RE: search for file path, please help
    ... "ListBox1" is populated with every file in that sub-directory. ... in the BkLocation textbox. ... The BkLocation text box would display something like ...
    (microsoft.public.access.formscoding)
  • RE: search for file path, please help
    ... "ListBox1" is populated with every file in that sub-directory. ... in the BkLocation textbox. ... The BkLocation text box would display something like ...
    (microsoft.public.access.formscoding)
  • Re: Automatically Populate Field
    ... If the dropdown includes the Project Number, you can refer to and display it ... in a textbox by creating an unbound textbox and making it's source be: ... OnChange of the dropdown, ... use of a form where an employee selects the project name from a drop down ...
    (microsoft.public.access.forms)
  • Re: CEdit Control
    ... I would like to display the value of an int variable in a textbox. ... using the MFC Dialog based application compiled using visual studio.net 2005. ... Can anyone show me how can i display an int value in a CEdit textbox control? ...
    (microsoft.public.vc.mfc)