VBA refreshing listbox in userform

[devatu]

Hello all,

I have a question regarding the refreshing of a listbox in vba. I cannot manage to find it online or fix it myself.

The picture below shows my userform, and the listbox is connected to an access database via ADO.
test.png

How do I refresh the listbox after I add a new item or delete an item?

Thanks in advance!

Cheers,

Devatu

[Parth007]

This should help you...

http://p2p.wrox.com/access/1688-list...y-refresh.html

[Norie]

How are you populating the listbox?

[devatu]

Thanks for your quick reply!

The given line on that website "me.Counties.Requery " gives an error. I've replaced Counties with the name of my access table, so the line atm is me.Table1.Requery.

Here is my code for the add item to database button:

Please Login or Register  to view this content.

And here is the code for the making of the list:

Please Login or Register  to view this content.

[Kyle123]

Extract the making of the list into a sub like this:

Please Login or Register  to view this content.

Then:

Please Login or Register  to view this content.

And simply call refreshListbox after the insert.

P.S I Fixed a few issues with your code

A few pointers, as a rule don't do Select *, name your fields in the Query, selecting all the columns is slow - naming them makes the query faster and means that you can have the columns in any order you want, so you don't have to loop through the recordset:

Please Login or Register  to view this content.

Can then be replaced with:

Please Login or Register  to view this content.

[devatu]

Thanks for your reply Kyle123, I will look into the meaning of the code in a second. At the moment your code does the trick indeed when I have added something. However, when I've deleted something from my listbox (and thus from my database), it still shows it in the listbox after I call refreshlistbox. Is there a way to do this for the delete function aswell? Thanks in advance!

[Kyle123]

There's no reason it should show when you've deleted something, please post your code for that, including the line where you call refreshListbox

[devatu]

Here it is. The method I use is probably ugly in the eyes of a real VBA expert haha, sorry for that.

Please Login or Register  to view this content.

And thanks for the feedback btw. I really benefit from the feedback of experienced users

[Norie]

Are you calling the RefreshListbox sub after you've run the DELETE query?

[devatu]

Dear Kyle, you are totally right. I just found out that I switched up rs(1) and rs(2). Don't really know how that happend, but because of that there was nothing deleted from the database, hence why there was no change in the listbox. Thanks for helping out!

[devatu]

At the moment, the refreshing function after the delete works for all records, except for the last remaining record. If i try to delete that one, it will remain visible in the listbox, but it is indeed deleted from the access file. Any ideas why?

Please Login or Register  to view this content.

Making of list:

Please Login or Register  to view this content.

[devatu]

Anyone ?

[Kyle123]

There doesn't seem to anything obviously wrong, please post your workbook

[Kyle123]

Please upload it here, many people can't access sharing sites from work

[Kyle123]

I'll have a look now

[devatu]

I don't know how

[devatu]

Thanks Kyle !

[Kyle123]

The code in the workbook isn't the same as you have posted?

[devatu]

Not anymore, I'm sorry for that. It is for my work so I had to proceed. But the deleting still closes the file / gives an error when I delete the last remaining record.

[Kyle123]

The code works fine for me (if I add in the refresh listbox call after deleting) - you also seem to have an empty record in the database - that's why you have a blank row.

What error are you getting?

To attach a workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Kyle123]

If i try to delete that one, it will remain visible in the listbox, but it is indeed deleted from the access file. Any ideas why?

But the deleting still closes the file / gives an error when I delete the last remaining record.

These seem different, which is it? What's actually happening?

[Kyle123]

You should also be deleting by the primary key of the table rather than the answer - deleting by a free text field is just asking for trouble - especially if someone enters the same answer multiple times

[devatu]

Hmm.. I will try to run it again and look what happens. Then I'll get back to you.

[devatu]

Okay so if i try to delete the empty row, it will give a Null error. (Or if I adjust the empty row in the access database manually, the same error happens). Any ideas?

[Kyle123]

Because you need to delete by id, your code should look like this:

Please Login or Register  to view this content.

The Delete routine:

Please Login or Register  to view this content.

Note that now Listbox1.List(selected,0) is the ID of the Record not the part. You'll need to adjust your other code to account for this

[devatu]

Thanks for your reply, I will take a look at this now !