Recordset used to be updatable - not anymore...(ADP)

[sk1hotpepr]

I'm having trouble trying to figure out why a recordset is no longer updatable on a form I'm using. The table has a primary key, so that's not it. I haven't done anything different. Can a user entering information somehow create an error that would no longer make a recordset editable?

Here is my recordsource for the form if it will help.

SELECT     ProjectBidInfo.ProjectNumber, ProjectBidInfo.GSFAdd, ProjectBidInfo.GSFRenov, ProjectBidInfo.SiteAcreage, ProjectBidInfo.ProgBldg, 
                      ProjectBidInfo.ProgSite, ProjectBidInfo.ProgMech, ProjectBidInfo.ProgElec, ProjectBidInfo.ProgPlb, ProjectBidInfo.ProgAsb, ProjectBidInfo.SDSite, 
                      ProjectBidInfo.SDBldg, ProjectBidInfo.SDMech, ProjectBidInfo.SDElec, ProjectBidInfo.SDPlb, ProjectBidInfo.SDAsb, ProjectBidInfo.CDBldg, 
                      ProjectBidInfo.CDSite, ProjectBidInfo.CDMech, ProjectBidInfo.CDElec, ProjectBidInfo.CDPlb, ProjectBidInfo.CDAsb, ProjectBidInfo.BidBldg, 
                      ProjectBidInfo.BidSite, ProjectBidInfo.BidElec, ProjectBidInfo.BidMech, ProjectBidInfo.BidPlb, ProjectBidInfo.DDSite, ProjectBidInfo.BidAsb, 
                      ProjectBidInfo.DDBldg, ProjectBidInfo.DDMech, ProjectBidInfo.DDElec, ProjectBidInfo.DDPlb, ProjectBidInfo.DDAsb, SUM(ProjectContractor.ActBid) 
                      AS MyBid
FROM         ProjectBidInfo LEFT OUTER JOIN
                      ProjectContractor ON ProjectBidInfo.ProjectNumber = ProjectContractor.ProjectNumber
GROUP BY ProjectBidInfo.ProjectNumber, ProjectBidInfo.GSFAdd, ProjectBidInfo.GSFRenov, ProjectBidInfo.SiteAcreage, ProjectBidInfo.ProgBldg, 
                      ProjectBidInfo.ProgSite, ProjectBidInfo.ProgMech, ProjectBidInfo.ProgElec, ProjectBidInfo.ProgPlb, ProjectBidInfo.ProgAsb, ProjectBidInfo.SDSite, 
                      ProjectBidInfo.SDBldg, ProjectBidInfo.SDMech, ProjectBidInfo.SDElec, ProjectBidInfo.SDPlb, ProjectBidInfo.SDAsb, ProjectBidInfo.CDBldg, 
                      ProjectBidInfo.CDSite, ProjectBidInfo.CDMech, ProjectBidInfo.CDElec, ProjectBidInfo.CDPlb, ProjectBidInfo.CDAsb, ProjectBidInfo.BidBldg, 
                      ProjectBidInfo.BidSite, ProjectBidInfo.BidElec, ProjectBidInfo.BidMech, ProjectBidInfo.BidPlb, ProjectBidInfo.DDSite, ProjectBidInfo.BidAsb, 
                      ProjectBidInfo.DDBldg, ProjectBidInfo.DDMech, ProjectBidInfo.DDElec, ProjectBidInfo.DDPlb, ProjectBidInfo.DDAsb

[\code]

 

[lupins46]

A 'group by' query is never updateable.

 

[sk1hotpepr]

Thanks for writing back, I swore I was able to enter information at one point. But I got rid of the "group by" in my record source. I had to add a subform just to pick up that summed field since I didn't need it to be updatable anyway and it seems to be working now. I don't know if there's a better way than that.

Sherry

 

[ESquared]

I can't conceive of why you'd need to group by so many columns. Use a derived table to get the sum per project contractor:

SELECT
   B.*,
   MyBid
FROM
   ProjectBidInfo B
   LEFT JOIN (
      SELECT ProjectNumber, MyBid = SUM(C.ActBid) FROM ProjectContractor C GROUP BY ProjectNumber
   ) S ON B.ProjectNumber = S.ProjectNumber

If you want less than all columns, then you can list them. the only ones that actually need the B. prefix is ProjectNumber because this also exists in the derived table S (which I chose for Sum).

 

[ESquared]

Oh... a side effect of this is that the recordset will be updatable because the group by is not in the main query. (You might have to set the "unique table" property or whatever it is to ProjectBidInfo.) You won't be able to edit the sum column, though.