Recordset not updateable...

[Brad Pears]

I have a form that is based on a query. Originally, this query simply had
one table in it with some where clauses etc... and the user could modify
data on the form that was bound to this query...

I have now added another object to the initial query this form is bound to
so that now the query includes two objects... the initial table (Customers)
and a query object that is a "group by" query on the same customers table -
grouped by on the "customer ID" - with another "where" clause field. I know
this sounds confusing but the effect is that the query is only producing ONE
row per customer based on the where clause - rather than m,ultiples rows per
customer as it was before...

However, now what is happening is that the user can not change any data on
the form. It now says that "This recordset is not updateable". The rights
are all ok on the form and query. When I remove the 2nd object (the query
object) as a test it is updateable again - so it obviously has something to
do with that 2nd query object being part of this original query... (clear as
mud??)

Any thoughts on why this might be? Does it have to do with the "group by"
query I am using because without this, the user would see multiple records
for the customer and I do not want that...

Here is the Access generated SQL from the query in question... This is not
an updateable recordset...

"SELECT Customers.CustomerID, Customers.CustomerStatus, Customers.LastName,
Customers.FirstName, Customers.PrimarySalesperson,
Customers.DateSARActivated, Customers.RequiresSalesAction,
Customers.IsSARDealtWith
FROM Customers INNER JOIN qryCustSAR_Distinct ON Customers.CustomerID =
qryCustSAR_Distinct.CustomerID
WHERE Customers.PrimarySalesperson Like [forms]![frmSARMaint].[cboSales]
ORDER BY Customers.PrimarySalesperson, Customers.DateSARActivated;"


Thanks,

Brad

 

[Rick Brandt]

I have a form that is based on a query. Originally, this query simply
had one table in it with some where clauses etc... and the user could
modify data on the form that was bound to this query...

I have now added another object to the initial query this form is
bound to so that now the query includes two objects... the initial
table (Customers) and a query object that is a "group by" query on
the same customers table - grouped by on the "customer ID" - with
another "where" clause field. I know this sounds confusing but the
effect is that the query is only producing ONE row per customer based
on the where clause - rather than m,ultiples rows per customer as it
was before...
However, now what is happening is that the user can not change any
data on the form. It now says that "This recordset is not
updateable". The rights are all ok on the form and query. When I
remove the 2nd object (the query object) as a test it is updateable
again - so it obviously has something to do with that 2nd query
object being part of this original query... (clear as mud??)

Any thoughts on why this might be? Does it have to do with the "group
by" query I am using because without this, the user would see
multiple records for the customer and I do not want that...

Here is the Access generated SQL from the query in question... This
is not an updateable recordset...

"SELECT Customers.CustomerID, Customers.CustomerStatus,
Customers.LastName, Customers.FirstName, Customers.PrimarySalesperson,
Customers.DateSARActivated, Customers.RequiresSalesAction,
Customers.IsSARDealtWith
FROM Customers INNER JOIN qryCustSAR_Distinct ON Customers.CustomerID
= qryCustSAR_Distinct.CustomerID
WHERE Customers.PrimarySalesperson Like
[forms]![frmSARMaint].[cboSales] ORDER BY
Customers.PrimarySalesperson, Customers.DateSARActivated;"

Yep, Group By queries are never updateable.

Why would a query of just the Customers table ever show duplicate rows?
Surely each CustomerID only occurs once in the Customers table? I fail to
see why eliminating the second query would produce multiple rows per
customer.

 

[Brad Pears]

Thanks for the response - just as I had thought it would likely be...

The multiple rows are there because the query object is based on the many
side of a one-many relationship with customers - that table being the
customer "Notes" table. A customer can (and does) have many notes on file.
There are fields in the notes table that I needed to filter on to get just
the list of customers that matched that criteria. So, what would happen is
that without the "group-by" on customer ID, it would list as many rows for
the customer as there were notes on file for that cust. I only wanted to see
the specific customers (one per)

Hope that makes it a little clearer!

Brad

I have a form that is based on a query. Originally, this query simply
had one table in it with some where clauses etc... and the user could
modify data on the form that was bound to this query...

I have now added another object to the initial query this form is
bound to so that now the query includes two objects... the initial
table (Customers) and a query object that is a "group by" query on
the same customers table - grouped by on the "customer ID" - with
another "where" clause field. I know this sounds confusing but the
effect is that the query is only producing ONE row per customer based
on the where clause - rather than m,ultiples rows per customer as it
was before...
However, now what is happening is that the user can not change any
data on the form. It now says that "This recordset is not
updateable". The rights are all ok on the form and query. When I
remove the 2nd object (the query object) as a test it is updateable
again - so it obviously has something to do with that 2nd query
object being part of this original query... (clear as mud??)

Any thoughts on why this might be? Does it have to do with the "group
by" query I am using because without this, the user would see
multiple records for the customer and I do not want that...

Here is the Access generated SQL from the query in question... This
is not an updateable recordset...

"SELECT Customers.CustomerID, Customers.CustomerStatus,
Customers.LastName, Customers.FirstName, Customers.PrimarySalesperson,
Customers.DateSARActivated, Customers.RequiresSalesAction,
Customers.IsSARDealtWith
FROM Customers INNER JOIN qryCustSAR_Distinct ON Customers.CustomerID
= qryCustSAR_Distinct.CustomerID
WHERE Customers.PrimarySalesperson Like
[forms]![frmSARMaint].[cboSales] ORDER BY
Customers.PrimarySalesperson, Customers.DateSARActivated;"

Yep, Group By queries are never updateable.

Why would a query of just the Customers table ever show duplicate rows?
Surely each CustomerID only occurs once in the Customers table? I fail to
see why eliminating the second query would produce multiple rows per
customer.