selecting list box entry using a combo box

[dgardner]

I have a combo box that lists street numbers. I also have a list box
that lists street numbers and addresses (separately). I want to click
on one of the street numbers and have the first item in the list box
with that street number selected.

Any suggestions will be appreciated.

Deborah

[AccessVandal via AccessMonster.com]

Hi Deborah,

Not going to ask you “why use a listbox?�.

But here goes…

Use this air code in you combo box afterupdate event. Assuming both control
in the same form.
Me.ListBoxName.RowSource = “SELECT StreetNumber, Addresses,…..FROM Table1
Where StreetNumber = “ & Me.ComboxName

I’m assuming your StreetNumber Datatype is “Number� as in your table
properties. If they are “Text�, use quotes like… StreetNumber = ‘� & Me.
ComboName & “’�

>dg******@twcny.rr.com wrote:
I have a combo box that lists street numbers. I also have a list box
that lists street numbers and addresses (separately). I want to click
on one of the street numbers and have the first item in the list box
with that street number selected.

Any suggestions will be appreciated.

Deborah

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200707/1

[AccessVandal via AccessMonster.com]

Hi Deborah,

>I want to keep the listbox populated with everyone on the street. I
want the street number combo box to go to the first instance of that
number.
You see, I may have multiple people I want to click on. (This
populates another table.) And by going to the first instance of the
street number, I will not have to scroll through sometimes 100s of
records. I do believe that your solution will repopulate the list box.

Did the code work for you in the cascading combo box? The cascading combo
boxes can do the job of filtering records by the “Street Number� in that list
box.

>I have been trying to use the ItemData property but have not been
successful with the examples I have found on the Internet.

I don’t think it’s practical to use VBA just to filter records on a list box.
Although it can be done by code, it’s basically the same thing using “Do
Loop� or “For Next Loop�, unless…… you say “ I don’t want to use combo boxes.
�

Show us your code, so that others might be able to help, if they have better
solutions. Remember, nobody can see your database/design except yourself.

>dg******@NOSPAM.twcny.rr.com wrote:

--
Please Rate the posting if helps you

Message posted via http://www.accessmonster.com

[dgardner]

On Jul 23, 9:16 pm, "AccessVandal via AccessMonster.com" <u18947@uwe>
wrote:

Hi Deborah,

I want to keep the listbox populated with everyone on the street. I
want the street number combo box to go to the first instance of that
number.
You see, I may have multiple people I want to click on. (This
populates another table.) And by going to the first instance of the
street number, I will not have to scroll through sometimes 100s of
records. I do believe that your solution will repopulate the list box.

Did the code work for you in the cascading combo box? The cascading combo
boxes can do the job of filtering records by the "Street Number" in that list
box.

I have been trying to use the ItemData property but have not been
successful with the examples I have found on the Internet.

I don't think it's practical to use VBA just to filter records on a list box.
Although it can be done by code, it's basically the same thing using "Do
Loop" or "For Next Loop", unless...... you say " I don't want to use combo boxes.
"

Show us your code, so that others might be able to help, if they have better
solutions. Remember, nobody can see your database/design except yourself.

[e-mail] wrote:

--
Please Rate the posting if helps you

Message posted viahttp://www.accessmonster.com

I think I have it.

Dim lngListNo As Long

lngListNo = Me.cboStNo.ListIndex
Me.lstResidents.Selected(lngListNo) = True

If there are 3 residents at 123 Main Street, 123 is listed 3 times in
cboStNo. Not very elegant, but this is working for me. I'll work on
the niceties later.

Thanks for all your help.

Deborah

[dgardner]

This does exactly what I want. I found the code at www.lebans.com and
modified it slightly to work with my app.

Dim lngListNo As Long

lngListNo = Me.cboStNo.ListIndex

' NumRows is the number of completely visible rows in the ListBox
Const NumRows = 7
' Row we want displayed in middle of ListBox.
Dim intDesiredRow As Integer
intDesiredRow = lngListNo
' ListBox must have the Focus
Me.lstResidents.SetFocus
' Force ListBox to start from the top
Me.lstResidents.ListIndex = 1

' Force the Scroll offset we desire
Me.lstResidents.ListIndex = intDesiredRow
' Now select the row without further scrolling
Me.lstResidents.ListIndex = intDesiredRow

[AccessVandal via AccessMonster.com]

Hi Deborah,

I hope the code works for you.

The logic of your code selects only the first row of the list box or the last
row.

>dg******@NOSPAM.twcny.rr.com wrote:
This does exactly what I want. I found the code at www.lebans.com and
modified it slightly to work with my app.

Dim lngListNo As Long

lngListNo = Me.cboStNo.ListIndex

' NumRows is the number of completely visible rows in the ListBox
Const NumRows = 7
' Row we want displayed in middle of ListBox.
Dim intDesiredRow As Integer

intDesiredRow = lngListNo
' ListBox must have the Focus
Me.lstResidents.SetFocus
' Force ListBox to start from the top
Me.lstResidents.ListIndex = 1

' Force the Scroll offset we desire
Me.lstResidents.ListIndex = intDesiredRow
' Now select the row without further scrolling
Me.lstResidents.ListIndex = intDesiredRow

--
Please Rate the posting if helps you

Message posted via http://www.accessmonster.com