Default selection in a listbox

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

re: Default selection in a listbox

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

re: Default selection in a listbox

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.roelandt@zeelandnet.nl> wrote in message news:<3fa2b0e1$0$808$fb624cd1@news1.zeelandnet.nl> ...[color=blue]
> 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[/color]