Alternate to DLOOKUP for unbound form controls

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!!
LVL 2
pskeensAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Rey Obrero (Capricorn1)Commented:
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
 
pskeensAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
pskeensAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
make sure that this is really the name of the query,

QRY_MILEAGEMATRIX_FORMTOTALS

0
 
pskeensAuthor Commented:
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
 
pskeensAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
pskeensAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:

do a DEBUG >COMPILE

correct any errors raised
0
 
pskeensAuthor Commented:
No errors found. very weird.
0
 
Rey Obrero (Capricorn1)Commented:
can you upload your db...
check Attach File below to upload
0
 
pskeensAuthor Commented:
How can you tell if you have a MISSING reference?  Is this a type, or actually missing?  Sorry, confused on this one.
0
 
pskeensAuthor Commented:
This thing is quite large, almost 40 mb with all linked tables.  I will see if I can scale it down some.
0
 
Rey Obrero (Capricorn1)Commented:
How can you tell if you have a MISSING reference?

you will a prefix MISSING: <object library>
0
 
pskeensAuthor Commented:
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
 
pskeensAuthor Commented:
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
 
pskeensAuthor Commented:
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
 
pskeensAuthor Commented:
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
 
pskeensAuthor Commented:
I got DAO added to the Reference library, however I am still throwing up the same errors as before.  Back to square one.  
0
 
Rey Obrero (Capricorn1)Commented:
try doing a decompile

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

create a blank db and import all objects
0
 
pskeensAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
from your post above i see that the QRY_MILEAGEMATRIX_FORMTOTALS is also based on another query.
0
 
Rey Obrero (Capricorn1)Commented:
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
 
pskeensAuthor Commented:
That is correct.  This query does the calculations of another query to return only the aggregated totals.  
0
 
pskeensAuthor Commented:
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
 
pskeensAuthor Commented:
I accept the answer.  Thanks for staying with me through this one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.