Storing text value rather than "ID"

[Sketchin]

Displaying text value rather than "ID"

Hey all,

I have a Contacts table with a States and Provinces field that is a lookup to a 2nd table that lists states and provinces (the purpose is to give a pull down menu on other forms to enter data in order to ensure continuity).

The Contacts table is storing the Autonumber ID from the lookup table rather than the text name. In most cases this isnt a problem, but I am using a reservation form where I choose a company name, and auto populate a bunch of text boxes with the address, city, province/state and postal/zip code. When this autopopulation happens, it is displaying the numerical value from the lookup table, rather than the text value.

i.e Los Angeles, 14 rather than Los Angeles, California

Is there a simple explanation for how to get it to display the text value? My method for getting all of the values is to create a combo box with every field that I need and hide the fields I don't want to see. Then I create multiple text boxes and use "=[cboCompany].[Column](2)"...and so on for the others.

Anyway, I have started rambling, so thanks for any help you can give.

 

[RainLover]

Join both Tables in your Query then select the actual text from the second table as part of your query.

Add these to your Form.

So the result is that you select a City via a Combo Box and the balance of the text fields from the second table will auto populate.

 

[missinglinq]

...a States and Provinces field that is a lookup to a 2nd table ...

Is this actually a lookup field in a table, which is to say assigned at the Table-level? If some this is a very bad idea! For the reasons why they should never be used read the article "The Evils of Lookup Fields in Tables" here:

http://mvps.org/access/lookupfields.htm

Note that this refers to lookup fields in tables not to Comboboxes in Forms being used to lookup Records. That is something else all together.

Let us know.

Linq ;0)>

 

[Sketchin]

Well...It is a lookup, even though I know I shouldn't use it!

I used the query and its working fine, the only potential issue is that I have is that I have an "invoice table" with a company field (number) that is related to the "Company table". On my invoice form, I select the company name, and it is looking for it in number format, rather than text.

The easy solution is to make the company field a text field on the Invoice table and call it a day.

Is that a bad idea? It would require me to remove the relationship from my "Invoice Table" to my "Company Table", which feels like the wrong way to go about it.

Thanks for any input.

I lied, its not a lookup at the table level. I call my "reference" tables , Lookup tables, but only pull data from them on the form level.

 

[RainLover]

On my invoice form, I select the company name, and it is looking for it in number format, rather than text.

This is actually correct. It shows the Company name but the ID Number is the Record Number. Just check the Control Source in Properties. Make sure it is the Company Not the ID.

Post back if I did not explain properly.

 

[Sketchin]

After a little figuring, I got it working with a simple select query and a combo box thats storing the Autonumber and displaying the company name.

Funny how something so simple can take so much time!

Thanks for the help guys

 

[RainLover]

It is simple when you know how but impossible if you don't.

Just remember, it is the ID that is stored. If you sort it will be by the ID not the Displayed Text.