two ComboBox questions

[terry w]

hello - I have some code attached to several of my ComboBoxes that works
fine, but I'm not if it is 'best practice' coding. I'd really like any
suggestions for improvement:

a) If I have to walk through the items in a ComboBox, i do this...

n = 0
Do While Not IsNull(Me!cboGroup.ItemData(n))
... code here ....
n = n + 1
Loop

Is this the best way to walk through these items? Is there a better way
involving 'Do While Not rs.EOF'?

b) Say I have three ComboBoxes and I only want to proceed
if they all show values...

If Len(cboA) * Len(cboB) * Len(cboC) > 0 Then .....

Again, is this the accepted way to do this sort of thing? I want to make
sure i don't run into any unexpected outcomes.

regards, Terrance

 

[DevlinM]

The question boils down to this: What is your objective? Or... Why are you
interested in "walking" through a combo box? Once we know your objective, we
can better assist you with an answer for "best practice."

 

[terry w]

hello Devlin - with respect, I don't think that is the question. I'm just
working through various scenarios to learn better coding. One of the things
I would like to know how to do is to examine each item in a combo box. For
example, there may be reports I want to print, one for each item in a
particular combo box. - Terrance

 

[DevlinM]

Answering your second question. Your test is fine. Just be sure that if
using the function Len, each of your controls returns a string.

 

[DevlinM]

OK, well there are a number of ways to determine the contents of a combo or
list box. ItemData is used to return the bound column of either control.
Looking at your scenario here is what you'd probably want to do.

Dim i as integer

For i = 0 to Me.Cbo_A.ListCount - 1
Debug.Print Me.Cbo_A.ItemData(i)
Next i

 

[DevlinM]

Iterating through the combo itself is just one way of looking at the contents
of the control. You can also look at the control's columns. For instance

Dim i as integer

For i = 0 to Me.Cbo_A.ListCount - 1
Debug.print Me.Cbo_A.Column(0) & " " & Me.Cbo_A.Column(1)
Next i

You can also look at the controls row source. You are most likely going to
be using a query, therefore, you can open the query to look at items. If you
are concerned about nulls in your list, you might do something with them
before you reach the point of displaying them in your control. For instance,
you might set criteria in query to WHERE Table_Name.Attribute_Name Is Not
Null. This will limit your list non-null values. Or you might use a query
or other method to elimate nulls from your recordset.

Once again, knowing your objective is an important as to how to answer your
question. If you just want to know how to iterate through a combo box, then
your answer is here.

Cheers!

 

[terry w]

hey Devlin - thanks for you help! - Terrance

Answering your second question. Your test is fine. Just be sure that if
using the function Len, each of your controls returns a string.

 

[Marshall Barton]

hello - I have some code attached to several of my ComboBoxes that works
fine, but I'm not if it is 'best practice' coding. I'd really like any
suggestions for improvement:

a) If I have to walk through the items in a ComboBox, i do this...

n = 0
Do While Not IsNull(Me!cboGroup.ItemData(n))
... code here ....
n = n + 1
Loop

Is this the best way to walk through these items? Is there a better way
involving 'Do While Not rs.EOF'?

b) Say I have three ComboBoxes and I only want to proceed
if they all show values...

If Len(cboA) * Len(cboB) * Len(cboC) > 0 Then .....

Again, is this the accepted way to do this sort of thing? I want to make
sure i don't run into any unexpected outcomes.

Another way for a) in A2002+

With Me.cboGroup.Recordset
Do Until .EOF
If Not IsNull(!somefield) Then
...
End If
.MoveNext
Loop
End With

b) Won't work if any of those field's contain Null. In
general, it is unusual to have a valid reason to set a
field's AllowZeroLength property to Yes so the fields should
contain Null instead of a ZLS where your code would be
acceptable.

Depending on how obscure you want to make your code, you
could use:
If Len((cboA + cboB + cboC) & vbNullString) > 0 Then

But we're talking about best practices where clarity
sometimes wins out over brevity. I would at least think
about using:
If Len(Nz(cboA,"")) > 0 _
And Len(Nz(cboB,"")) > 0 _
And Len(Nz(cboC,"")) > 0 Then