command click button msgbox

[Mark]

Hi,

I need some help in trying to get the following to work.
I have two forms (ClassCalendar and ClassRegistration).
From the switchboard, the user will go to the
ClassCalendar form, select a date range, and click a
command button that will open up the ClassRegistration
form. The ClassRegistration form has a pull-down text box
that lists all the classes that are available for the
selected date range picked from the ClassCalendar form.
The pull-down text box is populated from a query that
lists the classes' id number. The form also has a subform
that lists all of the classes' details, such as title,
location, etc.

All works fine, but I would like to have a message box
come up if there are no classes available for the date
range the user has selected.

The Click command button on the "formClassCalendar" is
listed below:

Private Sub ViewClasses_Click()

If IsNull(Me.TextBeginningDate) Then
MsgBox "You must enter a beginning date.",
vbExclamation, "Please enter beginning date"
Me.TextBeginningDate.SetFocus
cmdSetDates.Caption = "Set Beginning Date"

ElseIf IsNull(Me.TextEndDate) Then
MsgBox "You must enter an ending date.",
vbExclamation, "Please enter ending date"
Me.TextEndDate.SetFocus
cmdSetDates.Caption = "Set Ending Date"

ElseIf Me.TextBeginningDate > Me.TextEndDate Then
MsgBox "Ending date must be on or after Beginning
date.", vbInformation, "Please enter correct date range"
Me.TextEndDate.SetFocus
cmdSetDates.Caption = "Set Ending Date"

Else
'input is good, look in the DB
dim rstClasses as new Adodb.recordset
dim sqlClasses as string

sqlClasses = "SELECT key FROM Classes WHERE Date
Between [Forms]![formClassCalendar]![TextBeginningDate]
And [Forms]![formClassCalendar]![TextEndDate] And >=Date
();"

rstClasses.Open sqlClasses,
CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

If rstClasses.EOF then
'no records found
msgbox("No classes found")
Else

On Error GoTo Err_ViewClasses_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "formClassRegistrationByCalendar"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
rstclasses.close
set rstclasses = nothing


Exit_ViewClasses_Click:
Exit Sub

Err_ViewClasses_Click:
MsgBox Err.Description
Resume Exit_ViewClasses_Click

End If
End Sub


I am having problems with the following part of the above
code (between the "Else" statements):

Else
'input is good, look in the DB
dim rstClasses as new Adodb.recordset
dim sqlClasses as string

sqlClasses = "SELECT key FROM Classes WHERE Date
Between [Forms]![formClassCalendar]![TextBeginningDate]
And [Forms]![formClassCalendar]![TextEndDate] And >=Date
();"

rstClasses.Open sqlClasses,
CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

If rstClasses.EOF then
'no records found
msgbox("No classes found")
Else

Any help getting this to work would be a huge help.
Thanks!!!!

 

[cTaHk0]

replace your code:

sqlClasses = "SELECT key FROM Classes WHERE Date
Between [Forms]![formClassCalendar]![TextBeginningDate]
And [Forms]![formClassCalendar]![TextEndDate] And >=Date
();"

with this:
Dim strBeginningDate As String
Dim strEndDate As String
Dim strDate As String
..
..
..
strBeginningDate =
Format([Forms]![formClassCalendar]![TextBeginningDate], "MM") + "/" +
Format([Forms]![formClassCalendar]![TextBeginningDate], "DD") + "/" +
Format([Forms]![formClassCalendar]![TextBeginningDate], "YY")
strEndDate = Format([Forms]![formClassCalendar]![TextEndDate], "MM") +
"/" + Format([Forms]![formClassCalendar]![TextEndDate], "DD") + "/" +
Format([Forms]![formClassCalendar]![TextEndDate], "YY")
strDate = Format(Date, "MM") + "/" + Format(Date, "DD") + "/" +
Format(Date, "YY")

sqlClasses = "SELECT key FROM Classes WHERE Date Between #" &
strBeginningDate & "# And #" & strEndDate & "# And Date >= #" & strDate &
"#"
..
..
..

hope I help you,

cTaHk0