Topics Search

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

Form with an Inner Join Query as a Source, recordset is not updatable
Views: 48
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 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.
Sponsored Links:

Similar posts...

 

Creating an updatable query recordset using Access 2010

Creating an updatable query recordset using Access 2010 Icon
I am trying to create a recordset in Access 2010 using a query. The query uses global variable that captures the users log-name to filter for the records that they entered. The queryexecutes, but the records are not updatable.

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.
 

Filter a form but keep recordset updatable

Filter a form but keep recordset updatable Icon
I am looking to filter a form but keep the recordset updatable. Here's what I have -

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.
 

Why does this recordset become not-updatable

Why does this recordset become not-updatable Icon
I have a form with a multi-item subform. The subform is quite complex and its data source is 5 linked tables - it's working just fine but I would like to make changes in the data so it presents more effectively. All data requiring updating are associated with only 1 table.

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.
 

Recordset Not Updatable

Recordset Not Updatable Icon
I am having a problem running a macro which calls on 4 queries. Each one relies on data from the last one, they all seem to run properly except one which displays the message "this recordset is not updatable" down the bottom of the screen.

Anyone know what the problem is here and how I can fix it
 

Basic Queries Using QBE

Basic Queries Using QBE Icon
Learning objectives: Do queries contain any data? How do I create a query? What can I do with a query? How do I create a calculated field? Why does Access add square brackets around field names? What names should I give the queries I create? What does the ampersand operator (&) do? What is a non-updatable recordset? How do I tell whether a query results in a nonupdatable recordset? (pdf file, install Acrobat Reader to read this tutorial.)
 

Record set is not updatable

Record set is not updatable Icon
I am trying to edit my query and keep getting the error Record set is not updatable.

Here is my sql

SELECT Inv.[ID], Inv.[Area], Inv.[Hosting], Inv.[Type], Inv.[Ser_num] FROM Lease INNER JOIN Inv
ON Lease.Serial = Inv.Ser_num;
 

One to many updatable query table row (KB928023)

One to many updatable query table row (KB928023) Icon
In Acc2010 we are experiencing this error with a simple one-to-many updatable query, with the following message:
The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table.The query is wrongly attempting to add a new row to the 'one' table when it adds a new row to the 'many' table.

[LINK] acknowledges this as an Acc2007 problem. It says the problem arises if the output list includes a 'complex' field (sic) from the one table. Our output list includes only one field from the one table: a non-complex long integer ID field, with 'Allow Multiple Values' set to False (and no other properties that might make this a 'complex' field).
 

Form Refresh stopped working

Form Refresh stopped working Icon
In access 2003 I have a simple bound form with a few controls for name and address. The query in the recordset returns only one record so as to reduce network traffic.

I have a procedure that edits the query criteria so as to return just one record and then requeries the form to display the next record. Only problem is I cannot get form.requery to produce any effect.

If you run the query you see the new record while the form still shows the old one. Only when you re-open the form does the new records show. I've tried editing and saving the query manually to make sure that's OK but still no joy.

Is there any way to display the new record without re-opening the form? The query that is the record source of the form is:
 

Coding standards regarding executing SQL from VBA?

Coding standards regarding executing SQL from VBA? Icon
I was just curious, what's the general opinion on coding standards/efficiency when it comes to executing an SQL query from VBA?

Basically I have a subform datasheet, and the record source is going to be set as the result of a recordset populated from an SQL query. The user enters parameters and the query runs with these parameters, populating a recordset object and then that recordset is used at the record source for the subform.

So, when doing this, I could go to create and make a query and put in [] for the parameters, and then in my code I would use a query def and fill in the parameters and then usedoCmd.RunSQL(name of my query, dbOpenDynaset) or, I could just save the query as a string variable in my VBA code, and then just use DoCmd.RunSQL(SQL,dbOpenDynaset)

It's not a tiny query, it's the amalgamation of 4 tables from the database (summation report for the higher ups) and there are 20 requested fields. But it's not the biggest I've run by far.So what might be he best way to have that? saved query or VBA string? what do coding standards say and what runs more efficiently?
 

Assign control source to popup form

Assign control source to popup form Icon
I have a main form that contains multiple memo type text boxes. I'd like to be able to double click on one of the text boxes and have a popup form open with a large text box that then gets it's source from the main forms text box, so that users can view or most of the text without having to use the scroll bar. The data also needs to be updatable.