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.
--
Regards,
Chris
"mcescher" wrote:
> On Apr 20, 9:28 pm, eckert1961 <eckert1...@discussions.microsoft.com>
> wrote:
> > 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
> >
> >
> >
> > "eckert1961" wrote:
> > > 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
> >
> > > --
> > > Regards,
> > > Chris- Hide quoted text -
> >
> > - Show quoted text -
>
> 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.
> .
>
|
Similar Threads
