Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    code to display read-only data on a pop-up form (2

    The following 2 pieces of code behind an option group do the same thing. What I am interested in is which is either faster or better or more efficient.

    PIECE 1
    <code>Dim sql As String
    Dim stDocName As String
    Dim strWHERE As String
    Dim strORDER As String
    Dim strSEL As String
    Dim strUNION As String

    stDocName = "frmDisplayBoth"
    strSEL = "SELECT ProjectID,Category,Item AS [Item / Rate],ItemType,Quantity,ProductCode,Format(ItemCost,'cu rrency') AS [Cost / Unit],Invoice FROM qryMaterialsForUnion "
    strWHERE = "WHERE [ProjectID] = Forms!frmCustomers!fsubProjects.Form![ProjectID] "
    strUNION = "UNION SELECT ProjectID,WorkDate,Format(LabourRate,'currency'),' ',Hours,'','',Invoice FROM qryLabourForUnion "
    strORDER = "ORDER BY Invoice DESC , Category;"

    Select Case fraDisplay
    Case Is = 1
    sql = strSEL _
    & strWHERE + " And Invoice = No " _
    & strUNION _
    & strWHERE + " And Invoice = No " _
    & strORDER

    Case Is = 2
    sql = strSEL _
    & strWHERE + " AND Invoice = Yes " _
    & strUNION _
    & strWHERE + " AND Invoice = Yes " _
    & strORDER

    Case Is = 3
    sql = strSEL _
    & strWHERE _
    & strUNION _
    & strWHERE _
    & strORDER

    End Select
    DoCmd.OpenForm stDocName, , , , acFormReadOnly
    Forms!frmDisplayBoth.RecordSource = sql</code>

    PIECE 2
    Dim sql As String
    Dim stDocName As String
    Dim strWHERE As String

    stDocName = "frmDisplayBoth"
    strWHERE = "WHERE [ProjectID] = Forms!frmCustomers!fsubProjects.Form![ProjectID] "

    Select Case fraDisplay
    Case Is = 1
    sql = "SELECT ProjectID,Category,Item AS [Item / Rate],ItemType,Quantity,ProductCode,Format(ItemCost,'cu rrency') AS [Cost / Unit],Invoice " _
    & "FROM qryMaterialsForUnion " _
    & strWHERE + " And Invoice = No " _
    & "UNION SELECT ProjectID,WorkDate,Format(LabourRate,'currency'),' ',Hours,'','',Invoice " _
    & "FROM qryLabourForUnion " _
    & strWHERE + " And Invoice = No " _
    & "ORDER BY Invoice DESC , Category;"

    Case Is = 2
    sql = "SELECT ProjectID,Category,Item AS [Item / Rate],ItemType,Quantity,ProductCode,Format(ItemCost,'cu rrency') AS [Cost / Unit],Invoice " _
    & "FROM qryMaterialsForUnion " _
    & strWHERE + " AND Invoice = Yes " _
    & "UNION SELECT ProjectID,WorkDate,Format(LabourRate,'currency'),' ',Hours,'','',Invoice " _
    & "FROM qryLabourForUnion " _
    & strWHERE + " AND Invoice = Yes " _
    & "ORDER BY Invoice DESC , Category;"

    Case Is = 3
    sql = "SELECT ProjectID,Category,Item AS [Item / Rate],ItemType,Quantity,ProductCode,Format(ItemCost,'cu rrency') AS [Cost / Unit],Invoice " _
    & "FROM qryMaterialsForUnion " _
    & strWHERE _
    & "UNION SELECT ProjectID,WorkDate,Format(LabourRate,'currency'),' ',Hours,'','',Invoice " _
    & "FROM qryLabourForUnion " _
    & strWHERE _
    & "ORDER BY Invoice DESC , Category;"

    End Select
    DoCmd.OpenForm stDocName, , , , acFormReadOnly
    Forms!frmDisplayBoth.RecordSource = sql

    Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: code to display read-only data on a pop-up form (2

    You won't notice any difference in performance between these versions.
    I'd prefer the first version, because if you want to change the "main" part of the SQL, you only have to do it in one place. The second version would require you to apply exactly the same changes in three locations, increasing the probability of mistakes.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code to display read-only data on a pop-up for

    Perfect!

    Thanks.

    Tom

Posting Permissions

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