Need Updatable Recordset

[mkell1]

I'm hoping someone can throw some suggestions my way. Here's my problem:

I have an access subform with text boxes bound to fields returned in a query based on an order detail table. The Subform is being populated based on choices the user selects from 3 separate list boxes (as each list box is selected from, the next list box is populated based on the users selections). Because each list box is set up as multi-select, I am storing the values in a temp tables (first deleting the values for the specific user and then populating with the new selection(s) - this happens on the AfterUpdate event of each list box). Each of the temp tables that is populated with the users choices contains a field for the user ID (there can be large numbers of people working at the same time).

This works great for the selection process. However, once they select specific order headers to view details for, I am unable to retrieve an updatable recordset. The temp table contains the fields 'Contract', 'DelivDate', 'MealType', 'PONumber' and 'UserID'. All 5 fields are set as primary keys as I can't see excluding any of these fields as a primary. The query that the subform is based on joins this temp table to our order details table on contract ID, meal type, delivery date and PO number and uses the User ID in the where clause to only retrieve records for those orders that are in the temp table for that user. When I have it set up this way, the recordset comes back completely locked (there is no error message - the field simply acts as though it's locked property is set to True, which is is not - i checked ...). The only way that I have been able to open it up is to remove the user ID from the primary key for the table. I really don't want to do this as there could be more than one user working at one time.

Is there any way that I can get around this? I would welcome any suggestions that anyone has for me!

Thanks,

Melissa

 

[CautionMP]

mkell1,
I suspect your problem lies in one of two areas.

-Joins
-Relationships

If you open the underlying query, filter for a specific user ID and then try to change a record I willing to bet that the status bar will tell you "Recordset is not updateable", i.e. read-only, so no error is thrown.

This typically happens when you have a table with one record that when joined to other tables returns multiple records and there is no defined relationship between fields in the tables.

The relationship tells the database engine what do when you change a child record, that may change the parent record, which might change other child records.

My strength is UI and not data integrity so I can’t point you in a more specific direction without actually seeing what we are talking about.

I hope this qualifies as a suggestion.

[sub]P.S. sorry about the circular explanation, can you believe that in the bottom of one of my desk drawers is a certificate or two from MS saying I know what I’m talking about!?![/sub]

 

[CautionMP]

I guess I should provide the second part.

If your joins are out of whack and you have a key field that is not joined to something else, updating a record could cause a key violation, which in some cases will cause Access to interperate the recordset as read-only.

Try making all the join lines in your query include all records from temp table and only those records in order details where the fields are equal.

CMP

 

[mkell1]

Thanks CMP! I appreciate the suggestions and completely understand all that you have said here. My problem here is that I need to record the user ID as part of the temp table and have that as part of the key. Unfortunately, there is nothing to join that field to in the order table which is what is causing this issue. I'll have to see if I can come up with a workaround so that I can eliminate the user ID from the key of the temp table.

Thanks again!

Melissa