Assistance with vbYesNo

  • Thread starter Thread starter eckert1961
  • Start date Start date
E

eckert1961

Hello,

I would like to add vbYesNo to the following code that will have the
following result. I want the user to be prompted for, "Does this class run on
Mondays?" If the response is YES then I want the code to run as is. If NO
then I want Monday's to be excluded. Please let me know if any clarification
is required. Thank you.

Option Compare Database

Public Function GetLabel(ClassDayID As Long, ClassDay As Date)

'set up connection and recordset

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myrecset As New ADODB.Recordset
myrecset.ActiveConnection = cnn1
Dim mySQL As String

mySQL = "SELECT ClassDay FROM tblClassDays WHERE fkClassesID=" & ClassDayID
& " Order by ClassDay asc"
'open the recordset of project days for the project #

'if the date is a special day return the type from tblSpecial days
If DCount("pkSpecDateID", "tblSpecialDays", "dteSpec=#" & ClassDay &
"#") <> 0 Then
GetLabel = DLookup("txtType", "tblSpecialDays", "dteSpec=#" &
ClassDay & "#")

Else
'if the date is a Saturday or Sunday and not a special day
return the label Wknd
If Weekday(ClassDay) = 1 Or Weekday(ClassDay) = 3 Or
Weekday(ClassDay) = 5 Or Weekday(ClassDay) = 7 And DCount("pkSpecDateID",
"tblSpecialDays", "dteSpec=#" & ClassDay & "#") = 0 Then
GetLabel = "Wknd"
Else
'if the date is not a weekend date or special get the loop
through to assign a seq #
myrecset.Open mySQL
Do Until myrecset.EOF
'if date in the project detail record is not a weekend or
special day add to the counter
If Weekday(myrecset!ClassDay) <> 1 Then
If Weekday(myrecset!ClassDay) <> 3 Then
If Weekday(myrecset!ClassDay) <> 5 Then
If Weekday(myrecset!ClassDay) <> 7 Then
If DCount("pkSpecDateID", "tblSpecialDays",
"dteSpec=#" & myrecset!ClassDay & "#") <> 1 Then
c = c + 1
If ClassDay = myrecset!ClassDay Then
GetLabel = "Day " & c
End If
End If
End If
End If
End If
End If
myrecset.MoveNext
Loop
myrecset.Close
End If
End If


Set myrecset = Nothing

End Function
 
I changed my approach and ended up with the following.

Dim ans As Integer

ans = MsgBox("Does this class run on Mondays?", vbYesNo, "Monday Class?")

If Not IsNull(Me.filter) And (ans = vbYes) Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryGetMWF"
Me.Refresh
DoCmd.OpenReport "Attendance", A_PREVIEW, , Me.Form.filter
DoCmd.Maximize
DoCmd.SetWarnings True
ElseIf (ans = vbNo) Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryGetWF"
Me.Refresh
DoCmd.OpenReport "Attendance", A_PREVIEW, , Me.Form.filter
DoCmd.Maximize
DoCmd.SetWarnings True
Else
MsgBox "Apply a filter to the form first"
End If

It works great but if anyone has any suggestions on how to improve, it let
me know.
 
I changed my approach and ended up with the following.

Dim ans As Integer

ans = MsgBox("Does this class run on Mondays?", vbYesNo, "Monday Class?")

If Not IsNull(Me.filter) And (ans = vbYes) Then
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryGetMWF"
    Me.Refresh
    DoCmd.OpenReport "Attendance", A_PREVIEW, , Me.Form.filter
    DoCmd.Maximize
    DoCmd.SetWarnings True
ElseIf (ans = vbNo) Then
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryGetWF"
    Me.Refresh
    DoCmd.OpenReport "Attendance", A_PREVIEW, , Me.Form.filter
    DoCmd.Maximize
    DoCmd.SetWarnings True
Else
    MsgBox "Apply a filter to the form first"
End If

It works great but if anyone has any suggestions on how to improve, it let
me know.
--
Regards,
Chris















- Show quoted text -
Click to expand...

Perhaps something like this?

Dim ans As Integer
ans = MsgBox("Does this class run on Mondays?", vbYesNo, "Monday
Class?")
DoCmd.SetWarnings False
If (Not IsNull(Me.Filter) And (ans = vbYes)) Or (ans = vbNo) Then
DoCmd.OpenQuery IIf(ans = vbYes, "qryGetMWF", "qryGetWF")
Me.Refresh
DoCmd.OpenReport "Attendance", A_PREVIEW, , Me.Form.Filter
DoCmd.Maximize
Else
MsgBox "Apply a filter to the form first"
End If
DoCmd.SetWarnings True

Also, it's bit of a moot point, but in your first section of code, a
CASE statement would be very helpful to replace all those IFs.

Hope this helps,
Chris M.
 
Thanks Chris,

Your code is much cleaner. I pasted it into the module and everything works
as expected.

I'll also look at implementing your suggestion about using a Case statement
in the other code that I posted. I really appreciate your input.
 
Back
Top