Can't update a field via a query

[Gkirkup]

I used to have a query from a single table, used as the source for a form. On the form is a yes/no control. Clicking that would update the table yes/no field.
Now the data structure has changed to two tables. I have a sub-query which passes the results from the new table to my query. But the yes/no update no longer works, when you click the control in the form. That part of the query, form and table are unchanged. Has the recordset become non-updatable because I added a sub query? How do I get around that?

Robert

 

[JoanneJames]

Could you provide some code and the query? What query is bound to the form?

 

[Gkirkup]

JJ: Thanks. Here is the original query which drove the form. RCVDOK was updatable on the form:

SELECT Sales.[Sale Nmbr], Sales.Date, Sales.Customer, Sales.Packed_by, Sales.Sales_Person, Customers.S_NAME, [Qty Sold]+[Qty Sold (i)]+[Qty Sold (ii)]+[Qty Sold (iii)]+[Qty Sold (iv)] AS UQTY, ([Qty Sold]*[Price ea])+([Qty Sold (i)]*[Price Ea (i)])+([Qty Sold (ii)]*[Price Ea (ii)])+([Qty Sold (iii)]*[Price Ea (iii)])+([Qty Sold (iv)]*[Price Ea (iv)]) AS UTOT, Sales.PRINTOK, Sales.RCVDOK
FROM Sales LEFT JOIN Customers ON Sales.Customer = Customers.S_CODE
WHERE (((Sales.Date)=Date()))
ORDER BY Sales.[Sale Nmbr];

Now, new table structure, header and detail table. Now can't update RCVDOK on the form. There is a new subform 'Sales Today 0':

SELECT SALHED.SALNUM, SALHED.PACKBY, SALHED.SALESPX, SALHED.PRINTOK, SALHED.RCVDOK, SALHED.CUSCODE, Customers.S_NAME, SALHED.SALDATE, [Sales Today 0].SumOfQTYSOLD AS UQTY, [Sales Today 0].Expr1 AS UTOT, SALHED.PACKBY
FROM (SALHED INNER JOIN Customers ON SALHED.CUSCODE = Customers.S_CODE) INNER JOIN [Sales Today 0] ON SALHED.SALNUM = [Sales Today 0].SALNUM
WHERE (((SALHED.SALDATE)=Date()));

I appreciate your assistance.
Robert

 

[boblarson]

How do I get around that?

Don't include the details with the main form part. Use a subform with its own query.

 

[Gkirkup]

Bob: I may not have mentioned that the form is continuous. I added the subform, and got the error message 'You can't view a form as a continuous form if it contains a subform'.

Robert

 

[JoanneJames]

So you originally had 2 tables:[Customer] and [Sales] and now you have a 2 more tables: [Salhed] and [Sales Today 0]? Confusing, since you said Sales Today 0 was a subform. Unclear how your screen(s) are laid out.

 

[Gkirkup]

JJ: I have fixed this by using an additional screen driven by a single query. This is now updateable.

Robert