Michael Blakes CalendarDB code question

[Chris Ryner]

Has anyone used Michael Blakes Access97 CalendarDB? I have Been modifying
it to better suit my specific needs and I have run into a snag with some
coding on the SQL for populating the list boxes.

The list box for a specific day will contain the word new (added via Union)
always at the top of the list. then all of the appts for that day follow.
The appts don't appear to be in order. How can I make this happen so that
the earliest appt for a day is the first in that days list box following Add
New ???

Any one have a solution please feel free to reply as I have exhausted my
abilities. I feel that the strSQL or strUnionSQL need to have an orderby
something.

here is the code that populates the list boxes.




Private Sub FillDates()
'*******************************************
'Author: Michael Blake
'Contact Via: www.weAscend.com
'Date: October 12, 2000, 04:09:01 PM
'Copyright ©2000 Michael Blake
'Please keep comments intact when using code
'*******************************************


ReDim aGridDate(1 To 42)
Dim i As Integer
Dim strRowSource As String
Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Dim fEmpty As Boolean
Dim strUnionSQL As String

strSQL = "SELECT " & strSource & "." & strFieldID & ", " & _
strSource & "." & strField & " FROM " & strSource

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

If fNewOption Then
strUnionSQL = " UNION SELECT " & Chr(34) & Chr(34) & " AS " & strFieldID
& ", " & Chr(34) & _
"Add New" & Chr(34) & " AS " & strField & " FROM " &
strSource
Else
strUnionSQL = ""
End If

aGridDate(1) = fCalendarDay([cboMonth], [cboYear], 1, 1)
Me.txt1.Value = Int(Format(aGridDate(1), "dd"))
Me.lst1.ColumnCount = 2
Me.lst1.ColumnWidths = "0;1"
fEmpty = (rs.BOF And rs.EOF)
If fEmpty Then
strRowSource = Mid(Nz(strUnionSQL, "1234567 "), 8)
Else
strRowSource = "SELECT " & strSource & "." & strFieldID & ", " & _
strSource & "." & strField & " FROM " & strSource & _
" WHERE (((" & strSource & "." & strDateField & ")= #" &
Format(aGridDate(1), "mm/dd/yyyy") & "#))" & _
strUnionSQL
End If
Me.lst1.RowSource = strRowSource
For i = 2 To 42
aGridDate(i) = DateAdd("d", i - 1, aGridDate(1))
Controls("txt" & i).Value = Int(Format(aGridDate(i), "dd"))
If fEmpty Then
strRowSource = Mid(Nz(strUnionSQL, "1234567 "), 8)
Else
strRowSource = "SELECT " & strSource & "." & strFieldID & ", " & _
strSource & "." & strField & " FROM " & strSource & _
" WHERE (((" & strSource & "." & strDateField & ")= #" &
Format(aGridDate(i), "mm/dd/yyyy") & "#))" & _
strUnionSQL
End If

Controls("lst" & i).RowSource = strRowSource
Next i
Set db = Nothing: Set rs = Nothing

End Sub

 

[Albert D. Kallal]

Have you tried adding a "order by" clause to your sql?

The results of sql is NEVER in any order unless you speicifty it...

I would try adding a order by clause....

 

[Kilmer]

Hi Chris,

This is in reply to your private email to me as well. Use a query as
the source for the calendar. In that query sort by the date and then
sort by the time. The items will be sorted then.

HTH
Michael

Has anyone used Michael Blakes Access97 CalendarDB? I have Been modifying
it to better suit my specific needs and I have run into a snag with some
coding on the SQL for populating the list boxes.

The list box for a specific day will contain the word new (added via Union)
always at the top of the list. then all of the appts for that day follow.
The appts don't appear to be in order. How can I make this happen so that
the earliest appt for a day is the first in that days list box following Add
New ???

Any one have a solution please feel free to reply as I have exhausted my
abilities. I feel that the strSQL or strUnionSQL need to have an orderby
something.

here is the code that populates the list boxes.

<snip>

 

[Chris Ryner]

sorry i accidently hit reply sender instead of group.

I am new vb and can't figure out the syntax of the SQL what I do know is
from my previous post:

I feel that the strSQL or strUnionSQL need to have an orderby
something.

what i can't do is figure out what the sql should say. Trial and Error has
already taken 3 days.

See complete FILLdate procedure for Code from original message:

i have tried

strRowSource = "SELECT " & strSource & "." & strFieldID & ", " & _
strSource & "." & strField & " FROM " & strSource & _
" WHERE (((" & strSource & "." & strDateField & ")= #" &
Format(aGridDate(i), "mm/dd/yyyy") & "#))" & "ORDERBY [AppointmentTIME]" & _
strUnionSQL

and several other variations to no avail

this resulted in empty list boxes

 

[Chris Ryner]

Sorry I meant to reply group!

I wouldn't know where to begin to modify this like that. This form is full
of list boxes and the Sql loads these list boxes from a query I assume as i
found the display field in qryAppointmentWhen and modified it and got the
display on the calendar form to read "10:00 AM John Doe"
But I have applied sorts to this query and nothing happens.

Any ideas