Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    2

    Question Unanswered: Need help writing efficient VBA code

    I'm teaching myself Access and VBA, so bear with me. The report has 7 sub-reports associated with it and I am starting to run into performance issues. I was wondering if the VBA code could be critiqued as I'm sure there are performance gains to be had by using more efficient code.

    I’m trying to toggle between subreports and “draw” lines on the applicable subreport using the visible and the can grow properties. This format takes place for each line item in the detail section of the onFormat property in the main report.

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim Tmp As Report
    Set Tmp = Reports![rpt QCOC Sheet thru form]

    'Format Item Comments subreport!

    If IsNull(Tmp![srptItemComment]![ItemComment]) Then
    Tmp![srptItemComment].Visible = 0
    Else
    Tmp![srptItemComment].Visible = -1
    End If

    'Format SPC SPCChartType!

    If Tmp![SpcChartType] = 1 Then
    Tmp![rptSPCCoarse0025].Visible = -1
    Else
    Tmp![rptSPCCoarse0025].Visible = 0
    End If
    If Tmp![SpcChartType] = 2 Then
    Tmp![rptSPCCoarse0035].Visible = -1
    Else
    Tmp![rptSPCCoarse0035].Visible = 0
    End If
    If Tmp![SpcChartType] = 3 Then
    Tmp![rptSPCCoarse0055].Visible = -1
    Else
    Tmp![rptSPCCoarse0055].Visible = 0
    End If
    If Tmp![SpcChartType] = 4 Then
    Tmp![rptSPCFine0015].Visible = -1
    Else
    Tmp![rptSPCFine0015].Visible = 0
    End If
    If Tmp![SpcChartType] = 5 Then
    Tmp![rptSPCFine0025].Visible = -1
    Else
    Tmp![rptSPCFine0025].Visible = 0
    End If


    End Sub

    Thanks for the help,

  2. #2
    Join Date
    Dec 2002
    Posts
    12

    Re: Need help writing efficient VBA code

    Hi,

    I suggest to use Select-Case structure to the latter part of the code, something like this:

    ...
    'Format SPC SPCChartType!

    ' set first all to 0 values
    Tmp![rptSPCCoarse0025].Visible = 0
    Tmp![rptSPCCoarse0035].Visible = 0
    ' and so on
    ...
    ' then if there's any values>0 found then set visible to -1
    select case Tmp![SpcChartType]

    case 1
    Tmp![rptSPCCoarse0025].Visible = -1
    case 2
    Tmp![rptSPCCoarse0035].Visible = -1
    ' and so on
    ...
    end select

    Hope this helps.

    JN

    Originally posted by 02BNATDI
    If IsNull(Tmp![srptItemComment]![ItemComment]) Then
    Tmp![srptItemComment].Visible = 0
    Else
    Tmp![srptItemComment].Visible = -1
    End If

    'Format SPC SPCChartType!

    If Tmp![SpcChartType] = 1 Then
    Tmp![rptSPCCoarse0025].Visible = -1
    Else
    Tmp![rptSPCCoarse0025].Visible = 0
    End If
    If Tmp![SpcChartType] = 2 Then
    Tmp![rptSPCCoarse0035].Visible = -1
    Else
    Tmp![rptSPCCoarse0035].Visible = 0
    End If
    If Tmp![SpcChartType] = 3 Then
    Tmp![rptSPCCoarse0055].Visible = -1
    Else
    Tmp![rptSPCCoarse0055].Visible = 0
    End If
    If Tmp![SpcChartType] = 4 Then
    Tmp![rptSPCFine0015].Visible = -1
    Else
    Tmp![rptSPCFine0015].Visible = 0
    End If
    If Tmp![SpcChartType] = 5 Then
    Tmp![rptSPCFine0025].Visible = -1
    Else
    Tmp![rptSPCFine0025].Visible = 0
    End If


    End Sub

    Thanks for the help,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •