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

    Unanswered: Need help understanding this code.

    I'm new to VB and access and I need help in understanding this code what it does exactly. So if anyone can explain to me in detail what each line do that will be very helpful.


    1 Private Sub OpenDR6Report_Click()
    2 On Error GoTo bombout
    3
    4 Dim db As Database, qrs1 As Recordset, qrs2 As Recordset, trs As Recordset, qrs3 As
    Recordset
    5 Dim td As TableDef, tdvar As TableDef, qrs4 As Recordset, qrs5 As Recordset, qrs6 As
    Recordset
    6 Dim StartDate As Date, EndDate As Date, TotalCommodity As Single, RemainingWeight As
    Single
    7
    8 If Me!DR6TransactionId = 0 Then
    9 GoTo depart
    10 End If
    11
    12
    13 DoCmd.Hourglass True
    14 DoCmd.Echo False, "Running DR6 Form..."
    15 Set db = CurrentDb
    16
    17 ' Removes TempTable4 if they exist
    18 For Each tdvar In db.TableDefs
    19 If tdvar.Name = "TempTable4" Then db.TableDefs.Delete "TempTable4"
    20 Next tdvar
    21
    22 Set qrs1 = db.OpenRecordset("SELECT * FROM DR6Query WHERE ID = " & Me!DR6TransactionId
    , dbOpenDynaset, dbSeeChanges)
    23 EndDate = DateAdd("d", -1, qrs1!ManualWeightDate)
    24 StartDate = DateAdd("m", -1, EndDate)
    25 Set qrs2 = db.OpenRecordset("SELECT * FROM DR6CommodityUsageQuery WHERE
    ManualWeightDate >= #" _
    26 & StartDate & "# AND ManualWeightDate <= #" & EndDate & "# AND CommodityId = " &
    qrs1!CommodityId & _
    27 " AND Incoming = true", dbOpenDynaset, dbSeeChanges)
    28 Set qrs3 = db.OpenRecordset("SELECT * FROM DR6ConstantTableQuery WHERE CommodityId = "
    & qrs1!CommodityId _
    29 & " WITH OWNERACCESS OPTION", dbOpenDynaset, dbSeeChanges)
    30 Set qrs4 = db.OpenRecordset("SELECT Sum(CommodityWeight) AS
    JurisdictionCommodityWeight, JurisdictionID FROM DR6CommodityUsageQuery WHERE
    CommodityId = " _
    31 & qrs1!CommodityId & " AND Incoming = True AND ManualWeightDate >= #" & StartDate & _
    32 "# AND ManualWeightDate <= #" & EndDate & "# AND CSRoute = True GROUP BY
    JurisdictionID ", dbOpenDynaset, dbSeeChanges)
    33 Set qrs5 = db.OpenRecordset("SELECT * FROM TransactionQuery WHERE ID = " & Me!
    DR6TransactionId, dbOpenDynaset, dbSeeChanges)
    34 Set qrs6 = db.OpenRecordset("SELECT * FROM CompanyQuery WHERE ID = 2", dbOpenDynaset,
    dbSeeChanges)
    35
    36 ' Creates TempTable
    37 Set td = db.CreateTableDef("TempTable4")
    38 td.Fields.Append td.CreateField("JurisdictionName", dbText)
    39 td.Fields.Append td.CreateField("JurisdictionAddress1", dbText)
    40 td.Fields.Append td.CreateField("JurisdictionAddress2", dbText)
    41 td.Fields.Append td.CreateField("JurisdictionAddress3", dbText)
    42 td.Fields.Append td.CreateField("JurisdictionCertNumber", dbText)
    43 td.Fields.Append td.CreateField("JurisdictionContact", dbText)
    44 td.Fields.Append td.CreateField("JurisdictionPhone", dbText)
    45 td.Fields.Append td.CreateField("ReceiverName", dbText)
    46 td.Fields.Append td.CreateField("ReceiverCertNumber", dbText)
    47 td.Fields.Append td.CreateField("CommodityName", dbText)
    48 td.Fields.Append td.CreateField("RedemptionWeight", dbSingle)
    49 td.Fields.Append td.CreateField("RefundValue", dbSingle)
    50 td.Fields.Append td.CreateField("ProcessingPayment", dbSingle)
    51 td.Fields.Append td.CreateField("WeightTicketId", dbText)
    52 td.Fields.Append td.CreateField("ReceivedWeight", dbSingle)
    53 td.Fields.Append td.CreateField("AdministrationFee", dbSingle)
    54 td.Fields.Append td.CreateField("ReceivedDate", dbDate)
    55 db.TableDefs.Append td
    56
    57 Set trs = db.OpenRecordset("TempTable4", dbOpenDynaset, dbSeeChanges)
    58
    59 TotalCommodity = 0
    60 qrs2.MoveFirst
    61 Do Until qrs2.EOF
    62 TotalCommodity = TotalCommodity + qrs2!CommodityWeight
    63 qrs2.MoveNext
    64 Loop
    65
    66 RemainingWeight = TotalCommodity
    67
    68 qrs4.MoveFirst
    69 Do Until qrs4.EOF
    70 If qrs4!JurisdictionCommodityWeight > 0 Then
    71 trs.AddNew
    72 qrs2.MoveFirst
    73 Do Until qrs2!JurisdictionID = qrs4!JurisdictionID
    74 qrs2.MoveNext
    75 Loop
    76 trs!JurisdictionName = qrs2!Name
    77 trs!JurisdictionAddress1 = qrs2!MailAddress1
    78 trs!JurisdictionAddress2 = qrs2!MailAddress2
    79 trs!JurisdictionAddress3 = qrs2!MailCity & " , " & qrs2!MailState & " " &
    qrs2!MailZip
    80 trs!JurisdictionCertNumber = qrs2!CertNumber
    81 trs!JurisdictionContact = qrs2!Contact
    82 trs!JurisdictionPhone = qrs2!Phone
    83 trs!ReceiverName = qrs1!Name
    84 trs!ReceiverCertNumber = qrs1!CertNumber
    85 trs!CommodityName = qrs1!CommodityName
    86 trs!ReceivedWeight = qrs1!ManualNetWeight * qrs4!JurisdictionCommodityWeight /
    TotalCommodity
    87 RemainingWeight = RemainingWeight - qrs4!JurisdictionCommodityWeight
    88 trs!RefundValue = trs!ReceivedWeight * qrs3!RefundConstant
    89 trs!RedemptionWeight = trs!RefundValue / qrs3!RedemptConstant
    90 trs!ProcessingPayment = trs!RedemptionWeight * qrs3!ProcessConstant
    91 trs!WeightTicketID = qrs1!ID
    92 trs!AdministrationFee = trs!RefundValue * qrs3!AdminConstant
    93 trs!ReceivedDate = qrs1!ManualWeightDate
    94 trs.Update
    95 End If
    96 qrs4.MoveNext
    97 Loop
    98
    99 If RemainingWeight > 0.01 Then
    100 trs.AddNew
    101 trs!JurisdictionName = qrs6!Name
    102 trs!JurisdictionAddress1 = qrs6!MailAddress1
    103 trs!JurisdictionAddress2 = qrs6!MailAddress2
    104 trs!JurisdictionAddress3 = qrs6!MailCity & " , " & qrs6!MailState & " " &
    qrs6!MailZip
    105 trs!JurisdictionCertNumber = qrs6!CertNumber
    106 trs!JurisdictionContact = qrs6!Contact
    107 trs!JurisdictionPhone = qrs6!Phone
    108 trs!ReceiverName = qrs1!Name
    109 trs!ReceiverCertNumber = qrs1!CertNumber
    110 trs!CommodityName = qrs1!CommodityName
    111 trs!ReceivedWeight = qrs1!ManualNetWeight * RemainingWeight / TotalCommodity
    112 trs!RefundValue = trs!ReceivedWeight * qrs3!CPRefundConstant
    113 trs!RedemptionWeight = trs!RefundValue / qrs3!CPRedemptConstant
    114 trs!ProcessingPayment = trs!RedemptionWeight * qrs3!CPProcessConstant
    115 trs!WeightTicketID = qrs1!ID
    116 trs!AdministrationFee = trs!RefundValue * qrs3!CPAdminConstant
    117 trs!ReceivedDate = qrs1!ManualWeightDate
    118 trs.Update
    119 End If
    120
    121 qrs5.Edit
    122 qrs5!DR6Done = True
    123 qrs5.Update
    124 trs.Close
    125 qrs1.Close
    126 qrs2.Close
    127 qrs3.Close
    128 qrs4.Close
    129 qrs5.Close
    130 qrs6.Close
    131 db.Close
    132 DoCmd.OpenReport "DR6Report", acViewPreview
    133
    134 depart:
    135 DoCmd.Echo True
    136 DoCmd.Hourglass False
    137 Exit Sub
    138
    139 bombout:
    140 MsgBox Err.Description
    141 Resume depart
    142 End Sub
    143

  2. #2
    Join Date
    Jun 2011
    Posts
    6
    line 22: it starts with SELECT it is retreiving data from the database
    line 23: Take the date value from the database record "qrs1!ManualWeightDat" and remove 1 day from the date
    line 24: Take the date calculated on line 23 and remove 1 month

    i think Me!DR6TransactionId is the ID
    dbOpenDynaset is a keyword
    It opens a dynaset-type Recordset
    dbOpenForwardOnly opens a forward-only type Recordset

    if you declare qrs1 as a recordset, and qrs2 as a recordset
    then you can store the results of two seperate queries

    Line 37: it's creating a table object and then populating it
    Line 38: is adding a column to the new table called "JurisdictionName

    DB is declared up top.. Dim db As Database
    so "db" is a database object... and line 55 is adding this newly created table to the database

    After this I'm lost, can some explain to me the rest of the thing goes.

    thanks in advance..

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I doubt someone is going to explain, line for line, the code. But I will help with understanding of the code in a general manner.

    First, you need to understand that a recordset is similar to a table. There can be 1 to n records in a recordset, but the thing to keep in mind is you can only access one record in a recordset at one time. So the code after line 55 is looping through the recordsets.

    Line 22 through 34 are setting up 5 recordsets with data. Then line 57 is opening up the empty table, TempTable4, so that you can add records to it.

    59 through 64 loop through qrs2 and total up the CommodityWeight to get the TotalCommodityWeight(?).

    The rest of it is populating the TempTable4 with data. When you see trs it is referencing the table. AddNew adds a new record to the table, then all of the trs!BlahBlah are assigning values to the fields in the table. When you see and Update, that means, write the data to the table. Imagine you are adding a record to a table manually (by typing the data). You would have to start at the first field and type in the data you wanted. You would enter data in all the fields until you get to the last one. Once you get to th last field and press enter, the record is saved to the table. Until that point you can press the Esc button to abort saving the record. Same thing with a recordset. Update all the fields then tell it to update the recordset with Update.


    The last part opens qr5 and edits the data with Edit. Then it sets the DR6Done field to True. And updates the record with Update.

    The rest of if cleans up the recordsets by closing them and finally the report is printed, I am assuming based on the data in the TempTable4.

    And the very last part is error trapping. If an error occurs the program will jump to the bombout tag. Line 2 tells Access what to do if there is an error. The bombout tag reports the error then calls the depart tag to turn the Echo back on (so that the screen refreshes properly) and also turns the Hourglass off.

  4. #4
    Join Date
    Jun 2011
    Posts
    6
    thanks alot for the explaination!! DCKunkle!!

    I really understand it now. your input was great!!!!! thanks a million!!!!

  5. #5
    Join Date
    Jun 2011
    Posts
    6
    Can someone tell me what calculation is it actually doing? I want to dump this old code and in order to do this, I need to see what this code does. I mean what is it calculating. If someone can tell me what data it use to calculate his result that will be awsome.

    I just need to build a new code that will do the excat stuff, but in order to build a new code. I need to know what its calculating.

    Can someone please tell me what is calculating and how is he getting his result?

    This way I will know what to code on my new code..

    thanks

  6. #6
    Join Date
    Jun 2011
    Posts
    6
    can someone help me on how he get his result. I need some type of equation to get to the final answer.

    Like what he did to get the result

    example: weightmaster - totalweightmaster = total +combine this and that = result.

    I don't know something like that so I can understand on my new system to pull what record and from where. The code he is doing is hard to understand .

    thanks

Posting Permissions

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