Removing individual records from form after query is run 1

[breezett93]

The concept that I'm developing is a late orders form. After the user enters a date range, a list of orders will populate the form. There are some external factors that could cause an order to be late but not at the fault of the company. So internally, the order was completed on time.

I need to implement an override to remove an order from the form only (not the table). My initial searches pretty much turned up the opposite; so I couldn't find out how to remove a record from the form.

I was thinking of having a checkbox next to each record that fits the search criteria. When the checkbox is checked for that row, the record is removed and the count is reduced by one. When I tried implementing this, if I checked one box, they all became checked.

If there is a better solution, I am all ears.

 

[misscrf]

You would need to add a column to the underlying table, as an integer or bit, and have the checkbox control bound to that field. That will allow the checkbox to exist at a record level. If it is unbound, it is just a control on a form, and won't be tied to a record.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[breezett93]

I am using a query that pulls data from two different tables. Is there a different solution since I'm using a query instead of a table? Or do I need to have the query store the information into a new table that gets wiped after every use?

 

[Andrzejek]

There are some external factors that could cause an order to be late but not at the fault of the company. So internally, the order was completed on time.

I need to implement an override to remove an order from the form only (not the table).

By looking at your table(s) in your data base, how can you determine which records to by-pass - or include - because of the "external factors"? Is there a field to indicate it?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

By looking at your table(s) in your data base, how can you determine which records to by-pass - or include - because of the "external factors"? Is there a field to indicate it?

There is no field to indicate that information. Pretty much the boss just knows which orders were late and which ones weren't. The tables have a promise ship date and an actual ship date. If the actual > promise, then I have the order flagged as late.

So now for reporting, the boss wants to see all orders in a date range, filter out on-time orders, remove the late orders that weren't internally late, and see the final list.

I've got the first and second parts working perfectly. I just can't figure out how to remove a record from a form without deleting it also from the tables.

 

[dhookom]

Add a field to the table with the promised and actual ship dates that identifies the status. Have your boss update the field. This is data and should be stored in your table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

The promised date is in one table, and the actual date is in the second. Should I add the field to the actual?

 

[dhookom]

I expect I would add the field to the orders table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

the boss just knows which orders were late and which ones weren't." but you - and your application - do not know that piece of information. Take it out of his had and into a table.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

Sorry for the long delay.

I have added a column to the table called Late Override (Yes/No) type. On the form, I have added a check box; so that the order can be flagged as not late.

When I tried testing this, I got a message saying "This Recordset is not updateable." Researching this message revealed that a query that pulls from more than one table is not editable.

The override checkbox is the only part of this form that needs to be editable. When I tried removing the override from the query, the checkboxes were grey and not check-able.

What other options are available so that the user can check records pulled from the query. The goal of checkbox is to just have the record not visible when the "Show Late Orders" button is clicked. It doesn't actually do anything to the record itself.

 

[dhookom]

You need to provide some information about your significant tables and fields. It is possible to have a query based on more than one table that allows editing.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Certainly.

The query is pulling from the Orders table and Invoice table which are related via the OrderID. It specifically is calling InvoiceID, OrderID, CustomerRequestDate, PromiseDate, ActualDate, Notes, and Late Override.

 

[dhookom]

Is the OrderID the primary key in the Orders table?

What is the SQL view of the query that didn't allow updating?

If the LateOveride is related to the order rather than the invoice, I expect the field should be in the orders table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Yes, OrderID is the PK in the Orders table. InvoiceID is the PK in the Invoice table.

SQL for the query:

SELECT IvcTbl.IvcID AS Invoice, IvcTbl.OrdId AS [Order], OrdTbl.CustReqShipDate AS [Customer's Request Date], OrdTbl.ShpDt AS [Promised Date], IvcTbl.IvcDt AS [Actual Date], IvcTbl.Notes, IvcTbl.[Late Override]
FROM IvcTbl INNER JOIN OrdTbl ON IvcTbl.[OrdId] = OrdTbl.[OrdId]
GROUP BY IvcTbl.IvcID, IvcTbl.OrdId, OrdTbl.CustReqShipDate, OrdTbl.ShpDt, IvcTbl.IvcDt, IvcTbl.Notes, IvcTbl.[Late Override], IvcTbl.IvcID, Len([OrdTbl.OrdId])
HAVING (((Len([OrdTbl.OrdId]))>=5))
ORDER BY IvcTbl.IvcID DESC;

I agree with you that LateOverride seems like it should be in the Orders table. However, the invoice is what creates the packing slip for shipping, and they gauge an order being late by when it leaves the dock.

 

[dhookom]

A group by query is never updateable.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Thank you for that info.

I changed each Group By to an Expression, and swapped out Having for Where on the OrderID length.

Now the form is updateable.

Thank you. Now I can work on having checked Orders removed from the list of late orders.

 

[Andrzejek]

breezett93,
The customary way of showing appreciation for help received is to click on [blue]Great Post![/blue] link in the helpful post. You should do it to Duane's post if you found it helpful.
That action awards a star to the TT member, but also let others know which post(s) was helpful.

Have fun.

---- Andy

There is a great need for a sarcasm font.