Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Preview and Print code not working as expected 1

Status
Not open for further replies.
THWatson

THWatson

Technical User
Apr 25, 2000
2,601
CA
Using Access 2003

An Option Group on the Main Menu feeds text information to a form called frmDateSelector on which there is a text box called "Text7" and a text box called "txtWeekCheck."

What is fed into Text7 is "Attendance for a SPECIFIC DATE report.". The user then plugs in a date in txtWeekCheck, and then presses either Preview or Print.

The code behind the Preview button is
Code: 
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim stDocName As String

      
    If IsNull(Me.txtStartDate) And Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
    MsgBox "Please enter a Start Date.", vbOKOnly, "Start Date missing"
    Me.Undo
    Me.txtStartDate.SetFocus
    Exit Sub
    
    ElseIf IsNull(Me.txtEndDate) And Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
    MsgBox "Please enter an End Date.", vbOKOnly, "End Date missing"
    Me.txtEndDate.SetFocus
    Exit Sub
    
    ElseIf Me.txtEndDate < Me.txtStartDate And Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
    Call MsgBox("END Date must be greater than START Date!", vbExclamation, "Date check")
    Me.txtStartDate = ""
    Me.txtEndDate = ""
    Me.txtStartDate.SetFocus
    Exit Sub
    End If

stDocName = Me.Text7
Select Case stDocName

Case Is = "Attendance for a SPECIFIC DATE Report"
    If Weekday(Me.txtWeekCheck) <> 5 Then
        Select Case MsgBox("The date you entered is not a Thursday!" _
                       & vbCrLf & "" _
                       & vbCrLf & " Do you still wish a report for that date?" _
                       , vbYesNo Or vbExclamation Or vbDefaultButton1, "Is Date entered a Thursday?")
        Case vbYes
        DoCmd.OpenReport "rptAttendanceWeekly", acViewPreview, , "[MeetingDate] = Forms!frmDateSelector!txtWeekcheck"
            Select Case MsgBox("Do you wish to Preview the Guests for that date?", vbYesNo Or vbExclamation Or vbDefaultButton1, Application.Name)
           
            Case vbYes
           DoCmd.OpenReport "rptGuestWithName", acViewPreview, , "[GuestDate]=Forms!frmDateSelector!txtWeekCheck"
           Case vbNo
           Exit Sub
           End Select
    
        Case vbNo
            Me.txtWeekCheck = Null
            Me.txtWeekCheck.SetFocus
            Exit Sub
        End Select
    End If
End Sub

What this code is supposed to do is check whether or not the WeekDay from the date entered in txtWeekCheck is Thursday.

What is happening is that if the WeekDay is a Thursday, the Preview and Print command buttons do not work (they just sit there and stare at me...and not that invitingly either).

Curiously, if the WeekDay of the date entered in txtWeekCheck is NOT a Thursday, everything works fine.

The other interesting thing is that all of this worked fine until I changed what is fed into Text7 from the Main Menu, and changed all of the code in frmDateSelector, from "Attendance for a GIVEN WEEK Report" to "Attendance for a SPECIFIC DATE Report"

I'm puzzled.

Tom
 
Sort by date Sort by votes
Howdy THWatson . . .

Your codes does exactly what you designed it for. What you need to do is complete the [blue]If[/blue] statement for Thursday!

Code: 
[blue]   If Weekday(Me.txtWeekCheck) <> 5 Then [green]'Not Thursday![/green]
      [green]'Code for Not Thursday that works![/green]
   [red][b]Else[/b][/red] [green]'Is Thursday[/green]
      [green]'Your code for Thursday here[/green] [red][b](currently missing!)[/b][/red]
   End If[/blue]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
TheAceMan1

My thoughts? Oo boy. My main thought is I feel pretty much on the dumb side. I stared at that code for some time and couldn't quite get where I had run off the rails. But now it's as plain as day.

Code: 
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim stDocName As String

    If IsNull(Me.txtStartDate) And Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
    MsgBox "Please enter a Start Date.", vbOKOnly, "Start Date missing"
    Me.Undo
    Me.txtStartDate.SetFocus
    Exit Sub
    
    ElseIf IsNull(Me.txtEndDate) And Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
    MsgBox "Please enter an End Date.", vbOKOnly, "End Date missing"
    Me.txtEndDate.SetFocus
    Exit Sub
    
    ElseIf Me.txtEndDate < Me.txtStartDate And Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
    Call MsgBox("END Date must be greater than START Date!", vbExclamation, "Date check")
    Me.txtStartDate = ""
    Me.txtEndDate = ""
    Me.txtStartDate.SetFocus
    Exit Sub
    End If

stDocName = Me.Text7
Select Case stDocName

Case Is = "Attendance for a SPECIFIC DATE Report"
    If Weekday(Me.txtWeekCheck) <> 5 Then
        Select Case MsgBox("The date you entered is not a Thursday!" _
                       & vbCrLf & "" _
                       & vbCrLf & " Do you still wish a report for that date?" _
                       , vbYesNo Or vbExclamation Or vbDefaultButton1, "Is Date entered a Thursday?")
        Case vbYes
        DoCmd.OpenReport "rptAttendanceWeekly", acViewPreview, , "[MeetingDate] = Forms!frmDateSelector!txtWeekcheck"
            Select Case MsgBox("Do you wish to Preview the Guests for that date?", vbYesNo Or vbExclamation Or vbDefaultButton1, Application.Name)
           
            Case vbYes
           DoCmd.OpenReport "rptGuestWithName", acViewPreview, , "[GuestDate]=Forms!frmDateSelector!txtWeekCheck"
           Case vbNo
           Exit Sub
           End Select
    
        Case vbNo
            Me.txtWeekCheck = Null
            Me.txtWeekCheck.SetFocus
            Exit Sub
        End Select
    Else
    DoCmd.OpenReport "rptAttendanceWeekly", acViewPreview, , "[MeetingDate] = Forms!frmDateSelector!txtWeekcheck"
            Select Case MsgBox("Do you wish to Preview the Guests for that date?", vbYesNo Or vbExclamation Or vbDefaultButton1, Application.Name)
           
            Case vbYes
           DoCmd.OpenReport "rptGuestWithName", acViewPreview, , "[GuestDate]=Forms!frmDateSelector!txtWeekCheck"
           Case vbNo
           Exit Sub
           End Select
    
    End If
End sub

Too much work and too many funerals this week I guess. I conducted 2 already, and when we got home today from one there was a call for another one for early in the coming week.

That's what I get for pulling out of retirement. The church can be a persistent taskmaster!

Thanks, and best regards.

Tom
 
Upvote 0 Downvote
THWatson said:
[blue]I stared at that code for some time and couldn't quite get where I had run off the rails.[/blue]
Click to expand...

Believe me ... [blue]it happens to us all![/blue] [surprise]

Before I let you go I'd like to apprise you of your use of [blue]nested select statements[/blue]. Its not that they don't work in a nested fashion ... [blue]its that they make it harder to follow your logic flow[/blue] as you read! This makes it harder to troubleshoot your code! I believe its the [blue]nested select statments[/blue] that brought you to a hault. [pipe]

To make it easier to parse thru the logic of your code, I suggest, if your gonna use [blue]nested select statments[/blue] you use nested [blue]If Then ElseIf Else[/blue] statements. Logic flow is more easily discernable this way (at least for me). As a comparsion I've cleaned up your code using [blue]If[/blue] statements. Read you code to follow its logic flow, then read the following using [blue]If[/blue] statements:
Code: 
[blue]On Error GoTo Err_cmdOpenReport_Click
   Dim DL As String
         
   DL = vbNewLine & vbNewLine
   
   [green]'Validation![/green]
   If Not IsDate(Me.txtStartDate) Then
      MsgBox "Please enter a Start Date.", _
             vbOKOnly, _
             "Start Date missing"
      Me.Undo
      Me.txtStartDate.SetFocus
      Exit Sub
   ElseIf Not IsDate(Me.txtEndDate) Then
      MsgBox "Please enter an End Date.", _
             vbOKOnly, _
             "End Date missing"
      Me.txtEndDate.SetFocus
      Exit Sub
   ElseIf Me.txtEndDate < Me.txtStartDate Then
      Call MsgBox("END Date must be greater than START Date!", vbExclamation, "Date check")
      Me.txtStartDate = ""
      Me.txtEndDate = ""
      Me.txtStartDate.SetFocus
      Exit Sub
   ElseIf Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
      MsgBox "The Report Name: '" & Me!Text7 & "' Is in Error!" & DL & _
             "Contact an Administrator ASAP!", _
             vbCritical + vbOKOnly, _
             "Report Name Error! . . ."
      Exit Sub
   End If

   [green]'Code[/green]
   If Weekday(Me.txtWeekCheck) <> vbThursday Then
      If MsgBox("The date you entered is not a Thursday!" & DL & _
                "Do you still wish a report for that date?", _
                vbExclamation + vbYesNo + vbDefaultButton1, _
                "Is Date entered a Thursday?") = vbYes Then
         DoCmd.OpenReport "rptAttendanceWeekly", acViewPreview, , _
                          "[MeetingDate] = Forms!frmDateSelector!txtWeekcheck"
         
         If MsgBox("Do you wish to Preview the Guests for that date?", _
                   vbYesNo Or vbExclamation Or vbDefaultButton1, _
                   Application.Name) = vbYes Then
            DoCmd.OpenReport "rptGuestWithName", _
                             acViewPreview, , _
                             "[GuestDate]=Forms!frmDateSelector!txtWeekCheck"
         Else
            Exit Sub
         End If
      Else
         Me.txtWeekCheck = Null
         Me.txtWeekCheck.SetFocus
         Exit Sub
      End If
   Else
      DoCmd.OpenReport "rptAttendanceWeekly", _
                       acViewPreview, , _
                       "[MeetingDate] = Forms!frmDateSelector!txtWeekcheck"
      If MsgBox("Do you wish to Preview the Guests for that date?", _
                vbYesNo Or vbExclamation Or vbDefaultButton1, _
                Application.Name) = vbYes Then
         DoCmd.OpenReport "rptGuestWithName", _
                          acViewPreview, , _
                          "[GuestDate]=Forms!frmDateSelector!txtWeekCheck"
      Else
         Exit Sub
      End If
   End If[/blue]
If I'm right the [blue]If[/blue] statements will be more [blue]easily and quickly discernable![/blue] Either way the choice is your.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
Thanks for this. I'll give it a go!

Tom
 
Upvote 0 Downvote
The AceMan1

Yep, your method makes for easier reading. I have adjusted mine.

For sake of keeping the problem isolated to the piece of code that wasn't functioning accurately, I didn't post the entire code behind the Preview button.

So here 'tis
Code: 
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim stDocName As String
stDocName = Me.Text7

       Dim DL As String
         
   DL = vbNewLine & vbNewLine
   
   
   'Validation!
   If Not IsDate(Me.txtStartDate) And Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
      MsgBox "Please enter a Start Date.", _
             vbOKOnly, _
             "Start Date missing"
      Me.Undo
      Me.txtStartDate.SetFocus
      Exit Sub
   ElseIf Not IsDate(Me.txtEndDate) And Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
      MsgBox "Please enter an End Date.", _
             vbOKOnly, _
             "End Date missing"
      Me.txtEndDate.SetFocus
      Exit Sub
   ElseIf Me.txtEndDate < Me.txtStartDate And Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
      Call MsgBox("END Date must be greater than START Date!", vbExclamation, "Date check")
      Me.txtStartDate = ""
      Me.txtEndDate = ""
      Me.txtStartDate.SetFocus
      Exit Sub
   End If

Select Case stDocName

Case Is = "Attendance for a SPECIFIC DATE Report"
      If Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
      MsgBox "The Report Name: '" & Me!Text7 & "' Is in Error!" & DL & _
             "Contact an Administrator ASAP!", _
             vbCritical + vbOKOnly, _
             "Report Name Error! . . ."
      Exit Sub
   'End If
   
   
   'Code
   ElseIf Weekday(Me.txtWeekCheck) <> vbThursday Then
      If MsgBox("The date you entered is not a Thursday!" & DL & _
                "Do you still wish a report for that date?", _
                vbExclamation + vbYesNo + vbDefaultButton1, _
                "Is Date entered a Thursday?") = vbYes Then
         DoCmd.OpenReport "rptAttendanceWeekly", acViewPreview, , _
                          "[MeetingDate] = Forms!frmDateSelector!txtWeekcheck"
         
         If MsgBox("Do you wish to Preview the Guests for that date?", _
                   vbYesNo Or vbExclamation Or vbDefaultButton1, _
                   Application.Name) = vbYes Then
            DoCmd.OpenReport "rptGuestWithName", _
                             acViewPreview, , _
                             "[GuestDate]=Forms!frmDateSelector!txtWeekCheck"
         Else
            Exit Sub
         End If
      Else
         Me.txtWeekCheck = Null
         Me.txtWeekCheck.SetFocus
         Exit Sub
      End If
   Else
      DoCmd.OpenReport "rptAttendanceWeekly", _
                       acViewPreview, , _
                       "[MeetingDate] = Forms!frmDateSelector!txtWeekcheck"
      If MsgBox("Do you wish to Preview the Guests for that date?", _
                vbYesNo Or vbExclamation Or vbDefaultButton1, _
                Application.Name) = vbYes Then
         DoCmd.OpenReport "rptGuestWithName", _
                          acViewPreview, , _
                          "[GuestDate]=Forms!frmDateSelector!txtWeekCheck"
      Else
         Exit Sub
      End If
   End If


Case Is = "Attendance for a GIVEN PERIOD OF TIME Report"
    DoCmd.OpenReport "rptAttendanceGivenPeriod", acViewPreview, , "[MeetingDate] Between Forms!frmDateSelector!txtStartDate AND Forms!frmDateSelector!txtEndDate"
        Select Case MsgBox("Do you wish to Preview the Guests for that date?", vbYesNo Or vbExclamation Or vbDefaultButton1, Application.Name)
           
            Case vbYes
           DoCmd.OpenReport "rptGuestWithName", acViewPreview, , "[GuestDate] Between Forms!frmDateSelector!txtStartDate AND Forms!frmDateSelector!txtEndDate"
            Case vbNo
           Exit Sub
           End Select
    
Case Is = "MEMBERS ATTENDANCE TOTALS for any PERIOD OF TIME"
    DoCmd.OpenReport "rptTotalAttendanceForPeriodSelected", acViewPreview, , "[FirstOfMeetingDate] Between Forms!frmDateSelector!txtStartDate AND Forms!frmDateSelector!txtEndDate"
               
Case Is = "MEMBERS ATTENDANCE TOTALS for CURRENT Fiscal Year"
    DoCmd.OpenReport "rptTotalAttendanceForPeriodSelected", acViewPreview, , "[FirstOfMeetingDate] Between Forms!frmDateSelector!txtStartDate AND Forms!frmDateSelector!txtEndDate"
    
Case Is = "MEMBERS ATTENDANCE TOTALS for PREVIOUS Fiscal Year"
    DoCmd.OpenReport "rptTotalAttendanceForPeriodSelected", acViewPreview, , "[FirstOfMeetingDate] Between Forms!frmDateSelector!txtStartDate AND Forms!frmDateSelector!txtEndDate"
    
End Select
    

Exit_cmdOpenReport_Click:
    Exit Sub

Err_cmdOpenReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenReport_Click

End Sub

You will note that I added And Me.Text7 <> "Attendance for a SPECIFIC DATE Report" to your Validation lines in the early pieces of the code.
For example, If Not IsDate(Me.txtStartDate) Then becomes If Not IsDate(Me.txtStartDate) And Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then

Also the code piece
Code: 
 ElseIf Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
      MsgBox "The Report Name: '" & Me!Text7 & "' Is in Error!" & DL & _
             "Contact an Administrator ASAP!", _
             vbCritical + vbOKOnly, _
             "Report Name Error! . . ."
is moved into the Case Is = "Attendance for a SPECIFIC DATE Report" case statement because it doesn't apply to the other 4 case statements.

If you have any other comments, they would be most appreciated.

Thanks!!

Tom

 
Upvote 0 Downvote
THWatson . . .

The code you moved to the [blue]Case Is = "Attendance for a SPECIFIC DATE Report"[/blue] statement ...
Code: 
[blue]Case Is = "Attendance for a SPECIFIC DATE Report"
      [red] If Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
      MsgBox "The Report Name: '" & Me!Text7 & "' Is in Error!" & DL & _
             "Contact an Administrator ASAP!", _
             vbCritical + vbOKOnly, _
             "Report Name Error! . . ."
      Exit Sub[/red]
   'End If[/blue]
... will always fail and is not needed! This is because the case statement has already qualified text7 [blue]via Select Case stDocName, where stDocName = text7.[/blue] So if the case qualifies text7 as true the [blue]If[/blue] statement is looking to qualify text7 false ... and fails ... passing thru. This is bad only in that we have an [blue]If[/blue] statment that will never execute and can therefore be removed. I hope you see this.

Since your select statement revolves around report names, you may want to consider using a final [blue]Case Else[/blue] statement:
Code: 
[blue]   Case Else
      MsgBox "'" & Me!Text7 & "' is not a valid Report Name!" & DL & _
             "Notify an Admin ASAP!", _
             vbCritical + vbOKOnly, _
             "InValid Report Name Error! . . ."[/blue]
At least it traps any name errors here. I don't expect name errors from your code, but it does close the hole!

Thats it! Until next time you take care ... [blue]Ya Hear![/blue] [thumbsup2]



See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
The AceMan1

Terrific!

I was looking at that piece of code earlier
Code: 
 If Me.Text7 <> "Attendance for a SPECIFIC DATE Report" Then
      MsgBox "The Report Name: '" & Me!Text7 & "' Is in Error!" & DL & _
             "Contact an Administrator ASAP!", _
             vbCritical + vbOKOnly, _
             "Report Name Error! . . ."
      Exit Sub
and thinking it was redundant, but I decided to leave it until after church (from which we just got home) to consider further.

Thanks for the heads-up about that...and also the suggestion about the CASE ELSE statement.

I will take care. You too!!

Tom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
634
Kennelbloke
Kennelbloke
TrekBiker
  • Locked
  • Question
Form Data Not Updating
Replies
6
Views
518
dhookom
dhookom
Bubba002
  • Locked
  • Question
Pass a subform fieldname-ID as a string variable in CF expression
2 3
Replies
50
Views
2K
dhookom
dhookom
I
  • Locked
  • Question
How to get 2 codes to work together.
Replies
6
Views
432
hermanlaksko
hermanlaksko
applestoapples
  • Locked
  • Question
Make Table Query sort not working 1
Replies
2
Views
211
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top