Can I somehow edit a table through a form which uses subqueries?

[RSW]

Short version of question:

My research has shown that Access will not allow edits through a form that 1) has subqueries in its SELECT clause, or 2) uses aggregation such as First(). Is there any tricky way around this?

Long version of question:

I have a database form that shows an overview of orders for products (it's based on a query that pulls all current orders from the big table). I would like to handle both of these on each row:

• Show where we built it last time, and
• Allow the user to select where it will be built this time.

The problem is that I cannot figure a way to change the form/query such that it doesn't aggregate to find out where we built it last time, or doesn't rely on subqueries that do just that.

But I feel like there should be some way to do it--since each row displayed does rely on one and only one record in the complete table of orders. Which obviously is the record I want to update.

Is there something I can do that will accomplish this? I really do not want to have to make the user open up another form to see last time or choose this time.

Thanks in advance!!

 

[RSW]

Also--apologies in advance if this is not the correct forum--but not knowing the solution, I don't know which part of Access is most involved!

 

[boblarson]

A query has to be "updateable" for you to be able to edit it. Given that is not possible with what you are trying to do, maybe if you want to keep what you have, you can create a separate form for editing the record and then use the WHERE clause in the DoCmd.OpenForm code to specify the record that you are currently on, so then you only have the edit form have the fields necessary to keep it updateable.

 

[RSW]

A query has to be "updateable" for you to be able to edit it. Given that is not possible with what you are trying to do, maybe if you want to keep what you have, you can create a separate form for editing the record and then use the WHERE clause in the DoCmd.OpenForm code to specify the record that you are currently on, so then you only have the edit form have the fields necessary to keep it updateable.

Thanks...I really hate to add another form, but I'll do it if it's necessary.