Solved

Alternate to DLOOKUP for unbound form controls

Posted on 2009-03-29
30
605 Views
Last Modified: 2013-11-28
I need a quick answer to something I have been trying to solve for the past three hours.  I have a continuous form that displays a lot of records from a calculated query.  I Need to have some controls in the form footer for calculated totals.  The totals have to come form a query called QRY_MILEAGEMATRIX_FORMTOTALS.  I am currently using unbound controls with the following

txtbox1 = Dlookup.........
txtBox2 = Dlookup.........

and so on.  As we all know that Dlookup takes too long.  I am trying to populate these controls using a vb select statement but cannot figure it out.  I have tried the following two solutions to no avail

Dim rs As Recordset
    rs.OpenRecordset "Select * From QRY_MILEAGEMATRIX_FORMTOTALS"
    Me.TOT_MILES = rs("TOT_MILES")
    Me.BASE_DOL_P_MILE = rs("BASE_DOL_P_MILE")
    rs.Close

and

Dim strSQL As String
strSQL = "Select [TOT_MILES],[BASE_DOL_P_MILE] from QRY_MILEAGEMATRIX_FORMTOTALS;"
Me.TOT_MILES = ("TOT_MILES")
Me.BASE_DOL_P_MILE = ("BASE_DOL_P_MILE")

Your valuable knowledge is appreciated!!
0
Comment
Question by:pskeens
  • 17
  • 13
30 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015179
is the result of your query QRY_MILEAGEMATRIX_FORMTOTALS yields only one record?


Dim rs As Dao.Recordset
set rs=currentdb.OpenRecordset "Select * From QRY_MILEAGEMATRIX_FORMTOTALS"
    Me.TOT_MILES = rs("TOT_MILES")
    Me.BASE_DOL_P_MILE = rs("BASE_DOL_P_MILE")
    rs.Close
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015182
is the result of your query QRY_MILEAGEMATRIX_FORMTOTALS yields only one record?


Dim rs As Dao.Recordset
set rs=currentdb.OpenRecordset "Select * From QRY_MILEAGEMATRIX_FORMTOTALS"

rs.movefirst
    Me.TOT_MILES = rs("TOT_MILES")
    Me.BASE_DOL_P_MILE = rs("BASE_DOL_P_MILE")
    rs.Close
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015188
you can also use dlookup using the query  as the domain

    Me.TOT_MILES = dlookup("TOT_MILES","QRY_MILEAGEMATRIX_FORMTOTALS")
    Me.BASE_DOL_P_MILE =dlookup("BASE_DOL_P_MILE","QRY_MILEAGEMATRIX_FORMTOTALS")
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015213
Yes the query always only returns one row of records.  
I am getting an error at this statement

"Select * From QRY_MILEAGEMATRIX_FORMTOTALS"

Expected End of Statement
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015253
Dim rs As Dao.Recordset
set rs=currentdb.OpenRecordset("Select * From QRY_MILEAGEMATRIX_FORMTOTALS")

rs.movefirst
    Me.TOT_MILES = rs("TOT_MILES")
    Me.BASE_DOL_P_MILE = rs("BASE_DOL_P_MILE")
    rs.Close
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015271
This is one that I tried already.  If I enclose the select Statement in () then I get a "Too Few Parameters: Expected 4" Error.  
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015287
make sure that this is really the name of the query,

QRY_MILEAGEMATRIX_FORMTOTALS

0
 
LVL 2

Author Comment

by:pskeens
ID: 24015308
Yes, this is the name of the query.  here is the Dlookup statements that is working using this query, its just really slow

    'txtMiles = DLookup("TOT_MILES", "QRY_MILEAGEMATRIX_FORMTOTALS")
    'txtDolMile = DLookup("BASE_DOL_P_MILE", "QRY_MILEAGEMATRIX_FORMTOTALS")
    'txtTotFrt = DLookup("TOT_FRT", "QRY_MILEAGEMATRIX_FORMTOTALS")
    'txtTot08Avg = DLookup("TOTFRT_W08_AVG", "QRY_MILEAGEMATRIX_FORMTOTALS")
    'Text73 = DLookup("TOTFRT_W08_GOAL", "QRY_MILEAGEMATRIX_FORMTOTALS")
    'Text74 = DLookup("DOLVAR_ACT_VS_08", "QRY_MILEAGEMATRIX_FORMTOTALS")
    'Text75 = DLookup("PERC_VAR_ACT_VS_08", "QRY_MILEAGEMATRIX_FORMTOTALS")
    'Text76 = DLookup("DOLVAR_ACT_VS_GOAL", "QRY_MILEAGEMATRIX_FORMTOTALS")
    'Text77 = DLookup("PERC_VAR_ACT_VS_GOAL", "QRY_MILEAGEMATRIX_FORMTOTALS")
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015347
I have even tried this one to no avail

Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Sum([ACT_MILES]) AS TOT_MILES, Sum([BASE_FRT]) AS TOT_FRT, [TOT_FRT]/[TOT_MILES] AS BASE_DOL_P_MILE, Sum([TOT_FRT_W08_AVG]) AS TOTFRT_W08_AVG, Sum([TOT_FRT_W08_GOAL]) AS TOTFRT_W08_GOAL, [TOT_FRT]-[TOTFRT_W08_AVG] AS DOLVAR_ACT_VS_08, [DOLVAR_ACT_VS_08]/[TOTFRT_W08_AVG] AS PERC_VAR_ACT_VS_08, [TOT_FRT]-[TOTFRT_W08_GOAL] AS DOLVAR_ACT_VS_GOAL, [DOLVAR_ACT_VS_GOAL]/[TOTFRT_W08_GOAL] AS PERC_VAR_ACT_VS_GOAL FROM qry_MileageMatrix_Summary;")
rs.MoveFirst
    Me.TOT_MILES = rs("TOT_MILES")
    Me.BASE_DOL_P_MILE = rs("BASE_DOL_P_MILE")
    Me.TOT_FRT = rs("TOT_FRT")
    Me.TOTFRT_W08_AVG = rs("TOTFRT_W08_AVG")
    rs.Close

I get the same error "TOO FEW PARAMETERS: EXPECTED 4"

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015377
do a compact and repair
Tools>database utilities >compact and repair

from the VBA window
Tools >references
see if you have a MISSING reference ; unselect and look for the available version and select

also make sure the Microsoft DAO x.x Object library is selected
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015468
ok, Dao is not selected in the reference list.  If I try to select it I get the following error code
dao-error.gif
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015485

do a DEBUG >COMPILE

correct any errors raised
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015503
No errors found. very weird.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015506
can you upload your db...
check Attach File below to upload
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015510
How can you tell if you have a MISSING reference?  Is this a type, or actually missing?  Sorry, confused on this one.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 2

Author Comment

by:pskeens
ID: 24015515
This thing is quite large, almost 40 mb with all linked tables.  I will see if I can scale it down some.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015518
How can you tell if you have a MISSING reference?

you will a prefix MISSING: <object library>
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015541
Nope, nothing missing.  here is all the references that i am using in this project.  I am looking through all the modules right now, but I am positive that I do not reference "Dao" anywhere.  Not sure why I cant add.  Maybe a reboot is in order just to check if something is loaded into memory.
references.gif
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015612
I actually did find a couple instance where I used "Dao"  and forgot.  I used them to look up email addresses and send email.  I commented them out, but still the same error.  Do I need to remove these modules from the project?
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015662
Out of curiosity I started a blank db and went to vb to see If I could add Dao there and I get the same error.  Anyone for how I can add this to the Reference library?
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015678
Found this after another search

You don't need DAO 3.6...
"Microsoft Office 12.0 Access Database Engine Object Library" (ACEDAO) is the equivalent and the correct version to use in Access 2007.
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015686
I got DAO added to the Reference library, however I am still throwing up the same errors as before.  Back to square one.  
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015706
try doing a decompile

http://www.granite.ab.ca/access/decompile.htm

create a blank db and import all objects
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015712
Okay here is the delima.  I was thinking that its something to do with the query and I was right.  I appended the row to a table called "Mileage_totals" and used this code

    Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * From mileage_totals")

rs.MoveFirst
    Me.TOT_MILES = rs("TOT_MILES")
    Me.BASE_DOL_P_MILE = rs("BASE_DOL_P_MILE")
    rs.Close

Works like a charm.  Why can I not call these from the query?  I could append the record to a temp  table everytime they run the form but that is not the ideal solution.  Any thoughts on this one?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015731
from your post above i see that the QRY_MILEAGEMATRIX_FORMTOTALS is also based on another query.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24015735
from your post above i see that the QRY_MILEAGEMATRIX_FORMTOTALS is also based on another query., which could be the cause of the errors.
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015742
That is correct.  This query does the calculations of another query to return only the aggregated totals.  
0
 
LVL 2

Author Comment

by:pskeens
ID: 24015867
So here is the workaround for now.  Please let me know if there is a better solution.  I created a TEMP table for the total values


      '****************************************
        'Deletes record row from TEMP table holding values
        '****************************************

    Dim dbs As DAO.Database, sql As String, rCount As Integer
            Set dbs = CurrentDb

           dbs.Execute ("DELETE FROM (Mileage_Totals)")
       
        '*****************************************
        'Requery form and append new totals to TEMP table
        '*****************************************
   
    DoCmd.Requery
   
        Application.SetOption "Confirm Action Queries", 0
        DoCmd.OpenQuery "qry_MileageTotals_Append"
        Application.SetOption "Confirm Action Queries", -1

        '****************************************
        'Sets form totals to new totals in TEMP table
        '****************************************

            Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("Select * From mileage_totals")
        rs.MoveFirst
       
            Me.TOT_MILES = rs("TOT_MILES")
            Me.BASE_DOL_P_MILE = rs("BASE_DOL_P_MILE")
            Me.TOT_FRT = rs("TOT_FRT")
            Me.TOTFRT_W08_AVG = rs("TOTFRT_W08_AVG")
            Me.TOTFRT_W08_GOAL = rs("TOTFRT_W08_GOAL")
            Me.DOLVAR_ACT_VS_08 = rs("DOLVAR_ACT_VS_08")
            Me.PERC_VAR_ACT_VS_08 = rs("PERC_VAR_ACT_VS_08")
            Me.DOLVAR_ACT_VS_GOAL = rs("DOLVAR_ACT_VS_GOAL")
            Me.PERC_VAR_ACT_VS_GOAL = rs("PERC_VAR_ACT_VS_GOAL")
        rs.Close
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 24017955
well,  if you can't create a single query to get the totals then the solution you created will be good..

you can use a more simple way of turning OFF/ON the warnings with

docmd.setwarnings false
DoCmd.OpenQuery "qry_MileageTotals_Append"
docmd.setwarnings true
0
 
LVL 2

Author Closing Comment

by:pskeens
ID: 31564137
I accept the answer.  Thanks for staying with me through this one.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now