query ComboBox row's contents

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Phlip - 04 Aug 2005 16:27 GMT

Newsgroupies:

Suppose I want to write a function like this:

Private Function findInComboBox(ByVal cmb As ComboBox, sought) As Boolean

End Function

It should return True if a ComboBox's list displays some sample string. I
have no idea what to write inside it. LB_FINDSTRING would be nice, and I'm
well aware of the distance between an Access ComboBox and a raw SDK ListBox.
So what am I left with?

Signature

Phlip
http://www.greencheese.org/ZeekLand <-- NOT a blog!!!

Reply to this Message

Klatuu - 04 Aug 2005 19:21 GMT

I don't understand your reference to LB_FINDSTRING is, but I think this is
what you are looking for.

Private Function findInComboBox(ByVal cmb As ComboBox, sought) As Boolean
findInComboBox = IIf Instr(cmb.RowSource, sought) = 0, False, True)
End Function

> Newsgroupies:
>
[quoted text clipped - 8 lines]
> well aware of the distance between an Access ComboBox and a raw SDK ListBox.
> So what am I left with?

Reply to this Message

Phlip - 04 Aug 2005 21:05 GMT

>I don't understand your reference to LB_FINDSTRING is,

MS publishes at least two, completely different, "combo box" controls. The
<winuser.h> version has a "find string" method which that message ID
triggers. I am aware that the Access.ComboBox is completely different. It
shares the same general method set, so it's totally annoying we're missing
that one.

However, I cannot even ask a simpler question. I cannot ask the ComboBox
"what is the text you display in your 3rd row?" These gaps in the Access API
are astounding.

> but I think this is
> what you are looking for.
>
> Private Function findInComboBox(ByVal cmb As ComboBox, sought) As Boolean
> findInComboBox = IIf Instr(cmb.RowSource, sought) = 0, False, True)
> End Function

What if the ComboBox were bound to a Select statement?

That's a rhetorical question. A full featured "find string" method must
detect which of several data sources the ComboBox uses, and then reproduce
their circumstances to simulate querying the row contents.

My version:

Private Function findInComboBox(ByVal cmb As ComboBox, ByVal sought As
String) As Boolean

findInComboBox = False
Dim rs As DAO.Recordset
Set rs = cmb.Recordset
Set rs = rs.Clone
rs.MoveFirst

While Not rs.EOF
If sought = rs.Fields(1).Value Then
findInComboBox = True
Exit Function
End If

rs.MoveNext
Wend

End Function

I know I only use ComboBoxes bound to Select statements there. So, is my
analysis of the API correct? Or is there something simple we have
overlooked?

Signature

Phlip
http://www.greencheese.org/ZeekLand <-- NOT a blog!!!

Reply to this Message

Klatuu - 04 Aug 2005 21:17 GMT

Now I am confused. I thought you wanted to find a string in the row source
for the combo box. If your combobox uses fields as it's row source, your
method will work. I don't understand what you intend to do with this.

> >I don't understand your reference to LB_FINDSTRING is,
>
[quoted text clipped - 46 lines]
> analysis of the API correct? Or is there something simple we have
> overlooked?

Reply to this Message

Phlip - 04 Aug 2005 22:09 GMT

> Now I am confused. I thought you wanted to find a string in the row
> source
> for the combo box.

I didn't say "RowSource". Some ComboBoxes don't store values in it.

Suppose I gave you a reference to a ComboBox, and told you to fetch the text
in its 3rd row, but you didn't write the ComboBox and don't know what type
it is.

> If your combobox uses fields as it's row source, your
> method will work. I don't understand what you intend to do with this.

This is a theoretic exercise regarding the quality of the ComboBox's API.

In theory, any text the user sees on the screen, program code should be able
to fetch.

We may have overlooked a method that gets _any_ ComboBox's row text. Without
one, the only way to write the function I propose is detect the ComboBox
RowSourceType, then reconstruct its inputs. Sometimes the function browses
its Recordset, sometimes it parses the RowSource, etc.

This sucks.

Signature

Phlip
http://www.greencheese.org/ZeekLand <-- NOT a blog!!!

Reply to this Message

Marshall Barton - 04 Aug 2005 22:01 GMT

>Suppose I want to write a function like this:
>
[quoted text clipped - 6 lines]
>well aware of the distance between an Access ComboBox and a raw SDK ListBox.
>So what am I left with?

Since Access combo/list boxes are data centric, they have
many ways to specify the row source data, which makes a
simple function find function a bit of an oxymoron.

A way to do what you want (even though I can't imagine why
you want to) would be to loop through the row source's data:

For k = 0 To combo.ListCount - 1
    If "somestring" = combo.Column(x, k) Then
            ' found it
    End If
Next k

As far as I know there is almost no need to do this because
the user's data can raise the NotInList event or, if you
don't want to use that, you can use another event to check
if the ListIndex property is -1. Both of those will
indicate that the user's data is not in the list.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Phlip - 05 Aug 2005 00:10 GMT

> If "somestring" = combo.Column(x, k) Then

Ouch. That was it. Thanks. Ouch.

> As far as I know there is almost no need to do this

I'm writing unit tests for each feature. Unit tests must access state that
Access would prefer to channel between the user and database without the
intermediation of code.

And, in turn, that makes my kind of questions very difficult to ask here,
because they are always the opposite of what everyone else does.

Signature

Phlip
http://www.greencheese.org/ZeekLand <-- NOT a blog!!!

Reply to this Message

Marshall Barton - 05 Aug 2005 00:45 GMT

>> If "somestring" = combo.Column(x, k) Then
>
[quoted text clipped - 8 lines]
>And, in turn, that makes my kind of questions very difficult to ask here,
>because they are always the opposite of what everyone else does.

You are either perverse and deserve what you've got, or you
have my sympathies ;-)

Regardless of your motives, I do wish you luck in these, by
my own experiences, thankless endeavors.

Signature

Marsh
MVP [MS Access]

Reply to this Message