Form with an Inner Join Query as a Source, recordset is not updatable

[dajvwis]

I assume you mean to have Query7 be UF.

Is your Query7 a Union query?
Becuase using the code:
UPDATE UF INNER JOIN [Input List] ON UF.number=[Input List].number SET [Input List].status = "a";

Gives me the error: Operation must use an updatable query

UPDATE [Input List] INNER JOIN UF ON UF.number=[Input List].number SET [Input List].status = "a";



gives the same issue

[aytee111]

Can you attach your database? Just with the applicable tables and queries in it.

[dajvwis]

Here it is. Query2 and Query3 are my attempts at the Update Query.

[aytee111]

Very clever! I would never attempt something this complicated!

All I can suggest is to make each query an update query - your UQ1,2 and 3, then you won't need to do the UF query.

I also noticed that one of your records already has a status, your update will overwrite it. Is this ok?

Also your two tables aren't connected. I see in the Relationships that the column Result ID is a link but your data doesn't support it. If it did then you could make one query.

[dajvwis]

That's for the compliment, my first time messing around in Access.

The record with a status is just from me testing.

I suppose I could change UQ1, 2 and 3 to update queries. Then I could run all 3 OnOpen for the Review form, making the record source query for null values. That could work.

I'm looking into adding a "Refresh" function to my Review form, would that re-run the OnOpen? Or would I have to code that in?

[aytee111]

No, OnOpen will only be executed once. The code is : Me.Requery - that will go back to the record source only, not rerun the queries. You would have to run the queries in the refresh button. Is you data changing a lot, maybe by other users? You could also use the OnTimer event which would refresh every x minutes.

If your data is not correctly linked, check the box for Enforce Referencial Integrity in the Relationships window. That would prevent messed up data, say between the Input List and Master List tables. Testing can become very difficult when the data is not as it should be.

[dajvwis]

Data is going to be changing frequently, but only added, if I add Me.Requery and rerun the queries on each button click, it shouldn't be an issue. Input list is more or less junk data after it has been assigned a status, it just needs to be kept, and it will not be updated after a status is assigned. I've also changed the queries to only update records without a status.

I am also going to have a record lock on, so that only one person may be in each record at a time.

[dajvwis]

Well, it works now, so I thank you for all your help in figuring this out.

[aytee111]

You're welcome! Glad it works.