Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    6

    Unanswered: convert vba code to sql

    I got a vba code which I don't understand, can someone help me to convert it to sql 2005 so I can run it from sql instead of using the vba code from access.

    Here is the code. Or help me understand what it does. thanks a million!!

    Private Sub OpenDR6Report_Click()
    On Error GoTo bombout

    Dim db As Database, qrs1 As Recordset, qrs2 As Recordset, trs As Recordset, qrs3 As Recordset
    Dim td As TableDef, tdvar As TableDef, qrs4 As Recordset, qrs5 As Recordset, qrs6 As Recordset
    Dim StartDate As Date, EndDate As Date, TotalCommodity As Single, RemainingWeight As Single

    If Me!DR6TransactionId = 0 Then
    GoTo depart
    End If


    DoCmd.Hourglass True
    DoCmd.Echo False, "Running DR6 Form..."
    Set db = CurrentDb

    ' Removes TempTable4 if they exist
    For Each tdvar In db.TableDefs
    If tdvar.Name = "TempTable4" Then db.TableDefs.Delete "TempTable4"
    Next tdvar

    Set qrs1 = db.OpenRecordset("SELECT * FROM DR6Query WHERE ID = " & Me!DR6TransactionId, dbOpenDynaset, dbSeeChanges)
    EndDate = DateAdd("d", -1, qrs1!ManualWeightDate)
    StartDate = DateAdd("m", -1, EndDate)
    Set qrs2 = db.OpenRecordset("SELECT * FROM DR6CommodityUsageQuery WHERE ManualWeightDate >= #" _
    & StartDate & "# AND ManualWeightDate <= #" & EndDate & "# AND CommodityId = " & qrs1!CommodityId & _
    " AND Incoming = true", dbOpenDynaset, dbSeeChanges)
    Set qrs3 = db.OpenRecordset("SELECT * FROM DR6ConstantTableQuery WHERE CommodityId = " & qrs1!CommodityId _
    & " WITH OWNERACCESS OPTION", dbOpenDynaset, dbSeeChanges)
    Set qrs4 = db.OpenRecordset("SELECT Sum(CommodityWeight) AS JurisdictionCommodityWeight, JurisdictionID FROM DR6CommodityUsageQuery WHERE CommodityId = " _
    & qrs1!CommodityId & " AND Incoming = True AND ManualWeightDate >= #" & StartDate & _
    "# AND ManualWeightDate <= #" & EndDate & "# AND CSRoute = True GROUP BY JurisdictionID ", dbOpenDynaset, dbSeeChanges)
    Set qrs5 = db.OpenRecordset("SELECT * FROM TransactionQuery WHERE ID = " & Me!DR6TransactionId, dbOpenDynaset, dbSeeChanges)
    Set qrs6 = db.OpenRecordset("SELECT * FROM CompanyQuery WHERE ID = 2", dbOpenDynaset, dbSeeChanges)

    ' Creates TempTable
    Set td = db.CreateTableDef("TempTable4")
    td.Fields.Append td.CreateField("JurisdictionName", dbText)
    td.Fields.Append td.CreateField("JurisdictionAddress1", dbText)
    td.Fields.Append td.CreateField("JurisdictionAddress2", dbText)
    td.Fields.Append td.CreateField("JurisdictionAddress3", dbText)
    td.Fields.Append td.CreateField("JurisdictionCertNumber", dbText)
    td.Fields.Append td.CreateField("JurisdictionContact", dbText)
    td.Fields.Append td.CreateField("JurisdictionPhone", dbText)
    td.Fields.Append td.CreateField("ReceiverName", dbText)
    td.Fields.Append td.CreateField("ReceiverCertNumber", dbText)
    td.Fields.Append td.CreateField("CommodityName", dbText)
    td.Fields.Append td.CreateField("RedemptionWeight", dbSingle)
    td.Fields.Append td.CreateField("RefundValue", dbSingle)
    td.Fields.Append td.CreateField("ProcessingPayment", dbSingle)
    td.Fields.Append td.CreateField("WeightTicketId", dbText)
    td.Fields.Append td.CreateField("ReceivedWeight", dbSingle)
    td.Fields.Append td.CreateField("AdministrationFee", dbSingle)
    td.Fields.Append td.CreateField("ReceivedDate", dbDate)
    db.TableDefs.Append td

    Set trs = db.OpenRecordset("TempTable4", dbOpenDynaset, dbSeeChanges)

    TotalCommodity = 0
    qrs2.MoveFirst
    Do Until qrs2.EOF
    TotalCommodity = TotalCommodity + qrs2!CommodityWeight
    qrs2.MoveNext
    Loop

    RemainingWeight = TotalCommodity

    qrs4.MoveFirst
    Do Until qrs4.EOF
    If qrs4!JurisdictionCommodityWeight > 0 Then
    trs.AddNew
    qrs2.MoveFirst
    Do Until qrs2!JurisdictionID = qrs4!JurisdictionID
    qrs2.MoveNext
    Loop
    trs!JurisdictionName = qrs2!Name
    trs!JurisdictionAddress1 = qrs2!MailAddress1
    trs!JurisdictionAddress2 = qrs2!MailAddress2
    trs!JurisdictionAddress3 = qrs2!MailCity & " , " & qrs2!MailState & " " & qrs2!MailZip
    trs!JurisdictionCertNumber = qrs2!CertNumber
    trs!JurisdictionContact = qrs2!Contact
    trs!JurisdictionPhone = qrs2!Phone
    trs!ReceiverName = qrs1!Name
    trs!ReceiverCertNumber = qrs1!CertNumber
    trs!CommodityName = qrs1!CommodityName
    trs!ReceivedWeight = qrs1!ManualNetWeight * qrs4!JurisdictionCommodityWeight / TotalCommodity
    RemainingWeight = RemainingWeight - qrs4!JurisdictionCommodityWeight
    trs!RefundValue = trs!ReceivedWeight * qrs3!RefundConstant
    trs!RedemptionWeight = trs!RefundValue / qrs3!RedemptConstant
    trs!ProcessingPayment = trs!RedemptionWeight * qrs3!ProcessConstant
    trs!WeightTicketID = qrs1!ID
    trs!AdministrationFee = trs!RefundValue * qrs3!AdminConstant
    trs!ReceivedDate = qrs1!ManualWeightDate
    trs.Update
    End If
    qrs4.MoveNext
    Loop

    If RemainingWeight > 0.01 Then
    trs.AddNew
    trs!JurisdictionName = qrs6!Name
    trs!JurisdictionAddress1 = qrs6!MailAddress1
    trs!JurisdictionAddress2 = qrs6!MailAddress2
    trs!JurisdictionAddress3 = qrs6!MailCity & " , " & qrs6!MailState & " " & qrs6!MailZip
    trs!JurisdictionCertNumber = qrs6!CertNumber
    trs!JurisdictionContact = qrs6!Contact
    trs!JurisdictionPhone = qrs6!Phone
    trs!ReceiverName = qrs1!Name
    trs!ReceiverCertNumber = qrs1!CertNumber
    trs!CommodityName = qrs1!CommodityName
    trs!ReceivedWeight = qrs1!ManualNetWeight * RemainingWeight / TotalCommodity
    trs!RefundValue = trs!ReceivedWeight * qrs3!CPRefundConstant
    trs!RedemptionWeight = trs!RefundValue / qrs3!CPRedemptConstant
    trs!ProcessingPayment = trs!RedemptionWeight * qrs3!CPProcessConstant
    trs!WeightTicketID = qrs1!ID
    trs!AdministrationFee = trs!RefundValue * qrs3!CPAdminConstant
    trs!ReceivedDate = qrs1!ManualWeightDate
    trs.Update
    End If

    qrs5.Edit
    qrs5!DR6Done = True
    qrs5.Update
    trs.Close
    qrs1.Close
    qrs2.Close
    qrs3.Close
    qrs4.Close
    qrs5.Close
    qrs6.Close
    db.Close
    DoCmd.OpenReport "DR6Report", acViewPreview

    depart:
    DoCmd.Echo True
    DoCmd.Hourglass False
    Exit Sub

    bombout:
    MsgBox Err.Description
    Resume depart
    End Sub

  2. #2
    Join Date
    May 2008
    Posts
    97
    And how much $ are you asking for again?

Posting Permissions

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