AUID Number showing instead of text

[HowardHelp]

I have a table when viewed you can see the values in text form, when referenced in a form text appears as a number, which I don’t want.

The field gets its information from another table (table1a) in the main table lookup properties is the reference to the second table (table1a) in the design under Row Source and it looks like this

SELECT DISTINCTROW table1a.AUID, table1a.fldMusicType FROM table1a;

The table1a is a separate table which can be seen in the relationships in a queries because of the above code.

I quite simply want to know how if possible to view this as text in a field in my form without modifying the main table but I can modify the table1a or my queries or my form of cause.

I understand the number I see in my form is the tables AUID reference to the text I require in the table table1a.

Anyone have any simple suggestions, much appreciated.

 

[Ranman256]

in table design, look at the field's DEFAULT VALUE,... its prob set to that value. remove it.
if not, then there's some code putting it there.

 

[Pat Hartman]

Remove the table level lookup. They are a crutch and nothing but trouble as you can see.

you NEVER need to see the text in a query because users don't see queries. On a form, you would use a combo. The combo is bound to the ID and has a RowSource that selects the items from the table that defines the ID. The wizard can build this for you.

Also, always set the default for foreign keys to null. Do not leave the Access default of 0.

 

[Minty]

I think you are suffering from http://theaccessweb.com/lookupfields.htm

 

[HowardHelp]

Remove the table level lookup. They are a crutch and nothing but trouble as you can see.

you NEVER need to see the text in a query because users don't see queries. On a form, you would use a combo. The combo is bound to the ID and has a RowSource that selects the items from the table that defines the ID. The wizard can build this for you.

Also, always set the default for foreign keys to null. Do not leave the Access default of 0.

Hi i tried setting the 0 to null on the main table, do you mean use the combo on the form, if so the problem with that is the main table is from another database, the values you see is read only in the form so i want it to reflect the table, thanks through

 

[HowardHelp]

I think you are suffering from http://theaccessweb.com/lookupfields.htm

Yes the link sounds very much like the problem

 

[arnelgp]

Yes the link sounds very much like the problem

yes, to those who are novice.
but if you aged in access, and know what you are doing, it is an Angel.

 

[HowardHelp]

yes, to those who are novice.
but if you aged in access, and know what you are doing, it is an Angel.

Very Helpful