Solved

Alternate to DLOOKUP for unbound form controls

Posted on 2009-03-29
30
608 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

713 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