Re: Please Help...Using the column function within a query field

Oh boy... After reading that article.. no wonder why.........
That's exactly the merry-go-round I've been dealing with. So what do you
suggest? Just get rid of the lookup column in the original table? and put the
data? It seems inappropriate to duplicate the same data in two separate
tables (ProjEng in the Projects table -- and the same names in the AssignedTo
Table).
Do you have any other suggestions? I'm getting confused here.
Thanks in advance.
Sarah


"John Vinson" wrote:

On Thu, 30 Mar 2006 19:12:02 -0800, newsneakers
<newsneakers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I think I am trying to do something similar. I'm getting all mixed up with
the form controls. And trying to get the value of a field instead of the
underlying number -

The value of the field, as stored in your Table, *IS* the number.

That simple fact is concealed from your view by Microsoft's
misdesigned, misleading, and all but useless Lookup Wizard misfeature.

Your table CONTAINS the number; the lookup field connects that number
to a text value *in a different table*, the Lookup table.

See http://www.mvps.org/access/lookupfields.htm for a critique of the
Lookup Field type.


Basically I have this:
A table called Projects with seven columns (ProjName, City, ProjEng,
ProjPhase) etc. In the table, I have assigned all the data types as "text",
except ProjEng - which I created as a number - because it's taking that data
(that populates the Projects.ProjEng field) from an Contacts table.

Correction; it's not "taking" that data from a contacts table. It's
*linking to* the data in the contacts table. The text exists in the
Contacts table, and only in the Contacts table.

Everything worked just fine thus far.
Then I added a Projects Form with a link from Issues Table, to edit the
Projects.

What sort of "link"?

When all of the fields in the Projects Form were text-only, everything was
fine. However I changed the fields on the form to combo boxes in order to
limit the selection to a list (to prevent typographical errors in the
Projects Table, hoping to prevent duplicate records because of typos.
Again, all of the drop down lists (on the Projects Form) work fine (pulling
the data from the Projects Table to the combo drop down list- and -also
updating the selection back to the Projects Table according to the selection.
However, since the ProjEng column is a number-type in the Projects Table it
is showing as a number in the drop down list - instead of the names.

Change the ColumnWidths property of this combo box so that the column
width of the number field is 0. E.g. if your combo is based on a query
selecting the ProjEng ID and the Contacts LastName, you can use a
ColumnWidths property of

0;1

to conceal the ID and display the name.


John W. Vinson[MVP]

.

Relevant Pages

  • Re: serial numbers
    ... I want to put these items in a drop-down field were users select the item on input, say item "A" needs to be added to the database. ... The other thing also is that on creation of a new record these serial number values do not increase automatically but only for the value linked to the Item selection. ... select the item, use a second field Item_SN to lookup the item serial number from the table containing the unique items. ...
    (comp.databases.filemaker)
  • Wire up a ComboBox.
    ... How do I wire up ComboBox to my data table to store my selection and to my lookup table that allows me to make that selection: ... DataBindings configuration properties of the ComboBox ...
    (microsoft.public.dotnet.framework.windowsforms)
  • Re: lookup databinding combobox using stored procedures
    ... data table or something of that nature, and then bind the drop down list to ... table that has its own ID field, the ID field from the lookup table, and ... DELETE commands have been set to the stored procedures and I can call them ... OtherDataTable the combo box must display the appropreate selection. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Lookup Table Field Retrieval / Separating Databases
    ... lookup tables are essential to a smoothly-running ... wish for the user to enter general Physical and Performance data such as ... I cannot understand why the actual selection in the combo box ... clients, and I want to keep the data for each client completely separate. ...
    (microsoft.public.access.gettingstarted)
  • auto-enter sometimes?
    ... Field A displays a drop-down from a value list. ... One of the fields is a calculation. ... value," then editing field A causes a new lookup, no match, and the ... selection. ...
    (comp.databases.filemaker)