INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Removing individual records from form after query is run

Removing individual records from form after query is run

(OP)
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.

RE: Removing individual records from form after query is run

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

RE: Removing individual records from form after query is run

(OP)
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?

RE: Removing individual records from form after query is run

Quote (breezett93)

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.

RE: Removing individual records from form after query is run

(OP)

Quote:


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.

RE: Removing individual records from form after query is run

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

RE: Removing individual records from form after query is run

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

RE: Removing individual records from form after query is run

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

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

RE: Removing individual records from form after query is run

"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. smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Removing individual records from form after query is run

(OP)
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.

RE: Removing individual records from form after query is run

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

RE: Removing individual records from form after query is run

(OP)
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.

RE: Removing individual records from form after query is run

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

RE: Removing individual records from form after query is run

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

SQL for the query:

CODE

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.

RE: Removing individual records from form after query is run

A group by query is never updateable.

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

RE: Removing individual records from form after query is run

(OP)
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.

RE: Removing individual records from form after query is run

breezett93,
The customary way of showing appreciation for help received is to click on Great Post! 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close