How to refresh multiple listboxes in a form based on the 1st listbox?

[dpatel1682]

I have a form that has 4 list boxes and I need to refresh them on the fly when user select something in the first listbox. I need to make sure that all the other three boxes change it's value based on the 1st box selection without clicking any submit button.

[slenish]

I have had to do something like this myself. I found this link helpful. They use combo boxes but you could make some adjustments to get it to work with list boxes.

Hope this helps

http://www.databasedev.co.uk/filter_combo_boxes.html

[NeoPa]

Put some code in the AfterUpdate event of the first ListBox that calls a .Requery() for each of the other ListBoxes. Something like :

Private ListBox1_AfterUpdate()
    Call Me.ListBox2.Requery()
    Call Me.ListBox3.Requery()
    Call Me.ListBox4.Requery()
End Sub

[dpatel1682]

Put some code in the AfterUpdate event of the first ListBox that calls a .Requery() for each of the other ListBoxes. Something like :

Private ListBox1_AfterUpdate()
    Call Me.ListBox2.Requery()
    Call Me.ListBox3.Requery()
    Call Me.ListBox4.Requery()
End Sub

All,

Listbox2 needs to be refreshed based on the listbox1's selection. If I do the above it doesn't work. Any suggestions?

[NeoPa]

dpatel1682: Listbox2 needs to be refreshed based on the listbox1's selection. If I do the above it doesn't work. Any suggestions?

Only that you may have used it incorrectly. I see no other reason why it would fail to work.

I don't feel inclined to waste any more effort here though, if all the information you can feed back is that "it doesn't work".

If/when you post a response with some information in to work with I will look again.

[dpatel1682]

Only that you may have used it incorrectly. I see no other reason why it would fail to work.

I don't feel inclined to waste any more effort here though, if all the information you can feed back is that "it doesn't work".

If/when you post a response with some information in to work with I will look again.

Sorry for not providing enough info. Actually, it worked with slenish's suggested site with few modification of the code. Thanks again for your help and time

[NeoPa]

I understand it's not always easy to post everything required. It's always hard to work without it though.

I'm pleased Slenish's post helped you anyway, and thanks for posting you got a result.

[govnah]

Hi there, I am trying to do something similar to what you just achieved. I was hoping you could post the code you used to solve the problem.

Many Thanks

[NeoPa]

Govnah,

We're very happy if you find an answer to your question in an existing thread. If you find that it is not quite right, then this thread is unlikely to be a close enough match, and you should post your question, optionally with a link to this or another thread, in its own thread.

I suspect in this case you could find what you're after by following the link. As I say, if not, then post your own thread.

[dpatel1682]

Govnah,

What i did was I wrote a code in the after update event which sets a record set for the next list box. If you have multiple list boxes which needs to get refreshed based on the first input then you have to write code for the other boxes based on its value. Here's the sample

Private Sub List5_AfterUpdate()

Dim sAccountName As String
Dim sSubAccountType As String

    sSubAccountType = "SELECT distinct Main_Account_Type, Sub_Account_Type " & _
                     "FROM tbl_Account " & _
                     "where Main_Account_Type = " & "'" & Me.List5.Column(1) & "'" & _
                     "and sub_account_type <> ''"
                     
    Me.List7.RowSource = sSubAccountType
    Me.List7.RowSourceType = "TABLE/QUERY"
    Me.List7.Requery

sAccountName = "SELECT Main_Account_Type, Account_Name " & _
                     "FROM tbl_Account " & _
                     "Group by Main_Account_type, Account_Name " & _
                     "having Main_Account_Type = " & "'" & Me.List5.Column(1) & "'" & _
                     "and Account_Name <> ''"
                     
    Me.List9.RowSource = sAccountName
    Me.List9.RowSourceType = "TABLE/QUERY"
    Me.List9.Requery

End Sub

Please let me know if you have any quesitons.

[govnah]

dpatel1682,

Thank you for taking the time out to post the sample.

I noticed you don't have a loop in your code, which will check every column (record) in the listbox. That's what i'm struggling with.

how come you're your using this? "Me.List5.Colum n(1)"

"having Main_Account_Type = " & "'" & Me.List5.Column(1) & "'"

I think thats a whole thread on its own. I'll take NeoPa's advice and open a new thread.

Thank you dpatel1682, NeoPa

[govnah]

Thank You so much guys.

I went back and really had a go at figuring out how the whole listbox thing works. Then i came back to the code you provided 'dpatel1682' and it really did the trick.


Thanks again