Custom Sort

[ridgerunner]

After receiving an answer, to an earlier post, that it would be good design
to add a field to a small table in a database to aid in sorting the database
into certain sections for a report, I set that up and it worked great. The
form to be used for data entry needs to look basically like the report, but I
have run into problems with the form not allowing data entry when I set up a
simple query to join the two tables for sorting purposes. What are my
options? I read a good bit about the problems with forms and linked tables.
I do not have much experience with forms and I wondered if there is a
'work-around' solution. Should I add the special sort field to the main
table and delete the small table? This does not seem like a good solution to
me but I cannot think of anything else.
ridgerunner

 

[Allen Browne]

Make sure the little lookup table has the field marked as primary key. The
query will be read-only if the field is not uniquely indexed.

For example, you might have a CategoryID field in your main table, so you
created a table of categories with fields like this:
CategoryID Text the name of the category
SortOrder Number how to sort the categories.
Now open this categories table in design view, and make the CategoryID the
primary key.

If that's the only issue you have, you should now be able to edit all the
fields in the query that uses these 2 tables, and hence in the form that
relies on that query.

If that is not the issue, here are some other possibilities:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

 

[ridgerunner]

Thank you Allen. That solution worked.

Make sure the little lookup table has the field marked as primary key. The
query will be read-only if the field is not uniquely indexed.

For example, you might have a CategoryID field in your main table, so you
created a table of categories with fields like this:
CategoryID Text the name of the category
SortOrder Number how to sort the categories.
Now open this categories table in design view, and make the CategoryID the
primary key.

If that's the only issue you have, you should now be able to edit all the
fields in the query that uses these 2 tables, and hence in the form that
relies on that query.

If that is not the issue, here are some other possibilities:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html