Deleting a Row from a list Box populated from a query

[tonygg]

hi.



I have 2 list boxes. One is populated from a value list the other from a DB Query.

I select a row in each list box and use the data from that row to create a new product in the DB.

After creating the product I want to be able to remove the selected row from each list box (as it is finished with).

I can easily do it for the listbox with row source of value list. I simple use : Me.FileList.RemoveItem (Me.FileList.ListIndex). This remove the currently selected item.

I dont seem to be able to do it for the listbox with row source of Query/Table. This list box is populated from a query. I cant delete the underlying data from the DB.

Please could someone advise me on the simpliest way to remove the row from this listbox?

Many thanks

Tony

[alansidman]

I cant delete the underlying data from the DB.

Clarify this: Can't because you don't know how? Can't because it will disrupt other data in the record?

When I use a query to populate a combobox or listbox, I usually make a separate table of the values I want. When I need to add or delete values, I bring up the table values in a form and delete and add as necessary.

[John_G]

Please could someone advise me on the simpliest way to remove the row from this listbox?

Did you try requerying the list box - me.ListBoxName.requery? If you did, and it didn't remove the row, then either the listbox query does not have the correct WHERE clause in it (to filter out items you don't want) or the table the listbox is querying is not being updated correctly. Is there a field in the table which you can use to determine which values to select for the listbox?

What is the SQL for the listbox query? That is the best place to start looking.

I cant delete the underlying data from the DB.

This could mean two things - either you tried to delete it and it didn't work, or deleting the data would remove information you need to keep (my guess is the latter).

[tonygg]

Clarify this: Can't because you don't know how? Can't because it will disrupt other data in the record?

When I use a query to populate a combobox or listbox, I usually make a separate table of the values I want. When I need to add or delete values, I bring up the table values in a form and delete and add as necessary.

Thank you. To clarify the re query is at best very difficult as I am using data from a table maintained in a third party application. I am using it as a TO DO list for checking values in another table. I cant delete records from the source table or add columns to the source table.

It also seems to me to be significant hassle to create extra fields or temporary tables just to know which rows I have looked at already. Particular when you compare it to a listbox created from a value list that requires a single command to remove a row.

I was wondering if I could put the values into a recordset and then use that to populate the listbox with values so I could use Me.FileList.RemoveItem (Me.FileList.ListIndex). Do you think this could work?

Many thanks Tony

[tonygg]

Did you try requerying the list box - me.ListBoxName.requery? If you did, and it didn't remove the row, then either the listbox query does not have the correct WHERE clause in it (to filter out items you don't want) or the table the listbox is querying is not being updated correctly. Is there a field in the table which you can use to determine which values to select for the listbox?

What is the SQL for the listbox query? That is the best place to start looking.



This could mean two things - either you tried to delete it and it didn't work, or deleting the data would remove information you need to keep (my guess is the latter).

Thank you. To clarify the re query is at best very difficult as I am using data from a table maintained in a third party application. I am using it as a TO DO list for checking values in another table. I cant delete records from the source table or add columns to the source table.

It also seems to me to be significant hassle to create extra fields or temporary tables just to know which rows I have looked at already. Particular when you compare it to a listbox created from a value list that requires a single command to remove a row.

I was wondering if I could put the values into a recordset and then use that to populate the listbox with values so I could use Me.FileList.RemoveItem (Me.FileList.ListIndex). Do you think this could work?

Many thanks Tony

[Micron]

Following this as best as I can, it seems that the list items are based on a query which works. You select one, do something, now you don't want that one to appear. I agree with John_G - your query should filter out the ones that you don't want. There's no need to do anything to the source records that you say can't be modified. There must be something you can grab from a record in some table when you just did something that makes the list item no longer wanted. Perhaps your row source is missing that table. I agree, more info around those tables and row source sql is required.

There are hacks you could do, maybe like your suggestion, but they seem quite unwarranted at this point.

[tonygg]

Following this as best as I can, it seems that the list items are based on a query which works. You select one, do something, now you don't want that one to appear. I agree with John_G - your query should filter out the ones that you don't want. There's no need to do anything to the source records that you say can't be modified. There must be something you can grab from a record in some table when you just did something that makes the list item no longer wanted. Perhaps your row source is missing that table. I agree, more info around those tables and row source sql is required.

There are hacks you could do, maybe like your suggestion, but they seem quite unwarranted at this point.

Thank you everyone who contributed thus far. I now recognise I should have been fuller in my explanation. I really had just assumed that there was a simple way to remove items from a list box even if its source was a query. It is possible (but complex) to change the query to remove the items. I will explain the background (sorry I should have done this originally).

I have a product table (created in a third party application). I want to add new products to it. The details for the new products are a series of images (3 per product) that are in a file directory. The names of the files can be used to name the new product and the image paths (3) need to be included in the product details.

A problem I have with simply creating a new product in access is there is a spiders web of associated tables and values (all undocumented) that are created/updated when you create a new product from within the 3rd party application. To create a new product with all associated data from within the application is just a copy and paste (very quick). The problem is the resulting new product does not have the correct name(s) and the correct images. This is very fiddly to do from within the 3rd party application.

So to solve the problem I am creating the dummy new products in the application (copy and paste). Once done I am going into access. In access I will have 2 lists to work on (in 2 separate list boxes) . The first is the list of dummy new products needing updating. The second is the list of image files that have been found in a directory on the c drive.

I wanted to manually match the product to the image by selecting a row of each listbox and then update the Dummy product details with those from the selected image. This i have managed to do. I then wanted to remove the product and the image from the list boxes so I dont try and process it again and the 2 lists become smaller.

Now coming back to the problem with updating the query is that I want to exclude items that have the image details added. The image details (currently) only exist in the file directory and in one of my listboxs.

If my only choice is to enhance my query for selecting products then I think i will need a temp table to hold all the image files that have been processed and use it to exclude products that contain matching values. I cant just create a temp table with the full list of files from the directory as I would end up with the same problem about removing processed image files as I have with processed products.

I hope my explanation is ok?

I guess the answer to my initial question is if I have a listbox with row source a query I can only remove items from within the query (improving the selection criteria)?

I think i will need to either find a away to improve the selection on that query or load up the image files into temp tables. I guess I will need 2. One to hold unprocessed (removing items as I process them) and another holding processed (so I can use to exclude products that have already had images added)?

It is such a shame I need to do all this just to remove an item from a listbox.

Many thanks again

Tony

[orange]

It might be helpful if you could show us some sample data showing
--what you have, and
-what you really want as result.

[Micron]

I agree. To suggest anything more at this point is just guessing on our part, because no, I (at least me) still cannot follow.

I really had just assumed that there was a simple way to remove items from a list box even if its source was a query.

The answer was and still is, yes. How is usually that the query has to relate enough data in order to make an exclusion. If you need a join between some "dummy" table and the application data, then you have to create it.

When you're showing what you have, include samples from the source application as well as your db, and provide the sql for the listbox.

[tonygg]

Thanks to all posters. I have created a join to the temp table and have it working now. Thanks again. Tony