By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Default selection in a listbox

P: n/a
Hello everybody,

I'm a complete rookie in MS Access, so please have mercy with my VBA
code (in case anything is wrong with it ). I have a listbox that gets
its input values from several combo boxes, a small part of the VBA
code is the follwing:

Private Sub combo1_AfterUpdate()
If Me!combo1.Value <> "(All)" Then
strSQL9 = "Like '*" & Me!combo1.Value & "*')"
Call FillList
ElseIf Me!combo1.Value = "(All)" Then
strSQL9 = "Is NULL " & "OR Name1 LIKE '*')"
Call FillList
Else
End If
End Sub

Private Sub combo2_AfterUpdate()
If Me!combo2.Value <> "(All)" Then
strSQL10 = "Like '*" & Me!combo2.Value & "*')"
Call FillList
ElseIf Me!combo2.Value = "(All)" Or Me!combo2.Value = 0 Then
strSQL10 = "Is NULL " & "OR Name2 LIKE '*') "
Call FillList
Else
End If
End Sub

Private Sub FillList()
strSQL = strSQL1 & strSQL2 & strSQL9 & _
strSQL3 & strSQL4 & strSQL10 & strSQL11 & strSQL6 & strSQL5
Me!list1.RowSource = strSQL
Me!list1.Requery
End Sub

My problem is that I would like the list box to show by default all
the entries in the database, but the way I programmed it, I have to
click every combo box once before I get all the records correct. Is
here a way, how I can tell the listbox to look up all the combo boxes
(that show "(All)" by default) when I start the form and after every
change in one of the combo boxes ?

Thanks for your help !!

Hoschi
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi Hoschi,

I guess this should work it's air code but try using it if you want

Private Sub Form_Load()
' Be sure to set *ALL* the strSQL vars before calling FillList
strSQL9 = "Is NULL " & "OR Name1 LIKE '*')"
strSQL10 = "Is NULL " & "OR Name2 LIKE '*') "
End Sub

Private Sub Form_Activate()
Call FillList
End Sub

Leave the rest of the subs as you described them in your message

NP! Fons
Nov 12 '05 #2

P: n/a
Thanks Fons,

worked like a charm.
I observed another very interesting thing, when I tested my form. If
there is an empty column in the list box (meaning column count is one
more than actual columns SELECTed) the listbox starts making funny
things like only showing you part of all records. It took me at least
half a day to figure out whats wrong, because the statement in
"RowSource" was 100% correct.

Hoschi
"Fons Roelandt" <f.********@zeelandnet.nl> wrote in message news:<3f*********************@news1.zeelandnet.nl> ...
Hi Hoschi,

I guess this should work it's air code but try using it if you want

Private Sub Form_Load()
' Be sure to set *ALL* the strSQL vars before calling FillList
strSQL9 = "Is NULL " & "OR Name1 LIKE '*')"
strSQL10 = "Is NULL " & "OR Name2 LIKE '*') "
End Sub

Private Sub Form_Activate()
Call FillList
End Sub

Leave the rest of the subs as you described them in your message

NP! Fons

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.