CODEWITHSUNDEEP
vbams-accessreport
barry17
barry17

Reputation: 153

Access reporting with queries?

I have multiple queries that I'm trying to use to build a report. The way I'm doing is by using a temporary table. As I'm building it, I noticed that it takes quite sometime for the report to run because I'm using 2 criteria for each record.

So basically what I'm doing is populating the temp table with 2 values

CompanyID | Unit Price

Now what I do DlookUp the beginning inventory for each CompanyID, here's my code...

rs2.movefirst
with rs2
    While Not .EOF
        lngCompanyID = rs2("CompanyID")
            lngUnitPrice = rs2("UnitPrice")
            'GETTING BEGINNING INVENTORY VALUES
            lngBegCount = Nz(DLookup("BegCount", "qryBegInv", "UnitPrice = " & lngUnitPrice & " AND CompanyID =" & CompanyID & ""), 0)
            .Edit
            rs2("BegInvCount") = lngBegCount
            .Update
            rs2.movenext

     wend
End with

The problem I'm seeing is that the qryBEGINV that I'm looking up take about 10-15 seconds to load so since I have over 30 records, it takes a few minutes to run the report. Is there a way for me to be more efficient? Possibly DLOOKUP the values ONCE and then filtering instead of looking them up and filtering for every record?

Upvotes: 0

Views: 32

Answers (1)

nicomp
nicomp

Reputation: 4647

Start with another temp table populated with the results of qryBegInv so you aren't recreating that results set on every loop iteration.

Upvotes: 1

Related Questions