Clear Listbox Entries

[Asif]

I have a form (2-a SKIPPROCESS) which has three listboxes; CABLES,
COPPER and GENERAL. These three listboxes are based on a query that
extract data (date and quantity) from a table TBL_LIVE.

Under each listbox is a button "EMPTY", upon clicking on the button a
form (FRM_EMPTY) pops up asking the user are you sure you want to
empty the bin and the total quantity of that bin. When I click on YES
the entry highlighted in the listbox is deleted but I want the whole
list box to be deleted i.e. if the user clicks on EMPTY button under
the CABLES listbox then entries in that listbox are deleted likewise
for the other listboxes.

I'm running a delete query which contains fields from TBL_LIVE (LVID,
LVQUANTITY) the criteria I've specified for LVID is "[Forms]![2-a
SKIPPROCESS]![CABLES]"

As I'm not getting the results I'm after I was hoping someone would be
able to help.

Thanks

 

[Guest]

For each DELETE query you should use exactly the same criteria as you do for
the query which fills the corresponding list box. The criteria determine
which rows are listed in the list box, so they will also determine which ones
are deleted from the table, consequently deleting all rows currently listed
in the list box. You'll need to Requery the list box after executing the
DELETE query so that it matches the data in the underlying table and shows no
items.

At present it sounds like you are referencing the list box (CABLES) on the
form as the criterion. As the value of the list box is the currently
selected row it will therefore only delete the row in the table with the LVID
value which matches the item currently selected in the list box.

Ken Sheridan
Stafford, England

 

[Asif]

The original query that fills in the list boxs obtains data from 3
different tables and when I apply the same criteria in the delete
query it gives me an error message saying "Specify the table
containing the records you want to delete"

Thanks

 

[i_takeuti]

I have a form (2-a SKIPPROCESS) which has three listboxes; CABLES,
COPPER and GENERAL. These three listboxes are based on a query that
extract data (date and quantity) from a table TBL_LIVE.

Under each listbox is a button "EMPTY", upon clicking on the button a
form (FRM_EMPTY) pops up asking the user are you sure you want to
empty the bin and the total quantity of that bin. When I click on YES
the entry highlighted in the listbox is deleted but I want the whole
list box to be deleted i.e. if the user clicks on EMPTY button under
the CABLES listbox then entries in that listbox are deleted likewise
for the other listboxes.

I'm running a delete query which contains fields from TBL_LIVE (LVID,
LVQUANTITY) the criteria I've specified for LVID is "[Forms]![2-a
SKIPPROCESS]![CABLES]"

As I'm not getting the results I'm after I was hoping someone would be
able to help.

Thanks

 

[Guest]

In your original post you said "I'm running a delete query which contains
fields from TBL_LIVE". Does that mean you want to delete the rows from that
table only, and not the other two which contribute to the list box? If so
you'll need to identify the criteria which would identify the same rows from
this table as are represented by the items in the list box. This might mean
using a subquery in the DELETE query's WHERE clause.

If you post the SQL for one of the list boxes' queries here I might be able
to give you the SQL for a corresponding DELETE query.

The DELETE queries for the other list boxes will probably be similar in
principle, and hopefully once you've determined the correct query for one
you'll be able to do so for the other two.

Ken Sheridan
Stafford, England

 

[Asif]

The SQL Query for the CABLES listbox is as follows;

SELECT tbl_BINPROCESS.BINPROCESSID, tbl_BINPROCESS.BINPROCESSDate,
tbl_BINPROCESS.BINPROCESSQuantity, tbl_SKIP.SKIPDescription,
tbl_BIN.BINID
FROM tbl_SKIP INNER JOIN (tbl_BIN INNER JOIN (tbl_BINPROCESS INNER
JOIN tbl_LIVE ON tbl_BINPROCESS.BINPROCESSID = tbl_LIVE.LVID) ON
tbl_BIN.BINID = tbl_BINPROCESS.BINPROCESSBinID) ON tbl_SKIP.SKIPID =
tbl_BIN.BINSkipNumber
WHERE (((tbl_SKIP.SKIPDescription)="Cable Ends Skip"));

Thanks

 

[Guest]

You haven't confirmed which table(s) you want to delete the rows from, so
I'll assume that its tbl_LIVE as your first post suggested. If not, the
following will hopefully give you an idea of how to do it for another table.

DELETE *
FROM tbl_LIVE
WHERE EXISTS
(SELECT *
FROM tbl_SKIP, tbl_BIN, tbl_BINPROCESS
WHERE tbl_BIN.BinID = tbl_BINPROCESS.BINPROCESSBinID
AND tbl_SKIP.SKIPID = tbl_BIN.BINSkipNumber
AND tbl_SKIP.SKIPDescription = "Cable Ends Skip"
AND tbl_BINPROCESS.BINPROCESSID = tbl_LIVE.LVID);

The way it works is that the subquery returns rows where SKIPDescription =
"Cable Ends Skip" and BINPROCESSID = the LVID of the outer query's current
row. By including the subquery in the outer query's WHERE clause and using
the EXISTS predicate only those rows from tbl_LIVE will be deleted where the
subquery returns one or more rows.

I'm not clear as to whether this is what you want to happen or not, but its
what your original post seems to suggest. As always when testing such set
operations be sure to back up the tables first. After you've executed the
above query requery the list box in your code:

[Forms]![2-a SKIPPROCESS]![CABLES].Requery

Ken Sheridan
Stafford, England