Access 200 VB question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a script attached to a form that uses an Input Box to obtain a value
from the user, then opens a query-based report based on the value. The
problem occurs when the input box function is cancelled, then the program
'freezes up', or seems to get hung up in the code.

Excerpt from script:
Private Sub SelectReport_Click()

On Error GoTo Err_Select_Report

Dim dbs As Database, rpt As Report, strSQL As String, strMsg As String, rst
As Recordset, strSite As String, strYear As String
Application.Echo False
Set dbs = CurrentDb
strSite = [Forms]![Master Site Form]![SITE]
strMsg = "Enter 4-digit year for Site " & [Forms]![Master Site Form]![SITE]
strYear = InputBox(Prompt:=strMsg, Title:="Print Archive Report")
If strYear = "" Then
DoCmd.CancelEvent
GoTo Exit_SelectReport_Click:

Else


If strYear = Year(DATE) Then
strSQL = "SELECT CYManualArch.Site, CYManualArch.Initials,
CYManualArch.Direction, CYManualArch.BH, CYManualArch.CDate,
CYManualArch.APass, CYManualArch.ATWT, CYManualArch.A3axplus,
CYManualArch.A2ax, CYManualArch.ABus, CYManualArch.[4+AXSU],
CYManualArch.T3ax, CYManualArch.T4ax, CYManualArch.T5axgrain,
CYManualArch.T5axstlo, CYManualArch.T5axstun, " & _
"CYManualArch.T5axother, CYManualArch.T5axdump,
CYManualArch.T5axtank, CYManualArch.T5axplus, CYManualArch.T6axplus,
CYManualArch.Count FROM CYManualArch WHERE Site" & "=" & strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "ManualArch04", acViewDesign
[Reports]![ManualArch04].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
strSQL = "SELECT CYTubeArch.Site, CYTubeArch.Direction,
CYTubeArch.Date, CYTubeArch.Time, CYTubeArch.Bike, CYTubeArch.Car,
CYTubeArch.Pickup, CYTubeArch.[Bus&HTWT], CYTubeArch.[2axsu],
CYTubeArch.[3axsu], CYTubeArch.[4+axsu], CYTubeArch.[3&4semi],
CYTubeArch.[5axsemi], CYTubeArch.[6+AXSEMI], CYTubeArch.[Twins1],
CYTubeArch.[Twins2], CYTubeArch.Twins3, CYTubeArch.Other, CYTubeArch.Count
FROM CYTubeArch WHERE Site" & "=" & strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "TubeArch03", acViewDesign
[Reports]![TubeArch03].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
MsgBox "No count found for this site in" &
Year(DATE) & "tables! Check Vehicle Class history", vbOKOnly
End If
End If
Else
Select Case strYear
Case 2007
strSQL = "SELECT Arch07.Site, Arch07.Initials,
Arch07.Direction, Arch07.BH, Arch07.CDate, Arch07.APass, Arch07.ATWT,
Arch07.A3axplus, Arch07.A2ax, Arch07.ABus, Arch07.AHTWTtank,
Arch07.A3axplustank, Arch07.A2axtank, Arch07.T3ax, Arch07.T3axtank,
Arch07.T4ax, Arch07.T4axtank, Arch07.T5axgrain, Arch07.T5axstlo,
Arch07.T5axstun, Arch07.T5axother, Arch07.T5axdump, Arch07.T5axtank,
Arch07.T5axplus, Arch07.T6axplus, Arch07.Count FROM Arch07 WHERE Site" & "="
& strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "ManualArch04", acViewDesign
[Reports]![ManualArch04].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
strSQL = "SELECT Tube2007.Site, Tube2007.Direction,
Tube2007.Date, Tube2007.Time, Tube2007.Bike, Tube2007.Car, Tube2007.Pickup,
Tube2007.[Bus&HTWT], Tube2007.[2axsu], Tube2007.[3axsu], Tube2007.[4+axsu],
Tube2007.[3&4semi], Tube2007.[5axsemi], Tube2007.[6+axsemi],
Tube2007.[Twins1], Tube2007.[Twins2], Tube2007.Twins3, Tube2007.Other,
Tube2007.Count FROM Tube2007 WHERE Site" & "=" & strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "TubeArch03", acViewDesign
[Reports]![TubeArch03].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
MsgBox "No count found for this site in 2007
tables! Check Vehicle Class history", vbOKOnly
End If
End If
.....(more not included)
Exit_SelectReport_Click:
Exit Sub

Err_Select_Report:
MsgBox Err.DESCRIPTION
Resume Exit_SelectReport_Click

End Sub
 
Where is it hanging? At the check for = "", or at the DoCmd.CancelEvent? Is
there any reason for the CancelEvent?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Melissa@MnDOT said:
I have a script attached to a form that uses an Input Box to obtain a value
from the user, then opens a query-based report based on the value. The
problem occurs when the input box function is cancelled, then the program
'freezes up', or seems to get hung up in the code.

Excerpt from script:
Private Sub SelectReport_Click()

On Error GoTo Err_Select_Report

Dim dbs As Database, rpt As Report, strSQL As String, strMsg As String, rst
As Recordset, strSite As String, strYear As String
Application.Echo False
Set dbs = CurrentDb
strSite = [Forms]![Master Site Form]![SITE]
strMsg = "Enter 4-digit year for Site " & [Forms]![Master Site Form]![SITE]
strYear = InputBox(Prompt:=strMsg, Title:="Print Archive Report")
If strYear = "" Then
DoCmd.CancelEvent
GoTo Exit_SelectReport_Click:

Else


If strYear = Year(DATE) Then
strSQL = "SELECT CYManualArch.Site, CYManualArch.Initials,
CYManualArch.Direction, CYManualArch.BH, CYManualArch.CDate,
CYManualArch.APass, CYManualArch.ATWT, CYManualArch.A3axplus,
CYManualArch.A2ax, CYManualArch.ABus, CYManualArch.[4+AXSU],
CYManualArch.T3ax, CYManualArch.T4ax, CYManualArch.T5axgrain,
CYManualArch.T5axstlo, CYManualArch.T5axstun, " & _
"CYManualArch.T5axother, CYManualArch.T5axdump,
CYManualArch.T5axtank, CYManualArch.T5axplus, CYManualArch.T6axplus,
CYManualArch.Count FROM CYManualArch WHERE Site" & "=" & strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "ManualArch04", acViewDesign
[Reports]![ManualArch04].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
strSQL = "SELECT CYTubeArch.Site, CYTubeArch.Direction,
CYTubeArch.Date, CYTubeArch.Time, CYTubeArch.Bike, CYTubeArch.Car,
CYTubeArch.Pickup, CYTubeArch.[Bus&HTWT], CYTubeArch.[2axsu],
CYTubeArch.[3axsu], CYTubeArch.[4+axsu], CYTubeArch.[3&4semi],
CYTubeArch.[5axsemi], CYTubeArch.[6+AXSEMI], CYTubeArch.[Twins1],
CYTubeArch.[Twins2], CYTubeArch.Twins3, CYTubeArch.Other, CYTubeArch.Count
FROM CYTubeArch WHERE Site" & "=" & strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "TubeArch03", acViewDesign
[Reports]![TubeArch03].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
MsgBox "No count found for this site in" &
Year(DATE) & "tables! Check Vehicle Class history", vbOKOnly
End If
End If
Else
Select Case strYear
Case 2007
strSQL = "SELECT Arch07.Site, Arch07.Initials,
Arch07.Direction, Arch07.BH, Arch07.CDate, Arch07.APass, Arch07.ATWT,
Arch07.A3axplus, Arch07.A2ax, Arch07.ABus, Arch07.AHTWTtank,
Arch07.A3axplustank, Arch07.A2axtank, Arch07.T3ax, Arch07.T3axtank,
Arch07.T4ax, Arch07.T4axtank, Arch07.T5axgrain, Arch07.T5axstlo,
Arch07.T5axstun, Arch07.T5axother, Arch07.T5axdump, Arch07.T5axtank,
Arch07.T5axplus, Arch07.T6axplus, Arch07.Count FROM Arch07 WHERE Site" & "="
& strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "ManualArch04", acViewDesign
[Reports]![ManualArch04].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
strSQL = "SELECT Tube2007.Site, Tube2007.Direction,
Tube2007.Date, Tube2007.Time, Tube2007.Bike, Tube2007.Car, Tube2007.Pickup,
Tube2007.[Bus&HTWT], Tube2007.[2axsu], Tube2007.[3axsu], Tube2007.[4+axsu],
Tube2007.[3&4semi], Tube2007.[5axsemi], Tube2007.[6+axsemi],
Tube2007.[Twins1], Tube2007.[Twins2], Tube2007.Twins3, Tube2007.Other,
Tube2007.Count FROM Tube2007 WHERE Site" & "=" & strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "TubeArch03", acViewDesign
[Reports]![TubeArch03].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
MsgBox "No count found for this site in 2007
tables! Check Vehicle Class history", vbOKOnly
End If
End If
....(more not included)
Exit_SelectReport_Click:
Exit Sub

Err_Select_Report:
MsgBox Err.DESCRIPTION
Resume Exit_SelectReport_Click

End Sub
Click to expand...
 
Try troubleshooting by commenting out the line

'Application.Echo False

Since I don't see anything in your error trapping code that would turn echo back
on, you could be generating an error in your code and then hitting your error
handler and never seeing the error message and therefore never be able to click
on the dialog to continue processing.

At a minimum, add

Application.Echo True

as the first line of your error handling code.

Now try running your code and see what errors you get (if any) and then post
back with more information on the error (and if possible the line that is
causing the error)

Melissa@MnDOT said:
I have a script attached to a form that uses an Input Box to obtain a value
from the user, then opens a query-based report based on the value. The
problem occurs when the input box function is cancelled, then the program
'freezes up', or seems to get hung up in the code.

Excerpt from script:
Private Sub SelectReport_Click()

On Error GoTo Err_Select_Report

Dim dbs As Database, rpt As Report, strSQL As String, strMsg As String, rst
As Recordset, strSite As String, strYear As String
Application.Echo False
Set dbs = CurrentDb
strSite = [Forms]![Master Site Form]![SITE]
strMsg = "Enter 4-digit year for Site " & [Forms]![Master Site Form]![SITE]
strYear = InputBox(Prompt:=strMsg, Title:="Print Archive Report")
If strYear = "" Then
DoCmd.CancelEvent
GoTo Exit_SelectReport_Click:

Else


If strYear = Year(DATE) Then
strSQL = "SELECT CYManualArch.Site, CYManualArch.Initials,
CYManualArch.Direction, CYManualArch.BH, CYManualArch.CDate,
CYManualArch.APass, CYManualArch.ATWT, CYManualArch.A3axplus,
CYManualArch.A2ax, CYManualArch.ABus, CYManualArch.[4+AXSU],
CYManualArch.T3ax, CYManualArch.T4ax, CYManualArch.T5axgrain,
CYManualArch.T5axstlo, CYManualArch.T5axstun, " & _
"CYManualArch.T5axother, CYManualArch.T5axdump,
CYManualArch.T5axtank, CYManualArch.T5axplus, CYManualArch.T6axplus,
CYManualArch.Count FROM CYManualArch WHERE Site" & "=" & strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "ManualArch04", acViewDesign
[Reports]![ManualArch04].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
strSQL = "SELECT CYTubeArch.Site, CYTubeArch.Direction,
CYTubeArch.Date, CYTubeArch.Time, CYTubeArch.Bike, CYTubeArch.Car,
CYTubeArch.Pickup, CYTubeArch.[Bus&HTWT], CYTubeArch.[2axsu],
CYTubeArch.[3axsu], CYTubeArch.[4+axsu], CYTubeArch.[3&4semi],
CYTubeArch.[5axsemi], CYTubeArch.[6+AXSEMI], CYTubeArch.[Twins1],
CYTubeArch.[Twins2], CYTubeArch.Twins3, CYTubeArch.Other, CYTubeArch.Count
FROM CYTubeArch WHERE Site" & "=" & strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "TubeArch03", acViewDesign
[Reports]![TubeArch03].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
MsgBox "No count found for this site in" &
Year(DATE) & "tables! Check Vehicle Class history", vbOKOnly
End If
End If
Else
Select Case strYear
Case 2007
strSQL = "SELECT Arch07.Site, Arch07.Initials,
Arch07.Direction, Arch07.BH, Arch07.CDate, Arch07.APass, Arch07.ATWT,
Arch07.A3axplus, Arch07.A2ax, Arch07.ABus, Arch07.AHTWTtank,
Arch07.A3axplustank, Arch07.A2axtank, Arch07.T3ax, Arch07.T3axtank,
Arch07.T4ax, Arch07.T4axtank, Arch07.T5axgrain, Arch07.T5axstlo,
Arch07.T5axstun, Arch07.T5axother, Arch07.T5axdump, Arch07.T5axtank,
Arch07.T5axplus, Arch07.T6axplus, Arch07.Count FROM Arch07 WHERE Site" & "="
& strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "ManualArch04", acViewDesign
[Reports]![ManualArch04].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
strSQL = "SELECT Tube2007.Site, Tube2007.Direction,
Tube2007.Date, Tube2007.Time, Tube2007.Bike, Tube2007.Car, Tube2007.Pickup,
Tube2007.[Bus&HTWT], Tube2007.[2axsu], Tube2007.[3axsu], Tube2007.[4+axsu],
Tube2007.[3&4semi], Tube2007.[5axsemi], Tube2007.[6+axsemi],
Tube2007.[Twins1], Tube2007.[Twins2], Tube2007.Twins3, Tube2007.Other,
Tube2007.Count FROM Tube2007 WHERE Site" & "=" & strSite & ";"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount > 0 Then
DoCmd.OpenReport "TubeArch03", acViewDesign
[Reports]![TubeArch03].RecordSource = strSQL
DoCmd.RunCommand acCmdPrintPreview
DoCmd.OpenForm "ReportOptions", acNormal
Else
MsgBox "No count found for this site in 2007
tables! Check Vehicle Class history", vbOKOnly
End If
End If
....(more not included)
Exit_SelectReport_Click:
Exit Sub

Err_Select_Report:
MsgBox Err.DESCRIPTION
Resume Exit_SelectReport_Click

End Sub
Click to expand...
 
Back
Top