# Solved: Record set not updatable?



## DKTaber (Oct 26, 2001)

This is probably way too complex to ask on this forum, but nothing ventured, nothing gained.

Have an Access 2000 database containing two tables: SailSched (sail schedule) and SailTypes (type of sail). SailTypes is a lookup table for SailSched when entering or editing the sail schedule to enter SailSched.SailTypeNum (long integer). I use a query containing these two tables as the control source for an input form in which the lookup table (SailTypes) is joined to SailSched thru it's SailTypeNum.

Here's the problem. There's a combo box for picking the SailTypeNum from the TypeID field in the SailTypes lookup table. But if you try to select a number from the drop-down list that appears, you get the message that the "Recordset cannot be updated". Why? The form properties are set to allow both adds and edits. No fields are locked or disabled. What would cause Access to deny the ability to add or update the SailTypeNum in SailSched? It acts as though we're trying to change the TypeID number in the lookup table (which is an autonumber) . . . but we're only using that to enter the SailTypeNum in SailSched.

Anybody have a clue?


----------



## OBP (Mar 8, 2005)

The recordset created by the Query is not updateable this sometimes happens with multiple table queries, it quite often depends on the relationship between the tables and the relationship in the query. Use a seperate query for the combo box which only has the SailTypeNum in it.


----------



## DKTaber (Oct 26, 2001)

I've tried so many other things, so I might as well try that. Will let you know.

BTW, I've been designing Access databases since about 1995, have MANY queries that have multiple tables in them, and have NEVER encountered this.


----------



## OBP (Mar 8, 2005)

So have I and I have encountered it many times. You can actually see the "record set not updatable" message flash up very briefly at the bottom of the screen when you run the query. If that is the problem.


----------



## DKTaber (Oct 26, 2001)

OBP: For some reason, the query with the two linked tables mentioned at the start of this thread now allows updates. So I need to move to the next phase of the problem, which I didn't mention at the beginning because it would have added greatly to the complexity.

Two other tables are need to be linked to SailSched, DepartureLoc and ProgramNames. The links are as follows: SailSched.LeavesFrom (a long integer) to DepartureLoc.LocID (also a long integer, autonumber); and SailSched.ProgramNum (a long integer) to ProgramNames.ProgID (also a long integer, autonumber). Both DepartureLoc and ProgramNames are nothing but lookup tables for SailSched, but must be in the query (?) that is the control source for the SailSched input form. The link for both is all recs from SailSched and only those with matching ID's in the lookup tables. If I put EITHER of these tables in the query, the fields I need to enter for a new record, or edit for an existing record (LeavesFrom and ProgramNum), become not-updatable.

Why would two lookup tables make this happen?


----------



## OBP (Mar 8, 2005)

I am not sure, I have always found datasets that are not updatable very hard to fathom.
Have you considered using more than one query to gather your data. The first has all of your tables on to filter the data, the second queries that query and may then be updatable. The other point to consider is that the table can be on the query but not actually have any fields used in the query. This acts as a (relationship) filter. I would suggest you have a play, just try the various combinations that I have outlined plus any that you can think of. If it doesn't work just delete the query. If you need serious help then it would be best to zip up the database and attach it to your post, so that we have real data to work with.


----------



## DKTaber (Oct 26, 2001)

Thanks for the help. It occurred to me that all the lookup tables had autonumber ID's which were of *no value* at all, since we're only looking up the text description of the records with the ID's). So I eliminated them, and changed the link fields in SailSched and the lookup tables to Text, and linked the tables on those. Guess what; it all now WORKS! So as far as I'm concerned, this thread is closed, problem solved.

I have found that Access often gets confused when there are a lot of tables linked together, and the link fields are similar. That is probably the case here. Would that I could have two hours with Bill Gates -- just him and me. Boy, would he get an earfull!


----------



## OBP (Mar 8, 2005)

I am glad you have solved it I found that quite often when I was still at work, You are too close to the problem and can't see the wood for the trees. Step back and explain the problem to someone else, they don't even have to know about Access and it comes to you what you couldn't see. Can you mark the Thread as "Solved" using the Thread Tools at the top of the page please?


----------

