can't alter filed values in select query

[Guest]

while working on my db I had some lookup tables that I was trying to include
in a form. I added, then removed a primary key to several of the tables
(allowe Access to alter the indexes). Since then I cannot alter field values
when in a select query for any of my tables. I've check the index for the
tables I was playing with and there clear. I've tired remaking the queries
(only 2 fields in the query) and it won't let me alter anything. Can someone
tell me what I've done and how to correct it?

Ian.

 

[John Vinson]

while working on my db I had some lookup tables that I was trying to include
in a form.

How are you including them? The normal way to do so would be to base
your Form on the "main" table, or a query selecting and sorting
records from the main table; and using Combo Boxes on the Form in
order to store the lookup table's key field while displaying the
human-readable text.

I added, then removed a primary key to several of the tables
(allowe Access to alter the indexes). Since then I cannot alter field values
when in a select query for any of my tables.

Please post the SQL view of the query. Some multitable queries are
updateble and some aren't - check the online help for Updateable.

I've check the index for the
tables I was playing with and there clear. I've tired remaking the queries
(only 2 fields in the query) and it won't let me alter anything. Can someone
tell me what I've done and how to correct it?

I'd say your best bet is to use the Form, rather than trying to create
a Grand Master Query that does everything. It is NOT necessary, in
most cases, to include lookup tables in the Form's recordsource.

John W. Vinson[MVP]

 

[Guest]

Hey John,

Thank you for the reponse. I'm using the LU_Tbls to have values that are
either active/inactive which get sorted in a qry (which shows only the active
values) then these are used in combo boxes in the form (and stored in the
main tbl). I've used the technique a lot to let users change which variables
they need to see. I know it's a little sloppy but it let's me avoid the
lookup wizard and I don't know any VBA to speak of. Please ignore this part
of the problem however because....

I have done something very bad to my db and I don't know what.
Specficially, I can no longer update any field in ANY query. For instance a
simple single table query's with only two fields will no longer let me check
a YES/NO box. If I go straight to the table -- no problem. But if I put the
fields into a query -- just a "bing" and no error message at the bottom.
I've even tried creating a new table. It happened after I added then deleted
some primary keys and let Access update the index. Please help -- I don't
know what I've done.

Thanks Ian.

 

[John Vinson]

Please help -- I don't
know what I've done.

I don't know either, because I cannot see your database, or the
queries, or anything else! If you create a absolutely basic, no frills
query (by selecting a Table, selecting all its fields, and saving the
query) can you update that??

It is possible that you have corrupted the database in some way. Does
Tools... Database Utilities... Compact help? If not, try creating a
new, empty database and Importing everything.


John W. Vinson[MVP]