Solved

Alternate to DLOOKUP for unbound form controls

Posted on 2009-03-29
30
603 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
make sure that this is really the name of the query,

QRY_MILEAGEMATRIX_FORMTOTALS

0
 
LVL 2

Author Comment

by:pskeens
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

do a DEBUG >COMPILE

correct any errors raised
0
 
LVL 2

Author Comment

by:pskeens
Comment Utility
No errors found. very weird.
0
 
LVL 119

Expert Comment

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

Author Comment

by:pskeens
Comment Utility
How can you tell if you have a MISSING reference?  Is this a type, or actually missing?  Sorry, confused on this one.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Author Comment

by:pskeens
Comment Utility
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
Comment Utility
How can you tell if you have a MISSING reference?

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

Author Comment

by:pskeens
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
That is correct.  This query does the calculations of another query to return only the aggregated totals.  
0
 
LVL 2

Author Comment

by:pskeens
Comment Utility
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
Comment Utility
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
Comment Utility
I accept the answer.  Thanks for staying with me through this one.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

9 Experts available now in Live!

Get 1:1 Help Now