Form with an Inner Join Query as a Source, recordset is not updatable
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 datasource 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.
it contains only test records.
However I am not especially well versed in Access and this is my firstattempt at creating a useful database from scratch.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
The code uses ADO. My goal is to split this database to create a multi-user environment. The source table has a primary key and there are no calculated fields.
Can someone please explain how I can make this recordset updatable.
I have a table that I want to be able to update.
I have a query that I want to filter it based on.
When I use the table joined to the query for a recordset, it does exactly what I want except for not being updatable.
What is another way to make the form only display the records I want?
There are 2 fields that need to be married here, VendorNo to APVNDR and InvoiceNo to APINV. A single field will not always produce non-duplicative records, so I would need to test the two of them.
I made a form to be able to go through and fill in other empty fields in that table, but it is telling me the Recordset is not updatable.
What would make a recordset not updatable
Create View statement says..
"If the query defined by the SELECT statement is updatable,
then the view is also updatable. Otherwise the view is read-only".
Further,.. Help text for troubleshooting forms under the topic
of unable to update fields says..
"Underlying query or SQL statement for the form may
Not be 'updatable'".
HOW/WHERE do I tell M/S ACCESS that I want the
query to be Updatable?
(Also,.. does this mean that only ONE of the underlying
tables of the query will actually be updatable??.. if, in fact,
I am able to declare the query or Select statement as updatable)
The data needs some minor adjustment as part of the presentation so I create an additional table extracting the needed pieces from the original form datasource query then create the newsequencing key using a VBA function created especially for this purpose.
Finally I then modify the original form datasource query to include this new table but as soon as I adjust the form to include the new sequencing field Access declares the recordset un-updatable.
I didn't break any rules that I know of. If I remove the table from the datasource SQL it works just fine but I can't get the sequencing I need without shutting off my ability to update thedata in the one underlying table that needs updating.
I can run the query and see all the data.
Why is it not updatable? Through the Query or if I set up a form to point to the Query.
Does this have to do with the way I have the joins set up?
Note: the tables a linked to another Access Database.
If I open the tables individually I can edit them.I just dont know what can be wrong with my Query/joins
Table A (all license)
Table B (Owners)
Table C (alternate owners)
1 to Many relationship/join set up from A to B and A to C
Anyone know what the problem is here and how I can fix it
UPDATE tblMatrix INNER JOIN qrySelectMaxDate ON tblAll.SoldTo = qrySelectMaxDate.SoldTo SET tblMatrix.LastInvoicedDate = [qrySelectMaxDate]![MaxOfInvoiceDate];
and I am receiving an error "operation must use an updatable query".
Googled it but not of the explantation seems to be aplicable to my query
SELECT AACCUReport.PriEntityID, AACCUReport.TPName, AACCUReport.RACF_No, AACCUReport.Status FROM AACCUReport GROUP BY AACCUReport.PriEntityID, AACCUReport.TPName, AACCUReport.RACF_No, AACCUReport.Status HAVING (((AACCUReport.Status
I put the backend in a server and sent the front end to the users.
However, the user can't update his records and the form is showing that the recordset is not updatable in the status bar.!
in addition to that, I have a module that connects to the backend dynamically to search for a record. the code is running in my pc perfectly but not running in any of the users showing
run-time error '3051'
stating that the backend database is open exclusively or in read-only mode eventhough I gave the users full permission to the backend.