Air Code Mr. Goldgar Cascade Listbox to highlite, select closest

[BrianPaul]

Wow, was looking back through the post and found where you had replied. I
probebly shouldn't havn't started a new thread but was thinking I have never
seen a post simular to this one. I even checked Monster Access and couldn't
find anything simular.

If you can figure it out, This code should probebly be posted on your web
site. I can see where this code could be very useful for an appointment,
memo, schedule type database. I Nixed the first database but kept the second
one as explained below.

If CDate(.ItemData(lRow)) >= Date() Then
.Value = .ItemData(lRow) 'This is where I was getting the error in both
databases

'Original code given

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

Here is my SQL Statement:

SELECT QScoresDate.Day, QScoresDate.[Date of Game]
FROM QScoresDate
ORDER BY QScoresDate.[Date of Game];

I Even Changed the listbox properties to only use one select statement and
reflect the full date.

Sql Statement of the second Listbox which the first listbox filtered.

SELECT QGameScheduleAll.gameId, QGameScheduleAll.[Date of Game],
QGameScheduleAll.[Ranking Home Team], QGameScheduleAll.[Home Team Name],
QGameScheduleAll.[Home team Score], QGameScheduleAll.[Visiting Team Ranking],
QGameScheduleAll.[Visiting Team Name], QGameScheduleAll.[Visiting Team Score]
FROM QGameScheduleAll
WHERE (((QGameScheduleAll.[Date of Game])=[forms]![ScoreBoardMenu]![List65]))
ORDER BY IsNull([Visiting Team Ranking]) DESC , QGameScheduleAll.[Visiting
Team Ranking], IsNull([ranking home team]) DESC , QGameScheduleAll.[Ranking
Home Team], QGameScheduleAll.[Visiting Team Ranking];

So I actually tried it in two databases.

The bound colum in both databases were 0. The difference in this example is
the first column 0 is a date with its properties set to dddd the second
column 1 set to short date. However I just tried it with 1 column then
setting it to long date.

Thanks, Mr. Goldarg

[Dirk Goldgar]

Please see my comments and questions inline.

"BrianPaul" <Bria...@discussions.microsoft.com> wrote in message
news:F3AAD981-7A28-4336...@microsoft.com

> Wow, was looking back through the post and found where you had
> replied. I probebly shouldn't havn't started a new thread but was
> thinking I have never seen a post simular to this one. I even
> checked Monster Access and couldn't find anything simular.

I'm not sure why you started a new thread. It's easier for anybody
following a discussion if the whole discussion remains in the original
thread. But anyway ...

> If you can figure it out, This code should probebly be posted on your
> web site. I can see where this code could be very useful for an
> appointment, memo, schedule type database. I Nixed the first
> database but kept the second one as explained below.

When you talk about "databases", are you talking about separate .mdb
files? I just ask because sometimes people use the word "database" when
they are just talking about tables, and it can be confusing.

I'm afraid I have to ask you for still more information. What is the
SQL of QScoresDate? I'm assuming that's another query. If QScoresDate
is a date formatted as "dddd", then there's no way for the list box to
tell one week's "Friday" from another week's "Friday", if that column is
the bound column. Does the list box contain more than one week's dates?

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

(please reply to the newsgroup)

[BrianPaul]

What is the SQL of QScoresDate?

SELECT TGameSchedule.[Date of Game], TGameSchedule.[Date of Game] AS [Day]
FROM TGameSchedule
GROUP BY TGameSchedule.[Date of Game], TGameSchedule.[Date of Game]
ORDER BY TGameSchedule.[Date of Game]

It has the entire NCAA Division I football Schedule.

So If for example I selected 23 Sep 06 This would give me all the games
played on this date . This would explain the 2nd listbox.

However I could always change the colums around or just delete the 1 st
colum that is formatted dddd which results Saturday 23 Sep 06. To
just 1 column so it would be bound to the long date. Or just change them
around. On the second listbox that has a list of all the teams playing on
that date I just set the listbox to = Column 1 and not Column 0 which was
formated as dddd.

I already tried that but still got the error because you mentioned I had to
have the full date in the original post.

Thanks, I thought I could get it to work still from your answer in the first
post If I set the listbox to your specified criteria.

[Dirk Goldgar]

"BrianPaul" <Bria...@discussions.microsoft.com> wrote in message

news:553FE999-C5C3-4A0B...@microsoft.com

I would definitely set the list box's bound column to the column that
contains the unique date value, not to the column that contains the day
name. I also suggest you rewrite the query QScoresDate to explicitly
format the Day value. And I'd use a different name for the calculated
field, instead of "Day", which I believe is a reserved word (though it's
not causing you a problem at the moment).

So, use this SQL for QScoresDate:

SELECT DISTINCT
[Date of Game],
Format([Date of Game], "dddd") AS GameDay
FROM TGameSchedule
ORDER BY [Date of Game];

Then set your list box's properties as follows:

Row Source:
SELECT GameDay, [Date of Game] FROM QScoresDate
ORDER BY [Date of Game];

Bound Column: 2
Column Count: 2

Then I believe that this code ought to work as I originally posted it
(after you substitute the list box name as indicated):

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

Try that and let me know how it goes.

[BrianPaul]

Will try it this evening after the high school football game and get back
with you.

Thanks,

Brian

[BrianPaul]

Still the same error code 13 data type mismatch
If CDate(.ItemData(lRow)) >= Date Then

[BrianPaul]

I deleted one of the colums in the listbox and here is what is left

SELECT QScoresDate.[Date of Game] AS Home

FROM QScoresDate
ORDER BY QScoresDate.[Date of Game];

Still game me error 13 Type mismatch.

here is the code I used:

Private Sub Form_Load()

Dim lRow As Long

With Me.List65 ' ** substitute name

For lRow = Abs(.ColumnHeads) To (.ListCount - 1)

If CDate(.ItemData(lRow)) >= Date Then
.Value = .ItemData(lRow)
Call List65_AfterUpdate ' ** substitute name

Exit For
End If
Next lRow

End With
End Sub

I figured narrow it down to just 1 column so any other possible variables
would be eliminated from causeing any errors with the code.

Thanks

[Dirk Goldgar]

"BrianPaul" <Bria...@discussions.microsoft.com> wrote in message

news:ED5A8BB9-ACAF-47AE...@microsoft.com

I'm obviously overlooking something. Try inserting a debugging
statement to display the value of each row's bound column, like this:

For lRow = Abs(.ColumnHeads) To (.ListCount - 1)

Debug.Print "Row "; lRow, .ItemData(lRow) '**debugging

If CDate(.ItemData(lRow)) >= Date Then

After the code runs and the error is raised, check the Immediate Window
for the list of values associated with each row.

[BrianPaul]

Here is the result
opened the form with the code. When error was generated. hit the end
command button, then pressed CTRL G for window with following result:

Row 0

here is the SQL statement of the listbox 65

SELECT QScoresDate.[Date of Game]

FROM QScoresDate
ORDER BY QScoresDate.[Date of Game];

Here is the code you gave me:

Private Sub Form_Load()
Dim lRow As Long

With Me.List65 ' ** substitute name

For lRow = Abs(.ColumnHeads) To (.ListCount - 1)

Debug.Print "Row "; lRow, .ItemData(lRow) '**debugging

If CDate(.ItemData(lRow)) >= Date Then

.Value = .ItemData(lRow)
Call List65_AfterUpdate ' ** substitute name
Exit For
End If
Next lRow

End With
End Sub

Hope that helps

[Dirk Goldgar]

"BrianPaul" <Bria...@discussions.microsoft.com> wrote in message

news:BDFD2477-3B9F-4933...@microsoft.com

> Here is the result
> opened the form with the code. When error was generated. hit the end
> command button, then pressed CTRL G for window with following result:
>
> Row 0
>
> here is the SQL statement of the listbox 65
> SELECT QScoresDate.[Date of Game]
> FROM QScoresDate
> ORDER BY QScoresDate.[Date of Game];
>
> Here is the code you gave me:
> Private Sub Form_Load()
> Dim lRow As Long
>
> With Me.List65 ' ** substitute name
>
> For lRow = Abs(.ColumnHeads) To (.ListCount - 1)
> Debug.Print "Row "; lRow, .ItemData(lRow) '**debugging
>
> If CDate(.ItemData(lRow)) >= Date Then
> .Value = .ItemData(lRow)
> Call List65_AfterUpdate ' ** substitute name
> Exit For
> End If
> Next lRow
>
> End With
> End Sub
>
> Hope that helps

That suggests that the very first row of the list box has NO DATE. Look
at the results of the query QScoresDate and see what's up.

[BrianPaul]

You were correct. Ran the querry and the very first record was null, However
I never noticed it in the list box. Seems like it would show. Mystery solved
thanks alot for the solution.

[BrianPaul]

Dirk, Again it worked okay. However, How would I simulate selecting the
date. Reason is that the code does work and does highlight the record in the
list box but the 2nd listbox doesn't display the results unless I actually
click on the date that is highlighted. Im thinking I probebly should use
the before update event to do this. or maby on the 2nd listbox use the before
update. Any suggestions.

[Dirk Goldgar]

"BrianPaul" <Bria...@discussions.microsoft.com> wrote in message

news:31A7690E-2450-443C...@microsoft.com

> Dirk, Again it worked okay. However, How would I simulate selecting
> the date. Reason is that the code does work and does highlight the
> record in the list box but the 2nd listbox doesn't display the
> results unless I actually click on the date that is highlighted. Im
> thinking I probebly should use the before update event to do this. or
> maby on the 2nd listbox use the before update. Any suggestions.

I was assuming that the list box's AfterUpdate event took care of
requerying the second list box. If that were so, then the line

Call List65_AfterUpdate

ought to have made the second list box show the results. If that's not
working, but clicking on an entry in the first list box does work, then
I guess you're using a different event of the list box.

Please post all the code you have for any of the events of the first lis
box (List65, unless you've renamed it).

[BrianPaul]

Here is the code:

SELECT QGameScheduleAll.gameId, QGameScheduleAll.[Date of Game],
QGameScheduleAll.[Ranking Home Team], QGameScheduleAll.[Home Team Name],
QGameScheduleAll.[Home team Score], QGameScheduleAll.[Visiting Team Ranking],
QGameScheduleAll.[Visiting Team Name], QGameScheduleAll.[Visiting Team Score]
FROM QGameScheduleAll

WHERE (((QGameScheduleAll.[Date of Game])=[forms]![ScoreBoardMenu]![List92]))

ORDER BY IsNull([Visiting Team Ranking]) DESC , QGameScheduleAll.[Visiting
Team Ranking], IsNull([ranking home team]) DESC , QGameScheduleAll.[Ranking
Home Team], QGameScheduleAll.[Visiting Team Ranking];

Also in the first listbox:
Private Sub List65_AfterUpdate()
Me!List70 = Null
Me!List70.Requery
Me.List70.Selected(0) = False
End Sub

Im thinking the Me!list70=Null Comes out of this code:

Thats why it probebly isn't working. Uuuurr That didn't work just tried
taking out the statement.

Thanks

[Dirk Goldgar]

"BrianPaul" <Bria...@discussions.microsoft.com> wrote in message

news:F5316F36-CE2E-4815...@microsoft.com

> Here is the code:
>
> SELECT QGameScheduleAll.gameId, QGameScheduleAll.[Date of Game],
> QGameScheduleAll.[Ranking Home Team], QGameScheduleAll.[Home Team
> Name], QGameScheduleAll.[Home team Score], QGameScheduleAll.[Visiting
> Team Ranking], QGameScheduleAll.[Visiting Team Name],
> QGameScheduleAll.[Visiting Team Score] FROM QGameScheduleAll
> WHERE (((QGameScheduleAll.[Date of
> Game])=[forms]![ScoreBoardMenu]![List92])) ORDER BY IsNull([Visiting
> Team Ranking]) DESC , QGameScheduleAll.[Visiting Team Ranking],
> IsNull([ranking home team]) DESC , QGameScheduleAll.[Ranking Home
> Team], QGameScheduleAll.[Visiting Team Ranking];
>
> Also in the first listbox:
> Private Sub List65_AfterUpdate()
> Me!List70 = Null
> Me!List70.Requery
> Me.List70.Selected(0) = False
> End Sub

I take it that query you posted above is the rowsource of List70? But
that query makes no reference to List65 at all! Should it be saying
"[Forms]![ScoreBoardMenu]![List65]" where it is currently saying
"[forms]![ScoreBoardMenu]![List92]"?

[BrianPaul]

You are correct in your last post. I gave you the wrong list box. If you
recall to get around the problem I had before, I added another listbox then
set the date property in one going back from yesterday and the 2nd going from
today to the future. However, I set the 2 cascading listbox property views
to visible =No. until the date was clicked on in one of the two first list
boxes that overlapped each other. Sorry my fault I just selected the top
listbox and then sent you the SQL statement. So when I seen your post it
recalled my memory. So I deleted the last cascading listbox I created and
set the property visible= yes. I then loaded the form. and it worked. I
shouldn't have posted that it did highlight but didn't select the listbox.
Here is the code that should have been posted. but it all works now. My fault:

SELECT QGameScheduleAll.gameId, QGameScheduleAll.[Date of Game],
QGameScheduleAll.[Ranking Home Team], QGameScheduleAll.[Home Team Name],
QGameScheduleAll.[Home team Score], QGameScheduleAll.[Visiting Team Ranking],
QGameScheduleAll.[Visiting Team Name], QGameScheduleAll.[Visiting Team Score]
FROM QGameScheduleAll

WHERE (((QGameScheduleAll.[Date of Game])=[forms]![ScoreBoardMenu]![List65]))

[BrianPaul]

Dirk, I went completely back to the original post. I tried to apply the code
to the original SQL statement:

SELECT QScoresDate.Day, QScoresDate.[Date of Game]
FROM QScoresDate
ORDER BY QScoresDate.[Date of Game];

If you remember the first colum was formated to dddd and the 2nd colum to
short date. the result in the listbox would be for example: Sunday 7 Oct
96

For the solution I just used 1 field which you was working with me to
simplify.

When I applied the code to the original it still worked!....

So, if I would of caught the null value in the first place, I wouldn't have
the additional 15 posts just to figure it out. Do they have Special Ed. for
Access becuase Access for Dummies would kick my butt and if I was following
this post I would be shaking my head constantly. However, I would recommend
your solution be posted on your web site. Very useful. Title "select current
or closest date in listbox on form open" or something to that effect in the
title.

Thanks, again