extremely slow form

gwog · Jan 14, 2005

Using Access 97...A consultant built a form that allows user to select year then week. After those selections they click enter and the database copies the required data to a temporary database on the users hard drive. Everything works as it is supposed to.

However, in the recent weeks it has started running the query (to pull the data) extremely slowly. Like upwards of 5-7 minutes to pull 1 week. Where it used to take less than 1 minute.

It is all written in SQL - which I have limited knowledge about. I am including the code below.

I have indexed the table it pulls data from on year and week. But this does not help.

Any suggestions on what could be slowing it down?

__________________________code_________________________

Private Sub cmdExtractData_Click()
On Error GoTo Err_cmdExtractData_Click
Dim SourceFile, DestinationFile
Dim strIsFolder As String
Dim dbsTemp As Database
Dim strTable As String
Dim strConnect As String
Dim strSourceTable As String
Dim strMS As String
Dim tdfLinked As TableDef
Dim rstLinked As Recordset
Dim intTemp As Integer

Dim stDocName As String

DoCmd.SetWarnings False

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

' Delete the Data in the "tblFillRateDetail" table
stDocName = "qdelFillRateDetail"
DoCmd.OpenQuery stDocName, acNormal, acEdit
' Appends the data into the "tblFillRateDetail" based on the
' selected Time Frame
stDocName = "qappFillRateDetailByCriteria"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Me.boxCoverSelectType.Visible = False
Me.txtPreviousOptGrpVal = Me.grpGapTimeFrame
DoCmd.Requery "comboDay"
DoCmd.SetWarnings True

Exit_cmdExtractData_Click:
Exit Sub

Err_cmdExtractData_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdExtractData_Click

End Sub
Private Sub cmdClrCG_Click()
On Error GoTo Err_cmdClrCG_Click

' Sets the initial value of the Customer Group combo box to ""
Me.comboCG.Value = ""

Exit_cmdClrCG_Click:
Exit Sub

Err_cmdClrCG_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdClrCG_Click

End Sub
Private Sub cmdCommentForm_Click()
On Error GoTo Err_cmdCommentForm_Click
' Used to open the Comment form to open
' with specific comment data if available
Dim stDocName As String
Dim stLinkCriteria As String
Dim strMS As String
Dim strCriteria As String
Dim intComment As Integer
Dim intSCCat As Integer
Dim intOverall As Integer

' used to determine the desired time frame
intComment = Me.grpComment.Value

Select Case intComment
Case 1
strCriteria = Me.comboCG.Value
Case 2
strCriteria = Me.comboDC.Value
Case 3
strCriteria = Me.cboChannel.Value
Case 4
strCriteria = Me.ComboBU.Value
Case 5
strCriteria = Me.ComboSCCAT.Value
Case Else
strCriteria = "None"
End Select

Me.txtCriteria.Value = strCriteria

If Me.chkOverallComment = -1 Then
Me.txtOverallVal = "Yes"
Else
Me.txtOverallVal = "No"
End If

If Me.chkSCCatComment = -1 Then
Me.txtSCCatVal = "Yes"
Me.txtSCCatCommCriteria.Value = Me.ComboSCCAT.Value
Else
Me.txtSCCatVal = "No"
Me.txtSCCatCommCriteria = "GENERAL"
End If

' Checks to see if "No Comment" option is selected
' otherwise the comment form is opened
' If intComment = 0 Then
' strMS = MsgBox("No Comment option is selected.", _
' vbInformation, "Comment Error")
' Else
DoCmd.Minimize
stDocName = "frmFRComments"
DoCmd.OpenForm stDocName, , , stLinkCriteria
' End If

Exit_cmdCommentForm_Click:
Exit Sub

Err_cmdCommentForm_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdCommentForm_Click

End Sub

Public Sub DelTempDB()
' Delete the Temporary FillRateTempDB.mdb file

Dim strFile As String
Dim strFolder As String
' Dim strDefaultDir As String

' strDefaultDir = Application.GetOption("Default Database Directory")
' strDefaultDir = "c:"

Me.RecordSource = ""
Me![fsubFillRateTotalShorts_Grouped].Form.RecordSource = ""
Me.comboDay.RowSource = ""
strFile = strDefaultDir & "\" & "FillRateTempDB.mdb"

Kill strFile

End Sub
Private Sub cmdFiscalYrComments_Click()
On Error GoTo Err_cmdFiscalYrComments_Click

Dim stDocName As String

stDocName = "rptCategoryAnalysisCommentsByFiscalYr"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdFiscalYrComments_Click:
Exit Sub

Err_cmdFiscalYrComments_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdFiscalYrComments_Click

End Sub
Private Sub Command174_Click()
On Error GoTo Err_Command174_Click

Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command174_Click:
Exit Sub

Err_Command174_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command174_Click

End Sub
Private Sub cmdClrComboDay_Click()
' Sets the initial value of the Day combo box to ""
Dim strDate As String
Dim intGrpVal As Integer

Me.comboDay.Value = ""
intGrpVal = Me.grpGapTimeFrame
Me.grpGapTimeFrame.Value = Me.txtPreviousOptGrpVal
Me.txtPreviousOptGrpVal = intGrpVal
Select Case Me![grpGapTimeFrame].Value
Case 2
strDate = "For Fiscal Year: " & Me.cmbYear.Value & _
" Week: " & CStr(Me.comboWeek.Value)
Me.txtReportDate = strDate
Case 3
strDate = "For Fiscal Year: " & Me.cmbYear.Value & _
" Period: " & CStr(Me.comboPeriod.Value)
Me.txtReportDate = strDate
Case 4
strDate = "For Fiscal Year: " & Me.cmbYear.Value & _
" Between: " & Format(Me.comboFirstDate.Value, "Short Date") & _
" and " & Format(Me.comboFirstDate.Value, "Short Date")
Me.txtReportDate = strDate
End Select

End Sub
Private Sub cmdFRSummary_100Percent_Click()
On Error GoTo Err_cmdFRSummary_100Percent_Click

' Used to create the Fill Rate Summary report
Dim stDocName As String
Dim strMS As String
Dim strCG As String
Dim strCust As String
Dim strLoc As String
Dim strLOB As String
Dim strSCCat As String
Dim strSupGrp As String
Dim strItem As String
Dim strReason As String
Dim strTitle As String
Dim strCombCrit As String

Dim intCG As Integer
Dim intCGComm As Integer
Dim intCust As Integer
Dim intOverall As Integer
Dim intLoc As Integer
Dim intLocComm As Integer
Dim intChannel As Integer
Dim intChannelComm As Integer
Dim intLOB As Integer
Dim intLOBComm As Integer
Dim intBrand As Integer
Dim intSCCat As Integer
Dim intSCCatComm As Integer
Dim intSupGrp As Integer
Dim intBUCat As Integer
Dim intBUSubCat As Integer
Dim intItem As Integer
Dim intItemReasonCodeRollup As Integer
Dim intItemCutDetailRollup As Integer
Dim intReason As Integer
Dim intReasonCodeItemNoRollup As Integer
Dim intReasonCodeCutDetailRollup As Integer
Dim intPlannerComm As Integer
Dim intTtl As Integer

DoCmd.SetWarnings False
' Requeries the form to accept the new data
Forms!frmfillrateanalysis.Requery

DoCmd.SetWarnings True

DoCmd.Echo False

' Builds the Title for the report based on
' selected criteria
' Also builds a combined Criteria value for
' comparison for comments records
strTitle = "Line Item Fill Rate Summary By: "
strCombCrit = ""

If Me.comboCG.Value <> "" Then
strTitle = strTitle & "Customer Group " & _
Me.comboCG.Value & " - "
strCombCrit = Me.comboCG.Value
End If

If Me.comboCustomer.Value <> "" Then
strTitle = strTitle & "Customer " & _
Me.comboCustomer.Value & " - "
strCombCrit = strCombCrit & Me.comboCustomer.Value
End If

If Me.comboDC.Value <> "" Then
strTitle = strTitle & "Location " & _
Me.comboDC.Value & " - "
strCombCrit = strCombCrit & Me.comboDC.Value
End If

If Me.cboChannel.Value <> "" Then
strTitle = strTitle & "Channel " & _
Me.cboChannel.Value & " - "
strCombCrit = strCombCrit & Me.cboChannel.Value
End If

If Me.ComboBU.Value <> "" Then
strTitle = strTitle & "Line Of Business " & _
Me.ComboBU.Value & " - "
strCombCrit = strCombCrit & Me.ComboBU.Value
End If

If Me.ComboBrand.Value <> "" Then
strTitle = strTitle & "Brand " & _
Me.ComboBrand.Value & " - "
strCombCrit = strCombCrit & Me.ComboBrand.Value
End If

If Me.ComboSCCAT.Value <> "" Then
strTitle = strTitle & "Supply Category " & _
Me.ComboSCCAT.Value & " - "
strCombCrit = strCombCrit & Me.ComboSCCAT.Value
End If

If Me.comboSG.Value <> "" Then
strTitle = strTitle & "Supply Group " & _
Me.comboSG.Value & " - "
strCombCrit = strCombCrit & Me.comboSG.Value
End If

If Me.ComboBUCAT.Value <> "" Then
strTitle = strTitle & "Business Unit Category " & _
Me.ComboBUCAT.Value & " - "
strCombCrit = strCombCrit & Me.ComboBUCAT.Value
End If

If Me.ComboBUSUBCAT.Value <> "" Then
strTitle = strTitle & "Business Unit Sub-Category " & _
Me.ComboBUSUBCAT.Value & " - "
strCombCrit = strCombCrit & Me.ComboBUSUBCAT.Value
End If

If Me.comboItemNo.Value <> "" Then
strTitle = strTitle & "Item No " & _
Me.comboItemNo.Value & " - "
strCombCrit = strCombCrit & Me.comboItemNo.Value
End If

If Me.comboReasonCode.Value <> "" Then
strTitle = strTitle & "Reason Code " & _
Me.comboReasonCode.Value & " - "
strCombCrit = strCombCrit & Me.comboReasonCode.Value
End If

Me.txtReportTitle.Value = strTitle
Me.strCombinedCriteria = strCombCrit

' Assigns values to variables used to determine
' the status of the check boxes
intCG = Me.chkCustGrp.Value
intCGComm = Me.chkCustGrpComm.Value
intCust = Me.chkCust.Value
intOverall = Me.chkOverall.Value
intLoc = Me.chkLocation.Value
intLocComm = Me.chkLocationComm.Value
intChannel = Me.chkChannel.Value
intChannelComm = Me.chkChannelComm.Value
intLOB = Me.chkLOB.Value
intLOBComm = Me.chkLOBComm.Value
intBrand = Me.chkBrand.Value
intSCCat = Me.chkSCCat.Value
intSCCatComm = Me.chkSCCatComm.Value
intSupGrp = Me.chkSupGrp.Value
intBUCat = Me.chkBUCat.Value
intBUSubCat = Me.chkBUSubCat.Value
intItem = Me.chkItemNo.Value
intItemReasonCodeRollup = Me.chkItemReasonCodeRollup
intItemCutDetailRollup = Me.chkItemCutDetailRollup
intReason = Me.chkReason.Value
intReasonCodeItemNoRollup = Me.chkReasonCodeItemNoRollup
intReasonCodeCutDetailRollup = Me.chkReasonCodeCutDetailRollup
intPlannerComm = Me.chkPlannerComm.Value

' Opens the Fill Rate Summary 100% report in design mode
stDocName = "rptFRSummaryLevel_100Percent"
DoCmd.OpenReport stDocName, acViewDesign

' Determines which sub reports to display
' based on the check box status
If intPlannerComm = -1 Then
Reports![rptFRSummaryLevel_100Percent]![CommPlanner].Visible = True
Reports![rptFRSummaryLevel_100Percent]![CommPlanner].SourceObject = "Report.rptCommPlanner"
Else
Reports![rptFRSummaryLevel_100Percent]![CommPlanner].Visible = False
Reports![rptFRSummaryLevel_100Percent]![CommPlanner].SourceObject = ""
End If

If intCG = -1 Then
Reports![rptFRSummaryLevel_100Percent]![CustGrp_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![CustGrp_100Percent].SourceObject = "Report.rptFRCustGrp_100Percent"
Else
Reports![rptFRSummaryLevel_100Percent]![CustGrp_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![CustGrp_100Percent].SourceObject = ""
End If

If intCGComm = -1 Then
Reports![rptFRSummaryLevel_100Percent]![CommCustGrp].Visible = True
Reports![rptFRSummaryLevel_100Percent]![CommCustGrp].SourceObject = "Report.rptCommCustGrp"
Else
Reports![rptFRSummaryLevel_100Percent]![CommCustGrp].Visible = False
Reports![rptFRSummaryLevel_100Percent]![CommCustGrp].SourceObject = ""
End If

If intCust = -1 Then
Reports![rptFRSummaryLevel_100Percent]![Cust_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![Cust_100Percent].SourceObject = "Report.rptFRCust_100Percent"
Else
Reports![rptFRSummaryLevel_100Percent]![Cust_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![Cust_100Percent].SourceObject = ""
End If

If intOverall = -1 Then
Reports![rptFRSummaryLevel_100Percent]![CommOverall].Visible = True
Reports![rptFRSummaryLevel_100Percent]![CommOverall].SourceObject = "Report.rptCommOverallMgmt"
Else
Reports![rptFRSummaryLevel_100Percent]![CommOverall].Visible = False
Reports![rptFRSummaryLevel_100Percent]![CommOverall].SourceObject = ""
End If

If intLoc = -1 Then
Reports![rptFRSummaryLevel_100Percent]![Location_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![Location_100Percent].SourceObject = "Report.rptFRLocation_100Percent"
Else
Reports![rptFRSummaryLevel_100Percent]![Location_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![Location_100Percent].SourceObject = ""
End If

If intLocComm = -1 Then
Reports![rptFRSummaryLevel_100Percent]![CommLocation].Visible = True
Reports![rptFRSummaryLevel_100Percent]![CommLocation].SourceObject = "Report.rptCommLocation"
Else
Reports![rptFRSummaryLevel_100Percent]![CommLocation].Visible = False
Reports![rptFRSummaryLevel_100Percent]![CommLocation].SourceObject = ""
End If

If intChannel = -1 Then
Reports![rptFRSummaryLevel_100Percent]![Channel_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![Channel_100Percent].SourceObject = "Report.rptFRChannel_100Percent"
Else
Reports![rptFRSummaryLevel_100Percent]![Channel_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![Channel_100Percent].SourceObject = ""
End If

If intChannelComm = -1 Then
Reports![rptFRSummaryLevel_100Percent]![CommChannel].Visible = True
Reports![rptFRSummaryLevel_100Percent]![CommChannel].SourceObject = "Report.rptCommChannel"
Else
Reports![rptFRSummaryLevel_100Percent]![CommChannel].Visible = False
Reports![rptFRSummaryLevel_100Percent]![CommChannel].SourceObject = ""
End If

If intLOB = -1 Then
Reports![rptFRSummaryLevel_100Percent]![LOB_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![LOB_100Percent].SourceObject = "Report.rptFRLOB_100Percent"
Else
Reports![rptFRSummaryLevel_100Percent]![LOB_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![LOB_100Percent].SourceObject = ""
End If

If intLOBComm = -1 Then
Reports![rptFRSummaryLevel_100Percent]![CommLOB].Visible = True
Reports![rptFRSummaryLevel_100Percent]![CommLOB].SourceObject = "Report.rptCommLOB"
Else
Reports![rptFRSummaryLevel_100Percent]![CommLOB].Visible = False
Reports![rptFRSummaryLevel_100Percent]![CommLOB].SourceObject = ""
End If

If intBrand = -1 Then
Reports![rptFRSummaryLevel_100Percent]![Brand_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![Brand_100Percent].SourceObject = "Report.rptFRBrand_100Percent"
Else
Reports![rptFRSummaryLevel_100Percent]![Brand_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![Brand_100Percent].SourceObject = ""
End If

If intSCCat = -1 Then
Reports![rptFRSummaryLevel_100Percent]![SCCat_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![SCCat_100Percent].SourceObject = "Report.rptFRSCCat_100Percent"
Else
Reports![rptFRSummaryLevel_100Percent]![SCCat_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![SCCat_100Percent].SourceObject = ""
End If

If intSCCatComm = -1 Then
Reports![rptFRSummaryLevel_100Percent]![CommSuppCat].Visible = True
Reports![rptFRSummaryLevel_100Percent]![CommSuppCat].SourceObject = "Report.rptCommSuppCat"
Else
Reports![rptFRSummaryLevel_100Percent]![CommSuppCat].Visible = False
Reports![rptFRSummaryLevel_100Percent]![CommSuppCat].SourceObject = ""
End If

If intSupGrp = -1 Then
Reports![rptFRSummaryLevel_100Percent]![SuppGrp_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![SuppGrp_100Percent].SourceObject = "Report.rptFRSuppGrp_100Percent"
Else
Reports![rptFRSummaryLevel_100Percent]![SuppGrp_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![SuppGrp_100Percent].SourceObject = ""
End If

If intBUCat = -1 Then
Reports![rptFRSummaryLevel_100Percent]![BUCat_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![BUCat_100Percent].SourceObject = "Report.rptFRBUCat_100Percent"
Else
Reports![rptFRSummaryLevel_100Percent]![BUCat_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![BUCat_100Percent].SourceObject = ""
End If

If intBUSubCat = -1 Then
Reports![rptFRSummaryLevel_100Percent]![BUSubCat_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![BUSubCat_100Percent].SourceObject = "Report.rptFRBUSubCat_100Percent"
Else
Reports![rptFRSummaryLevel_100Percent]![BUSubCat_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![BUSubCat_100Percent].SourceObject = ""
End If

If intItem = -1 And intItemReasonCodeRollup = -1 Then
Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].SourceObject = ""
Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].SourceObject = "Report.rptFRItemReasonCode_100Percent"

ElseIf intItem = -1 Then

Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].SourceObject = "Report.rptFRItem_100Percent"
Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].SourceObject = ""

Else
Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].SourceObject = ""
Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].SourceObject = ""

End If

If intReason = -1 And intReasonCodeItemNoRollup = -1 Then
Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].SourceObject = ""
Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].SourceObject = "Report.rptFRReasonCodeItem_100Percent"

ElseIf intReason = -1 Then

Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].Visible = True
Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].SourceObject = "Report.rptFRReasonCode_100Percent"
Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].SourceObject = ""

Else
Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].SourceObject = ""
Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].Visible = False
Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].SourceObject = ""

End If

' Saves changes, closes and then previews the report
' DoCmd.Save
DoCmd.CLOSE , , acSaveYes
DoCmd.OpenReport stDocName, acViewPreview

' Determins if the "rptFillRateByItemCutDetail" report should be displayed
If intItem = -1 And intItemCutDetailRollup = -1 Then
DoCmd.OpenReport "rptFillRateByItemCutDetail_100Percent", acViewPreview
End If

' Determins if the "rptFillRateByReasonCodeCutDetail" report should be displayed
If intReason = -1 And intReasonCodeCutDetailRollup = -1 Then
DoCmd.OpenReport "rptFillRateByReasonCodeCutDetail", acViewPreview
End If

DoCmd.Echo True

Exit_cmdFRSummary_100Percent_Click:
Exit Sub

Err_cmdFRSummary_100Percent_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdFRSummary_100Percent_Click

End Sub

Private Sub grpSelectType_AfterUpdate()
Select Case Me.grpSelectType.Value
Case 1, 2
Me.boxOFR1.Visible = False
Me.boxOFR2.Visible = False
Me.boxOFR2.Visible = False
Me.boxCommandCover.Visible = False
Me.boxCover.Visible = False
Case 3
Me.boxOFR1.Visible = True
Me.boxOFR2.Visible = True
Me.boxOFR3.Visible = True
Me.boxCommandCover.Visible = False
Me.boxCover.Visible = False
Me.chkLOB.Value = 0
Me.chkBrand.Value = 0
Me.chkSCCatComment.Value = 0
Me.chkSCCat.Value = 0
Me.chkSupGrp.Value = 0
Me.chkBUCat.Value = 0
Me.chkBUSubCat.Value = 0
Me.chkItemNo.Value = 0
Me.chkItemReasonCodeRollup.Value = 0
Me.chkItemCutDetailRollup.Value = 0
Me.chkReason.Value = 0
Me.chkReasonCodeItemNoRollup.Value = 0
Me.chkReasonCodeCutDetailRollup.Value = 0
Me.ComboBU.Value = ""
Me.ComboBrand.Value = ""
Me.ComboSCCAT.Value = ""
Me.comboSG.Value = ""
Me.ComboBUCAT.Value = ""
Me.ComboBUSUBCAT.Value = ""
Me.comboItemNo.Value = ""
Me.comboReasonCode.Value = ""
If Me.grpComment.Value = 4 Then
Me.grpComment.Value = 0
End If
Case Else
Me.boxOFR1.Visible = True
Me.boxOFR2.Visible = True
Me.boxOFR2.Visible = True
Me.boxCommandCover.Visible = True
Me.boxCover.Visible = True
End Select

End Sub

mp9 · Jan 14, 2005

How big is the database? When was it last comapacted and repaired? (Menu path: Tools, Database Utilities...)

[pc2]

http://www22.brinkster.com/accessory/

gwog · Jan 14, 2005

Thanks mp9 for the response.

The backend database is 664,928 kb. The frontend is 149,834.

I just repaired and compacted both of them in the last 2 days. It didn't help the speed at all.

Just for clarification there are also links to other databases in the frontend - but only the two mentioned above are referenced in the form.

Any other suggestions are greatly appreciated.

mp9 · Jan 14, 2005

Well, for what it's worth your code can be slightly streamlined:

Code:

Private Sub cmdExtractData_Click()
On Error GoTo Err_cmdExtractData_Click

  DoCmd.SetWarnings False
    
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    
  ' Delete the Data in the "tblFillRateDetail" table
  DoCmd.OpenQuery "qdelFillRateDetail", acNormal, acEdit
  ' Appends the data into the "tblFillRateDetail" based on the
  ' selected Time Frame
  DoCmd.OpenQuery "qappFillRateDetailByCriteria", acNormal, acEdit
    
  Me.boxCoverSelectType.Visible = False
  Me.txtPreviousOptGrpVal = Me.grpGapTimeFrame
  DoCmd.Requery "comboDay"
  DoCmd.SetWarnings True

Exit_cmdExtractData_Click:
    Exit Sub

Err_cmdExtractData_Click:
    MsgBox Err.Description
    Resume Exit_cmdExtractData_Click
    
End Sub
Private Sub cmdClrCG_Click()
On Error GoTo Err_cmdClrCG_Click

    ' Sets the initial value of the Customer Group combo box to ""
    Me.comboCG.Value = vbNullString

Exit_cmdClrCG_Click:
    Exit Sub

Err_cmdClrCG_Click:
    MsgBox Err.Description
    Resume Exit_cmdClrCG_Click
    
End Sub
Private Sub cmdCommentForm_Click()
On Error GoTo Err_cmdCommentForm_Click
' Used to open the Comment form to open
' with specific comment data if available
    
    ' used to determine the desired time frame
    intComment = Me.grpComment.Value
    
    Select Case intComment
    Case 1
        Me.txtCriteria.Value = Me.comboCG.Value
    Case 2
        Me.txtCriteria.Value = Me.comboDC.Value
    Case 3
        Me.txtCriteria.Value = Me.cboChannel.Value
    Case 4
        Me.txtCriteria.Value = Me.ComboBU.Value
    Case 5
        Me.txtCriteria.Value = Me.ComboSCCAT.Value
    Case Else
        Me.txtCriteria.Value = "None"
    End Select
    
    If Me.chkOverallComment = -1 Then
        Me.txtOverallVal = "Yes"
    Else
        Me.txtOverallVal = "No"
    End If
    
    If Me.chkSCCatComment = -1 Then
        Me.txtSCCatVal = "Yes"
        Me.txtSCCatCommCriteria.Value = Me.ComboSCCAT.Value
    Else
        Me.txtSCCatVal = "No"
        Me.txtSCCatCommCriteria = "GENERAL"
    End If
    

    ' Checks to see if "No Comment" option is selected
    ' otherwise the comment form is opened
'    If intComment = 0 Then
'        strMS = MsgBox("No Comment option is selected.", _
'            vbInformation, "Comment Error")
'    Else
        DoCmd.Minimize
        DoCmd.OpenForm "frmFRComments"
'    End If
    
Exit_cmdCommentForm_Click:
    Exit Sub

Err_cmdCommentForm_Click:
    MsgBox Err.Description
    Resume Exit_cmdCommentForm_Click
    
End Sub

Public Sub DelTempDB()
  ' Delete the Temporary FillRateTempDB.mdb file
  
  Dim strFile As String
  Dim strFolder As String
'  Dim strDefaultDir As String
  
'  strDefaultDir = Application.GetOption("Default Database Directory")
'  strDefaultDir = "c:"
  
  Me.RecordSource = vbNullString
  Me![fsubFillRateTotalShorts_Grouped].Form.RecordSource = vbNullString
  Me.comboDay.RowSource = vbNullString
  strFile = strDefaultDir & "\" & "FillRateTempDB.mdb"

  Kill strFile

End Sub
Private Sub cmdFiscalYrComments_Click()
On Error GoTo Err_cmdFiscalYrComments_Click

    DoCmd.OpenReport "rptCategoryAnalysisCommentsByFiscalYr", acPreview

Exit_cmdFiscalYrComments_Click:
    Exit Sub

Err_cmdFiscalYrComments_Click:
    MsgBox Err.Description
    Resume Exit_cmdFiscalYrComments_Click
    
End Sub
Private Sub Command174_Click()
On Error GoTo Err_Command174_Click

    Screen.PreviousControl.SetFocus
    DoCmd.FindNext

Exit_Command174_Click:
    Exit Sub

Err_Command174_Click:
    MsgBox Err.Description
    Resume Exit_Command174_Click
    
End Sub
Private Sub cmdClrComboDay_Click()
    ' Sets the initial value of the Day combo box to ""
  Dim strDate As String
  Dim intGrpVal As Integer
  
  Me.comboDay.Value = vbNullString
  intGrpVal = Me.grpGapTimeFrame
  Me.grpGapTimeFrame.Value = Me.txtPreviousOptGrpVal
  Me.txtPreviousOptGrpVal = intGrpVal
  Select Case Me![grpGapTimeFrame].Value
  Case 2
      strDate = "For Fiscal Year: " & Me.cmbYear.Value & _
            "  Week: " & CStr(Me.comboWeek.Value)
  Case 3
      strDate = "For Fiscal Year: " & Me.cmbYear.Value & _
            "  Period: " & CStr(Me.comboPeriod.Value)
  Case 4
    strDate = "For Fiscal Year: " & Me.cmbYear.Value & _
        "  Between: " & Format(Me.comboFirstDate.Value, "Short Date") & _
        " and " & Format(Me.comboFirstDate.Value, "Short Date")
  End Select
  Me.txtReportDate = strDate
    
End Sub
Private Sub cmdFRSummary_100Percent_Click()
On Error GoTo Err_cmdFRSummary_100Percent_Click

' Used to create the Fill Rate Summary report
    Dim strMS As String
    Dim strCG As String
    Dim strCust As String
    Dim strLoc As String
    Dim strLOB As String
    Dim strSCCat As String
    Dim strSupGrp As String
    Dim strItem As String
    Dim strReason As String
    Dim strTitle As String
    Dim strCombCrit As String
    
    Dim intCG As Integer
    Dim intCGComm As Integer
    Dim intCust As Integer
    Dim intOverall As Integer
    Dim intLoc As Integer
    Dim intLocComm As Integer
    Dim intChannel As Integer
    Dim intChannelComm As Integer
    Dim intLOB As Integer
    Dim intLOBComm As Integer
    Dim intBrand As Integer
    Dim intSCCat As Integer
    Dim intSCCatComm As Integer
    Dim intSupGrp As Integer
    Dim intBUCat As Integer
    Dim intBUSubCat As Integer
    Dim intItem As Integer
    Dim intItemReasonCodeRollup As Integer
    Dim intItemCutDetailRollup As Integer
    Dim intReason As Integer
    Dim intReasonCodeItemNoRollup As Integer
    Dim intReasonCodeCutDetailRollup As Integer
    Dim intPlannerComm As Integer
    Dim intTtl As Integer
  
    DoCmd.SetWarnings False
    ' Requeries the form to accept the new data
    Forms!frmfillrateanalysis.Requery

    DoCmd.SetWarnings True

    DoCmd.Echo False
        
    ' Builds the Title for the report based on
    ' selected criteria
    ' Also builds a combined Criteria value for
    ' comparison for comments records
    strTitle = "Line Item Fill Rate Summary By: "
    strCombCrit = ""
    
    If Me.comboCG.Value <> "" Then
        strTitle = strTitle & "Customer Group " & _
            Me.comboCG.Value & " - "
        strCombCrit = Me.comboCG.Value
    End If
    
    If Me.comboCustomer.Value <> "" Then
        strTitle = strTitle & "Customer " & _
            Me.comboCustomer.Value & " - "
        strCombCrit = strCombCrit & Me.comboCustomer.Value
    End If
    
    If Me.comboDC.Value <> "" Then
        strTitle = strTitle & "Location " & _
            Me.comboDC.Value & " - "
        strCombCrit = strCombCrit & Me.comboDC.Value
    End If
    
    If Me.cboChannel.Value <> "" Then
        strTitle = strTitle & "Channel " & _
            Me.cboChannel.Value & " - "
        strCombCrit = strCombCrit & Me.cboChannel.Value
    End If
    
    If Me.ComboBU.Value <> "" Then
        strTitle = strTitle & "Line Of Business " & _
            Me.ComboBU.Value & " - "
        strCombCrit = strCombCrit & Me.ComboBU.Value
    End If
    
    If Me.ComboBrand.Value <> "" Then
        strTitle = strTitle & "Brand " & _
            Me.ComboBrand.Value & " - "
        strCombCrit = strCombCrit & Me.ComboBrand.Value
    End If
    
    If Me.ComboSCCAT.Value <> "" Then
        strTitle = strTitle & "Supply Category " & _
            Me.ComboSCCAT.Value & " - "
        strCombCrit = strCombCrit & Me.ComboSCCAT.Value
    End If
    
    If Me.comboSG.Value <> "" Then
        strTitle = strTitle & "Supply Group " & _
            Me.comboSG.Value & " - "
        strCombCrit = strCombCrit & Me.comboSG.Value
    End If
    
    If Me.ComboBUCAT.Value <> "" Then
        strTitle = strTitle & "Business Unit Category " & _
            Me.ComboBUCAT.Value & " - "
        strCombCrit = strCombCrit & Me.ComboBUCAT.Value
    End If
    
    If Me.ComboBUSUBCAT.Value <> "" Then
        strTitle = strTitle & "Business Unit Sub-Category " & _
            Me.ComboBUSUBCAT.Value & " - "
        strCombCrit = strCombCrit & Me.ComboBUSUBCAT.Value
    End If
    
    If Me.comboItemNo.Value <> "" Then
        strTitle = strTitle & "Item No " & _
            Me.comboItemNo.Value & " - "
        strCombCrit = strCombCrit & Me.comboItemNo.Value
    End If
    
    If Me.comboReasonCode.Value <> "" Then
        strTitle = strTitle & "Reason Code " & _
            Me.comboReasonCode.Value & " - "
        strCombCrit = strCombCrit & Me.comboReasonCode.Value
    End If

    Me.txtReportTitle.Value = strTitle
    Me.strCombinedCriteria = strCombCrit
    
    ' Assigns values to variables used to determine
    ' the status of the check boxes
    intCG = Me.chkCustGrp.Value
    intCGComm = Me.chkCustGrpComm.Value
    intCust = Me.chkCust.Value
    intOverall = Me.chkOverall.Value
    intLoc = Me.chkLocation.Value
    intLocComm = Me.chkLocationComm.Value
    intChannel = Me.chkChannel.Value
    intChannelComm = Me.chkChannelComm.Value
    intLOB = Me.chkLOB.Value
    intLOBComm = Me.chkLOBComm.Value
    intBrand = Me.chkBrand.Value
    intSCCat = Me.chkSCCat.Value
    intSCCatComm = Me.chkSCCatComm.Value
    intSupGrp = Me.chkSupGrp.Value
    intBUCat = Me.chkBUCat.Value
    intBUSubCat = Me.chkBUSubCat.Value
    intItem = Me.chkItemNo.Value
    intItemReasonCodeRollup = Me.chkItemReasonCodeRollup
    intItemCutDetailRollup = Me.chkItemCutDetailRollup
    intReason = Me.chkReason.Value
    intReasonCodeItemNoRollup = Me.chkReasonCodeItemNoRollup
    intReasonCodeCutDetailRollup = Me.chkReasonCodeCutDetailRollup
    intPlannerComm = Me.chkPlannerComm.Value
    
    
    ' Opens the Fill Rate Summary 100% report in design mode
    DoCmd.OpenReport "rptFRSummaryLevel_100Percent", acViewDesign
    
    ' Determines which sub reports to display
    ' based on the check box status
    If intPlannerComm = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![CommPlanner].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![CommPlanner].SourceObject = "Report.rptCommPlanner"
    Else
        Reports![rptFRSummaryLevel_100Percent]![CommPlanner].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![CommPlanner].SourceObject = vbNullString
    End If
    
    
    If intCG = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![CustGrp_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![CustGrp_100Percent].SourceObject = "Report.rptFRCustGrp_100Percent"
    Else
        Reports![rptFRSummaryLevel_100Percent]![CustGrp_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![CustGrp_100Percent].SourceObject = vbNullString
    End If
    
    If intCGComm = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![CommCustGrp].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![CommCustGrp].SourceObject = "Report.rptCommCustGrp"
    Else
        Reports![rptFRSummaryLevel_100Percent]![CommCustGrp].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![CommCustGrp].SourceObject = vbNullString
    End If
    
    If intCust = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![Cust_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![Cust_100Percent].SourceObject = "Report.rptFRCust_100Percent"
    Else
        Reports![rptFRSummaryLevel_100Percent]![Cust_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![Cust_100Percent].SourceObject = vbNullString
    End If

    If intOverall = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![CommOverall].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![CommOverall].SourceObject = "Report.rptCommOverallMgmt"
    Else
        Reports![rptFRSummaryLevel_100Percent]![CommOverall].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![CommOverall].SourceObject = vbNullString
    End If

    If intLoc = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![Location_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![Location_100Percent].SourceObject = "Report.rptFRLocation_100Percent"
    Else
        Reports![rptFRSummaryLevel_100Percent]![Location_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![Location_100Percent].SourceObject = vbNullString
    End If

    If intLocComm = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![CommLocation].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![CommLocation].SourceObject = "Report.rptCommLocation"
    Else
        Reports![rptFRSummaryLevel_100Percent]![CommLocation].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![CommLocation].SourceObject = vbNullString
    End If

    If intChannel = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![Channel_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![Channel_100Percent].SourceObject = "Report.rptFRChannel_100Percent"
    Else
        Reports![rptFRSummaryLevel_100Percent]![Channel_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![Channel_100Percent].SourceObject = vbNullString
    End If

    If intChannelComm = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![CommChannel].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![CommChannel].SourceObject = "Report.rptCommChannel"
    Else
        Reports![rptFRSummaryLevel_100Percent]![CommChannel].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![CommChannel].SourceObject = vbNullString
    End If

    If intLOB = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![LOB_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![LOB_100Percent].SourceObject = "Report.rptFRLOB_100Percent"
    Else
        Reports![rptFRSummaryLevel_100Percent]![LOB_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![LOB_100Percent].SourceObject = vbNullString
    End If

    If intLOBComm = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![CommLOB].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![CommLOB].SourceObject = "Report.rptCommLOB"
    Else
        Reports![rptFRSummaryLevel_100Percent]![CommLOB].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![CommLOB].SourceObject = vbNullString
    End If

    If intBrand = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![Brand_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![Brand_100Percent].SourceObject = "Report.rptFRBrand_100Percent"
    Else
        Reports![rptFRSummaryLevel_100Percent]![Brand_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![Brand_100Percent].SourceObject = vbNullString
    End If

    If intSCCat = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![SCCat_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![SCCat_100Percent].SourceObject = "Report.rptFRSCCat_100Percent"
    Else
        Reports![rptFRSummaryLevel_100Percent]![SCCat_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![SCCat_100Percent].SourceObject = vbNullString
    End If

    If intSCCatComm = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![CommSuppCat].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![CommSuppCat].SourceObject = "Report.rptCommSuppCat"
    Else
        Reports![rptFRSummaryLevel_100Percent]![CommSuppCat].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![CommSuppCat].SourceObject = vbNullString
    End If
    
    If intSupGrp = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![SuppGrp_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![SuppGrp_100Percent].SourceObject = "Report.rptFRSuppGrp_100Percent"
    Else
        Reports![rptFRSummaryLevel_100Percent]![SuppGrp_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![SuppGrp_100Percent].SourceObject = vbNullString
    End If

    If intBUCat = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![BUCat_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![BUCat_100Percent].SourceObject = "Report.rptFRBUCat_100Percent"
    Else
        Reports![rptFRSummaryLevel_100Percent]![BUCat_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![BUCat_100Percent].SourceObject = vbNullString
    End If

    If intBUSubCat = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![BUSubCat_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![BUSubCat_100Percent].SourceObject = "Report.rptFRBUSubCat_100Percent"
    Else
        Reports![rptFRSummaryLevel_100Percent]![BUSubCat_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![BUSubCat_100Percent].SourceObject = vbNullString
    End If

    If intItem = -1 And intItemReasonCodeRollup = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].SourceObject = vbNullString
        Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].SourceObject = "Report.rptFRItemReasonCode_100Percent"
    
    ElseIf intItem = -1 Then
    
        Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].SourceObject = "Report.rptFRItem_100Percent"
        Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].SourceObject = vbNullString
    
    Else
        Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![Item_100Percent].SourceObject = ""
        Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![ItemReasonCode_100Percent].SourceObject = vbNullString
    
    End If
        
    If intReason = -1 And intReasonCodeItemNoRollup = -1 Then
        Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].SourceObject = vbNullString
        Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].SourceObject = "Report.rptFRReasonCodeItem_100Percent"
        
    ElseIf intReason = -1 Then
        
        Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].Visible = True
        Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].SourceObject = "Report.rptFRReasonCode_100Percent"
        Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].SourceObject = vbNullString
        
    Else
        Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![ReasonCode_100Percent].SourceObject = vbNullString
        Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].Visible = False
        Reports![rptFRSummaryLevel_100Percent]![ReasonCodeItem_100Percent].SourceObject = vbNullString
        
    End If
    
    
    ' Saves changes, closes and then previews the report
'    DoCmd.Save
    DoCmd.Close , , acSaveYes
    DoCmd.OpenReport "rptFRSummaryLevel_100Percent", acViewPreview

    ' Determins if the "rptFillRateByItemCutDetail" report should be displayed
    If intItem = -1 And intItemCutDetailRollup = -1 Then
        DoCmd.OpenReport "rptFillRateByItemCutDetail_100Percent", acViewPreview
    End If
        
    ' Determins if the "rptFillRateByReasonCodeCutDetail" report should be displayed
    If intReason = -1 And intReasonCodeCutDetailRollup = -1 Then
        DoCmd.OpenReport "rptFillRateByReasonCodeCutDetail", acViewPreview
    End If

    DoCmd.Echo True

Exit_cmdFRSummary_100Percent_Click:
    Exit Sub

Err_cmdFRSummary_100Percent_Click:
    MsgBox Err.Description
    Resume Exit_cmdFRSummary_100Percent_Click
    
End Sub

Private Sub grpSelectType_AfterUpdate()
  Select Case Me.grpSelectType.Value
    Case 1, 2
      Me.boxOFR1.Visible = False
      Me.boxOFR2.Visible = False
      Me.boxOFR2.Visible = False
      Me.boxCommandCover.Visible = False
      Me.boxCover.Visible = False
    Case 3
      Me.boxOFR1.Visible = True
      Me.boxOFR2.Visible = True
      Me.boxOFR3.Visible = True
      Me.boxCommandCover.Visible = False
      Me.boxCover.Visible = False
      Me.chkLOB.Value = 0
      Me.chkBrand.Value = 0
      Me.chkSCCatComment.Value = 0
      Me.chkSCCat.Value = 0
      Me.chkSupGrp.Value = 0
      Me.chkBUCat.Value = 0
      Me.chkBUSubCat.Value = 0
      Me.chkItemNo.Value = 0
      Me.chkItemReasonCodeRollup.Value = 0
      Me.chkItemCutDetailRollup.Value = 0
      Me.chkReason.Value = 0
      Me.chkReasonCodeItemNoRollup.Value = 0
      Me.chkReasonCodeCutDetailRollup.Value = 0
      Me.ComboBU.Value = ""
      Me.ComboBrand.Value = ""
      Me.ComboSCCAT.Value = ""
      Me.comboSG.Value = ""
      Me.ComboBUCAT.Value = ""
      Me.ComboBUSUBCAT.Value = ""
      Me.comboItemNo.Value = ""
      Me.comboReasonCode.Value = ""
      If Me.grpComment.Value = 4 Then
        Me.grpComment.Value = 0
      End If
    Case Else
      Me.boxOFR1.Visible = True
      Me.boxOFR2.Visible = True
      Me.boxOFR2.Visible = True
      Me.boxCommandCover.Visible = True
      Me.boxCover.Visible = True
  End Select

End Sub

http://www22.brinkster.com/accessory/

KerryL · Jan 14, 2005

Any chance that it's running slower because each file being copied to the hard drive is being scanned by anti-virus software that automatically scans every file that's opened or manipulated on the PC?

Just a thought.

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!

extremely slow form

gwog

Technical User

mp9

Programmer

gwog

Technical User

mp9

Programmer

KerryL

Technical User

Similar threads

Part and Inventory Search

Sponsor