Select Multiple records based on a selection in a form

[MunroeM]

Hello I am wondering what code would I need to use to have all the records that are under a certain field have their U/S check boxes checked "yes".



Or even point me in the right direction as im comming up lost when searching for what i want to do.

I've included a Pic with how my tables work and the relationships also.

What I am trying to do is that when the user selects a tool board to delete. It will Check the U/S box to "YES" on all the Temmis items that fall under that tool board. I already have queries and a macro that copy the U/S checked Items from the "TemmisItemT" table to the "DeleteT" table and then it deletes the same records it just copied from the "temmisItemT" table.

So if the User Selects AG-008 and clicks a button i want it to Select the 2 Temmis Items that are under it and add a check to the "U/S" field of those 2 items.

Thanks

Munroe

[ItsMe]

As a simple explanation, create an update query and be sure to use a WHERE statement. Copy one of your tables and use the copy as a way to test the concept. Go to the Query Designer and use one of the Action tools towards the left of the Ribbon to 'Update'. Add criteria to the grid at the bottom of the designer and to the appropriate field(s) so you do not update everything. Look at the resulting SQL via SQL view.

In the end, you will probably want to use a select query as a subquery to an INSERT INTO. So, the SELECT query that your form is based on, with the addition of some where criteria, would be the subquery to a SELECT INTO. Your UPDATE that you practice with in the query designer will be revised. The UPDATE will be replaced with INSERT INTO. While SET and or VALUES will be replaced with the SELECT query.
http://www.w3schools.com/sql/sql_insert_into_select.asp

[MunroeM]

I did not even notice the update query when I created my append and delete queries.

In the end I ended up making 3 queries. first one set all the temmis items of the selected board to "U/S", second one took the U/S temmis items from the selected board and added them the the DeleteTemmisT table along with changing some of the fields in the record to display who deleted them and when. Thired one deleted the selected Board/Kit and since it was a cascade delete related record the temmis items with it were also deleted.

Again Thanks for the Help

[ItsMe]

Glad you were able to work through it. Even if you do not edit SQL directly from SQL view, it is a good idea to look at the SQL while you work with the Query Designer.