My Last Post about List box solution didn't work..Try to exp bette

[BrianPaul]

Try to post the question more efficient this time.

I have a listbox on a form that has for example the following dates:

Jan 1
jan 3
jan 5
jan 7

When I select one of these days for example Jan 3. another listbox opens and
lists all appointments for that day.

Question: If today was january ,6 (I have no Jan 6 in the above example) I
want to be able to open the form and set the focus to the listbox and
highlight Jan 7 since its the next date. I tried to use <Date() and it
erased all the listbox entries prior to todays date. Can it be Done? and how?

Thanks.

 

[Dirk Goldgar]

Try to post the question more efficient this time.

I have a listbox on a form that has for example the following dates:

Jan 1
jan 3
jan 5
jan 7

When I select one of these days for example Jan 3. another listbox
opens and lists all appointments for that day.

Question: If today was january ,6 (I have no Jan 6 in the above
example) I want to be able to open the form and set the focus to the
listbox and highlight Jan 7 since its the next date. I tried to use
<Date() and it erased all the listbox entries prior to todays date.
Can it be Done? and how?

Thanks.

Try the following completely untested air code. I'm assuming that you
want this to happen every time the form is opened, so I'll put the code
in the form's Load event. It might work in the Open event, too, but I
chose the Load event to make sure all controls have been instantiated.
I'm also assuming that the rows in the list box are either displaying
full dates, not just month and day, or else they only contain dates in
the current year.

'----- start of air code -----
Private Sub Form_Load()

Dim lRow As Long

With Me.lstMyListBox ' ** substitute name

For lRow = Abs(.ColumnHeads) To (.ListCount - 1)
If CDate(.ItemData(lRow)) >= Date() Then
.Value = .ItemData(lRow)
Call lstMyListBox_AfterUpdate ' ** substitute name
Exit For
End If
Next lRow

End With

End Sub
'----- end of air code -----

NOTE: On the lines marked " ** substitute name", substitute the name of
your list box for "lstMyListBox".

 

[BrianPaul]

Thanks Dirk, You have always came through.

Try the following completely untested air code. I'm assuming that you
want this to happen every time the form is opened, so I'll put the code
in the form's Load event. It might work in the Open event, too, but I
chose the Load event to make sure all controls have been instantiated.
I'm also assuming that the rows in the list box are either displaying
full dates, not just month and day, or else they only contain dates in
the current year.

'----- start of air code -----
Private Sub Form_Load()

Dim lRow As Long

With Me.lstMyListBox ' ** substitute name

For lRow = Abs(.ColumnHeads) To (.ListCount - 1)
If CDate(.ItemData(lRow)) >= Date() Then
.Value = .ItemData(lRow)
Call lstMyListBox_AfterUpdate ' ** substitute name
Exit For
End If
Next lRow

End With

End Sub
'----- end of air code -----

NOTE: On the lines marked " ** substitute name", substitute the name of
your list box for "lstMyListBox".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[BrianPaul]

Bummer, Get a data type mismatch error. I checked to settings on the field
in the listbox query and it is a date. I will keep trying to work with it to
see if I can get it to work.
Thanks

 

[Dirk Goldgar]

Bummer, Get a data type mismatch error. I checked to settings on the
field in the listbox query and it is a date. I will keep trying to
work with it to see if I can get it to work.

What is the SQL of the list box's RowSource query? Which column is the
bound column?

Also, which line of code is giving the type mismatch error?