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

[dajvwis]

Hello, I saw a similar issue on this forum, however the solution was to change the source to the table, which is not plausible in my current setup.



To further elaborate, my issue is that I have a form, Review, which is using a query, Query1, as it's data source, this seems to be causing the recordset to be non-updatable. The data source must be a query because I am using a rather complex system to limit the records that the form will show.

If I were to change the source to the table, Input List, the form does function correctly, but does not limit the records correctly.

I was wondering if anyone would be able to help me find a solution to this issue.

I have attached the database, it contains only test records.

Thank you in advance for any assist you may provide. If you have any questions I will be happy to answer them as able, however I am not especially well versed in Access and this is my first attempt at creating a useful database from scratch.

I saw this link: http://allenbrowne.com/ser-61.html.

So I'm even more confident of what the issue is, presumably a "stacked query", as I've got queries using queries using queries (one of which is a union, so it is read only), or if not that then it would be "not indexed", as it is comparing a table to a query.

[aytee111]

You will need to create a temporary table for this form. Take your record source query, make it a make-table query, and run it OnOpen.

Run an update query to update the real table(s) in the OnClose event.

[aytee111]

Actually now that I come to think about it I don't think Access will let you delete a table and recreate it when it is your form's record source. Run two queries OnOpen - the first to delete all the records from the temp table and the second is your current query as an Append.

[dajvwis]

I've been thinking about it, and what I have come up with is to write an UPDATE query to change the Status of the records I do not want in the form and change the form to just pull in records without a status.


What I've come up with so far is:
UPDATE [Input List] SET [Input List].Status = 'Automatic'
WHERE EXISTS (SELECT UF.Number FROM UF WHERE UF.Number = [Input List].Number);

Which will automaticallly update a record, except it is updating all the records, not just the records that match.

AND

UPDATE [Input List] SET [Input List].Status = 'Automatic'
WHERE UF.Number=[Input List].Number;

Which will update one record, except it requests the Input of UF.Number, which I believe should be defined and I have a Query UF and it has a Field named Number.'

For Clarification, UF is a Union Query, but I am not attempting to edit any data of UF, but rather data of Input List upon which UF is built.

[aytee111]

Why don't you want to use a temp table?

[dajvwis]

Well, for one, this is intended for multiple users, and though I am not well versed in Access, it has been my understanding that that would not be optimal for a multiple user scenario.

Additionally, if using a temporary table, I would still need to update the original table as part of a QA issue, so I'd have to build a way to update the Input List in this way whether or not it was necessary for the review form or not.

I would like to keep the number of tables to the minimum necessary, and avoid deleting any data, it's a matter of transparency.

[aytee111]

What you say is true - you need to create different table names, such as
tablename & Environ("username") if you have the user name.

Another option is to use filters to get to the records you want. Form OnOpen event, do something like this:
me.filteron=true
me.filter="ID=1 and Name='Smith' and ...."

Would that work?

[dajvwis]

I believe my biggest issue is my complex system of filtering which is used to rule out records that partially match my master list.

I only want the reviewer to see records which:
a) Do not have a status (meaning they were not reviewed)
b) Do not match any combination of 2 of 3 criteria when compared to a master list (matching two of three criteria would be enough to imply that the person in question has previously been found)

I was originally working under the premise of finding all records which do not match, and then showing the remaining records that did not have a Status in the form. However, I am now working under the idea that alternatively I can assign a status to all records which DO match, and then show only records without a Status in the form.

As I am using a form for Input as well, perhaps there is a way to check the record when it is added and add a status if it does match.

[aytee111]

That makes sense. Your OnOpen would first run two update queries - one to clear all status codes, the second to update the status. Your record source would be a query based on the table with status="xx".

[dajvwis]

I'd need to avoid clearing Status and rather just Update it, but yes, that would work, and the record source would query any with a null status.

[aytee111]

By not running the first query, you are assuming that your data never changes? That it will never be removed from the form and will always want to be displayed - forever!

[dajvwis]

The status of a record on the input list should never change, it will either be a previous record, a new record, or a false record forever. If a person was false in the past and become new in the future, the past hit was still false.

Additionally, the entered data should never change as it represents a snapshot rather than a record of a persons current information.

I started trying to make the second update query, but ran into the issues listed in post 4

[aytee111]

Post 4?

Show me the SQL.

[dajvwis]

Here is the code:

What I've come up with so far is:
UPDATE [Input List] SET [Input List].Status = 'Automatic'
WHERE EXISTS (SELECT UF.Number FROM UF WHERE UF.Number = [Input List].Number);

Which will automaticallly update a record, except it is updating all the records, not just the records that match.

AND

UPDATE [Input List] SET [Input List].Status = 'Automatic'
WHERE UF.Number=[Input List].Number;

Which will update one record, except it requests the Input of UF.Number, which I believe should be defined and I have a Query UF and it has a Field named Number.'

[aytee111]

If I understand you correctly:
I created a table called Input List.
I created a query called UF.
I created an update query:
UPDATE Query7 INNER JOIN [Input List] ON Query7.number = [Input List].number SET [Input List].status = "a";

It worked. What am I missing?