Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Alternate to DLOOKUP for unbound form controls

Posted on 2009-03-29
30
Medium Priority
?
618 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

972 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