lookup tables references (1 Viewer)

I have a table for example tblAuthor containing 2 fields authorid as autonumber and author as text.

if in another table i create a field called author and i set its datatype to lookup and set it the the author from the tblAuthor, it's properties will change to that of a number rather than that of text. Why this happens ?

The above situation is causing me problems when i come to use cascade combo boxes

e.g

tblMainCategory
MainCategoryID :autonumber
MainCategory : text

tblCategory
CategoryID : Autonumber
Category : text
MainCategory : Lookup wizard to MainCategory in the above table

tblMainTable
bookid : autonumber
title : text
MainCategory : lookup wizard to MainCategory in tblCategory
Category : lookup wizard to Category in tblCategory

then in the form if i do the following

SELECT tblCategory.Category FROM tblCategory WHERE tblCategory.MainCategory = [Forms]![formname]![MainCategory]
I would get an empty list in the combo box.

However, if i do the above thing but the MainCategory in tblCategory will be set to text and not to lookup from the tblMainCategory, everything works fine.

Any information is greatly appreciated!

In tblMainTable you are actually storing the CategoryID and MainCategoryID rathar than the text fields so these fields need to be defined as long integer in tblMainTable. You may need to change the bound column to be column 1 which should be the ID field. Then in the column widths property, make the length of column 1, 0 so it is hidden.

In a relational database, the foreign keys are primary keys from the related tables, NOT the text values.

thx for the info! I have a clearer idea now.