scroll to item in listbox VBA

[steven80]

Hi,

I have a multiselect listbox in a form base on a query that contains an ID field, a name of an event and a date.

When i open my form i would like the listbox to scroll to the first upcoming event. It doesn't have to select it, just scroll to it so that it's on top in the list.

I already have the ID stored in a variable, so I just have to say to the listbox to go to that id.

Does anybody know how to do this?

Thanks
Steven

 

[arnelgp]

Dim i As Long
For i = 0 To Me.list.ListCount-1
If Me.List.Column(0) = ID Then Exit For
Next
Me.List = Me.list.ItemData(i)

 

[steven80]

Thanks for the help, but i got an error on the line:
Me.List = Me.list.ItemData(i)

Here is my code, maybe you can see what I did wrong.

Dim b As Long
For b = 0 To Me.cboBeschikbaar.ListCount - 1
If Me.cboBeschikbaar.Column(0) = RijID Then
Exit For
Else
End If
Next b
Me.cboBeschikbaar = Me.cboBeschikbaar.ItemData(b)

If I read out the code while running I get following values:
me.cbobeschikbaar.column(0) = "27;28;29"
RijID = 2
I already tried to adjust the (b) to (2) in the last line, but that also gives an error.

Thanks in advance

Steven

 

[mulch17]

Thanks for the help, but i got an error on the line:
Me.List = Me.list.ItemData(i)

Here is my code, maybe you can see what I did wrong.

Dim b As Long
For b = 0 To Me.cboBeschikbaar.ListCount - 1
If Me.cboBeschikbaar.Column(0) = RijID Then
Exit For
Else
End If
Next b
Me.cboBeschikbaar = Me.cboBeschikbaar.ItemData(b)

If I read out the code while running I get following values:
me.cbobeschikbaar.column(0) = "27;28;29"
RijID = 2
I already tried to adjust the (b) to (2) in the last line, but that also gives an error.

Thanks in advance

Steven

Try this instead:

Dim b As Long
For b = 0 To Me.cboBeschikbaar.ListCount - 1
If Me.cboBeschikbaar.Column(0, b) = RijID Then
Exit For
Else
End If
Next b
Me.cboBeschikbaar = Me.cboBeschikbaar.ItemData(b)

Your last code didn't have b inside of the loop, so it was running the same check over and over.

The second parameter of Column is the row index. That's what b is. Column(0, b) checks the first column of each row until it finds a match. If you're expecting RijID to be in the 2nd or 3rd column of the listbox, just change it to Column(1, b) or Column(2, b), respectively.

Does that take care of it for you?

EDIT: This doesn't have anything to do with the question, but is there a reason why your list box is named cboBeschikbaar, and not lstBeschikbaar? If cboBeschikbaar is referring to a list box, and not a combo box, I think that could be a little confusing.

 

[steven80]

Thanks for the help, but i still have an error: 'Invalid use of Null' on the line

Me.cboBeschikbaar = Me.cboBeschikbaar.ItemData(b)

The listbox cboBeschikbaar has following fields in it: ID, name and date, and is sorted ascending on the date field.

(The name cboBeschikbaar instead of LstBeschikbaar is because i changed the control to a listbox, but when i developed the form it was first a combobox. I know the name is a bit confusing, but it is definitely a listbox ;-) )


Thanks

Steven

 

[Cronk]

What is the "first" event? The oldest or the newest?

If you don't want to have the list box highlight the item, just sort the row source on date, either ascending or descending depending on your definition of first.

 

[steven80]

The first event is the one with the oldest date but not the oldest or newest in ID

so, the results of the query where the listbox is based on looks a bit like this:

id name date
5 event1 22/01/16
3 event 2 25/01/16
7 event 3 26/01/16
2 event 4 28/01/16
...

In this case i the listbox to have the line with id = 2 on top

At noon i will zip a part of the database and post it here.

Thanks

Steven

 

[steven80]

Here the database attached.

You can find the code in the form frmContacten at the bottom of the Private Sub Form_Current()


Thanks

Steven

 

[mulch17]

Try changing Column(0, b) to Column(1, b). Column 0 has your check-boxes, Column 1 is the one that actually has the ID value. Does that fix it for you?

 

[steven80]

Ok, that helps already, but now i get a runtime error 2147352567(80020009)

Is it because my first colum (ID) has the type autonumber?

 

[mulch17]

I actually got the same error when I tested it in your database, I was hoping that it was just something with my machine.

I have tried searching for descriptions for that error message and I haven't found any. I'm afraid this one is above my pay grade. I would have to wait to see what somebody else here says.

 

[Cronk]

If your list box row source is based on your query (say qryEvents) and you say the query is sorted in ascending date order, then what you are wanting is the first row in the list box to be highlighted.

The earliest date can be found from

Dmin("EventDate","qryEvents")

Assuming the list box is bound to the ID field, then you just need to set the value of the list box to the corresponding ID for the earliest date, ie

lstYourListBox = DLookup("ID", "qryEvents", "Eventdate=#" & Format(DMin("eventDate", "qryEvents"), "mm/dd/yyyy") & "#")

 

[steven80]

I can't set a value to the listbox.

I tried with giving it a hard value in my code:

lstBeschibaar = 2, but then it still returns the error 2147352567(80020009)

 

[Cronk]

Have you compiled your code?

If there are no compile errors, maybe your database is corrupted. Try a Compact and Repair and if that does not fix the problem, create a new database and import all objects.

 

[steven80]

I didn't find a solution to solve the error, so I adjusted my code a bit and did a whole new approach were I only load the events that find place in the future in the listbox.

Not exactly what I wanted, but it works fine too for me.

Anyway, thanks to you all for the help!!!

Kind regards

Steven