form command button code

[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. How do I go about doing this?

I'm not sure where I would use an event procedure in one
of the forms properties and how the code would be
written. The query is based on criteria of "ClassDate":

Between [Forms]![formClassCalendar]![TextBeginningDate]
And [Forms]![formClassCalendar]![TextEndDate] And >=Date()

Should I accomplish this in the click command button? 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

On Error GoTo Err_ViewClasses_Click

Dim stDocName As String
Dim stLinkCriteria As String

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

Exit_ViewClasses_Click:
Exit Sub

Err_ViewClasses_Click:
MsgBox Err.Description
Resume Exit_ViewClasses_Click

End If
End Sub


Thanks for taking a look at this!!!

 

[Gert-Jan van der Kamp]

Mark,

It's in the code below. Check it first to make sure it corresponds with your
tablestructure etc.

Regards,

Gert-Jan

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. How do I go about doing this?

I'm not sure where I would use an event procedure in one
of the forms properties and how the code would be
written. The query is based on criteria of "ClassDate":

Between [Forms]![formClassCalendar]![TextBeginningDate]
And [Forms]![formClassCalendar]![TextEndDate] And >=Date()

Should I accomplish this in the click command button? 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