Can't save row source on linked table

[werafa]

Hi,
I've got a linked table from another access file that has two fields with lookup tables.
the original table has row source = SELECT Offices.[ID], Offices.[Location] FROM Offices;

When I open the linked table the row source is missing.
I can add it, and the values populate the linked table correctly
but the row source is lost when I close and re-open the table.

Is this behavior normal, and what is the best way to fix it?

thanks

[HiTechCoach]

Yes, unfortunately, it is normal behavior of the [buggy] lookup field in tables.


Delete the lookups from the table's field design.

This is another one of the reasons I never used them. Many, including myself, consider them Evil.

See: The Evils of Lookup field in tables (http://hitechcoach.com/microsoft-office/access/access-links/50-access-tables-tips-and-tricks/582-the-evils-of-lookup-fields-in-tables)

[werafa]

Thanks. behind my ears hasn't dried yet.

Is there another way to force users to use only values from another table in this field?
I suppose I could use VBA - but this seems a little heavy handed

Werafa

[HiTechCoach]

Are you asking about how to prevent this for databases where other poeple are creating the tables?

[werafa]

No.

I merely want to prevent typos from creating new categories

[HiTechCoach]

I would assume you are using a form with a combo box. If yes, then try setting the combo box's Limit to list property to True/Yes.

[cleteh]

I had a similar issue. I first built a view in SQL Server called OpenAuditInstanceInternal and linked it. I then set a variable equal to the records in my view. I then set the rowsource equal to the results of the view stored in the variable sSQL.

This code is inserted into the ON Load Event for the form.

sSql = "select * from OpenAuditInstanceInternal"

lstAuditInstance.RowSource = sSql
lstAuditInstance.Requery

[werafa]

I had set it up as a table with the lookup in the table.
Have learned that his is bad practice :(

[HiTechCoach]

I had set it up as a table with the lookup in the table.
Have learned that his is bad practice :(

It is a Best Practice to only let users view forms when working with data in tables. Not queries or tables directly. Tables and queries look very much like an Excel spreadsheet. Which makes it very tempting to use. Avoid the urge to protect your data.

What makes Access a great tool for data entry is the power of bound forms.

[werafa]

Thanks Boyd,
I learned something today :)

[HiTechCoach]

You're welcome.

Glad we could assist.