Solved HELP WITH DLOOKUP (1 Viewer)

[dtdukes86]

Hi all been a little while

so I have a report "RptStockLocation" based on a qry "QryLocationReport" nothing fancy ,, its a list of products has in stock filtered by a stock physical location.

The locations are preset in the inventory table such as "shop front" "warehouse" "Upstairs" and so on....

also on that report I have some dlookup fields ,,,

these are

allocated - which shows the sum of the stock where order status is paid but not delivered for example,, currently allocated returns a number eg 2 or 3

sold - same as above all the orders where the sold items is marked as paid and delivered returns the total eg 2 or 3 ...

and finally I have a field called available stock , which is a calculated field base on the dlookups previous and some others and returns the qty of stock available for resale.

it does all work!!!! but the dlookups are slow and sometimes return a error if I try to rush into the field to soon.

my attempt to fix it ....

i tried to post the entire "QryLocationReport" qry to a new "HOLDING" table and then update that table with a update qry posting the dlookup values without opening the report so I could then bring the report up based on the new table but I cannot seem to get the dlookups values into the new table I just get 0's

any questions I'm happy to answer where I can and any help would be fantastic .

 

[Gasman]

If it is a report, how can you 'rush into a field'?
They are called controls BTW.
Regardless, can you join the tables to bring into the source of the report?

 

[fjell]

have you tried to use an SQL statement instead of using the Dlookup Function?
another

 

[Mike Krailo]

it does all work!!!! but the dlookups are slow and sometimes return a error if I try to rush into the field to soon.

my attempt to fix it ....

i tried to post the entire "QryLocationReport" qry to a new "HOLDING" table and then update that table with a update qry posting the dlookup values without opening the report so I could then bring the report up based on the new table but I cannot seem to get the dlookups values into the new table I just get 0's

any questions I'm happy to answer where I can and any help would be fantastic .

What is your current dlookup that you are using? The less information you provide, the harder it is for us to help you.

have you tried to use an SQL statement instead of using the Dlookup Function?
another

You can do an aggregate query that groups the products by location in a query (Totals Query).

 

[Pat Hartman]

You should be able to create a "totals" query that summarizes data and bind your report to that query. It will be significantly faster than using domain functions. Keep in mind that each domain function, each time it runs (at least once per row of the main query) spawns a separate query. That is why we never use domain functions inside of queries or VBA loops as long as there is another option and there is almost always another option. So, if the query bound to the report returns 1000 rows, your inclusion of domain functions is causing Access to run an additional 1000 queries. So, the more rows in the main query, the slower the result will be. Using a totals query at least allows Access to optimize the data retrieval.

 

[dtdukes86]

thank you to everyone who replied sorry i didnt reply to you guys individually. so basically i took on board what you said and in the most part scrapped the dlookup , i managed to find a common field and link the qry to the totals qry via one field and then show it all on the report , therefore the report works , loads faster and the whole thing is honkey dorey for now. great advice

 

[Mike Krailo]

In that case, you can now mark this as solved.