Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 6 votes, 5.00 average. Display Modes
Old 08-17-2009, 06:31 AM   #1
phual
Newly Registered User
 
Join Date: Jun 2009
Posts: 27
Thanks: 4
Thanked 0 Times in 0 Posts
phual is on a distinguished road
There are alternative to DLookup.... allegedly...

I've fallen foul of the 'DLookup is slow as hell' issue for a loop that I've coded. Whilst I can find plenty of references that alternative to DLookup exist, I've found nothing that explicity says how they work!

Following information from this link, I'm assuming that the ideal solution uses seek or some find variant.

The following code is used on a form to reset fileds used for filtering. I'm sure that there are more elegent solution to this partiular problem (which I would also be interested in hearing), but I'm specifically interested in the concept of the DLookup alternative and would like to see how that would be applied to this scenario.

Code:
 
'For each record in table, set value of filter-fields on form to null
For n = 1 To m
    Me.Controls("Filter_" & DLookup("Field", "tblFilterSetup", "ID = " & n)) = Null
    On Error Resume Next        'if there is no record with that ID, continue
Next n
Thanks

Stuart

phual is offline   Reply With Quote
Old 08-17-2009, 07:18 AM   #2
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: There are alternative to DLookup.... allegedly...

What are you trying to do ??

I can imagine... you open your table in a recordset (search for "openrecordset" to find some samples) and loop that...
But your checking controls based upon a setup table?
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 08-17-2009, 11:57 AM   #3
phual
Newly Registered User
 
Join Date: Jun 2009
Posts: 27
Thanks: 4
Thanked 0 Times in 0 Posts
phual is on a distinguished road
Re: There are alternative to DLookup.... allegedly...

I want to understand how to use an alternative to DLookup but I presume your curiosity is more targeted towards what I want my specific code to do.

I have a large table with various yes/no fields that represent available services. These services can be grouped into wider categories. Typically, a user might want to perform a search of the format:

(1a OR 1c) AND (2b) AND (3c OR 3d)

where the number represents the group and letter represents a field within that group. These grouping are defined in the table tblFilterSetup, which is used to decide how to structure the syntax for the above filter (i.e. when to use an OR and when to use an AND).

Of course, having a large, complex set of filter parameters on my user's form, I also have a need to reset the values of those parameters so that my users can recreate their filter afresh. Conveniently, I happen to have a table which stores the names of all the fields I have filters for (i.e. tblFilterSetup), so I may as well use that to drive the code that resets all of the filter parameters to null.

Now, the code to create the filter is rather convoluted and distracts from my fundamental question. The code to set all of the filters to null is really simple. Hence, I presented the simpler code to illustrate my problem.

I'm comfortable in creating my recordset... I'm struggling with is how to find a record based upon one value in the record set and extract the corresponding value from another field - i.e. a more efficient equivalent of DLookup direct to the table.

Any clearer?

Stuart

P.S. I know that I could have gone for a different structure to the 'one table with lots of y/n fields' idea, and had I made the table in the first place I probably would have. I would have most likely had three tables:
- tblOrganisation (organisation information),
- tblServiceLookup (basically, tblFilterSetup), and
- tblServices (which contains only Org_ID and Service_ID)
However, I'm tasked with building a user interface, and whether or not I try twisting arms to redesign the data structure, I'm still curious about how to achieve the DLookup functionality without the performance overhead.

phual is offline   Reply With Quote
Old 08-18-2009, 08:27 AM   #4
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,800
Thanks: 55
Thanked 1,028 Times in 994 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: There are alternative to DLookup.... allegedly...

surely simply have an. update query

use the id column, to determine the row range that you want to process, and update all the necessary fields to whatever value you need.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 08-18-2009, 11:10 AM   #5
phual
Newly Registered User
 
Join Date: Jun 2009
Posts: 27
Thanks: 4
Thanked 0 Times in 0 Posts
phual is on a distinguished road
Re: There are alternative to DLookup.... allegedly...

Quote:
Originally Posted by gemma-the-husky View Post
surely simply have an. update query

use the id column, to determine the row range that you want to process, and update all the necessary fields to whatever value you need.
I'd not really considered it. It never crossed my mind to use a query to update unassigned controls on a form. At least, I assume that your suggestion is in response to resetting those values rather than to applying a filter. I can't really visualise how it may work, but the concept seems simple. I think I'd have to play with it to see what happens.

Anyway, my question still stands...

I want to understand how to use an alternative to DLookup (or "how to find a record based upon one value in a record set and extract the corresponding value from another field - i.e. a more efficient equivalent of DLookup direct to the table").

Stuart
phual is offline   Reply With Quote
Old 08-18-2009, 02:32 PM   #6
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,800
Thanks: 55
Thanked 1,028 Times in 994 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: There are alternative to DLookup.... allegedly...

i dont think there can be a more optimised way of finding a single value than using dlookup

think of it in terms of a vectored lookup - you are asking the compiler to retrieve a value from a memory location that is identified by a single particular value. - MS have clearly optimised dlookup for just this task. (a database doesnt really have all these tables etc - its a single monolithic .mdb file, and its all just data locations, and pointer chains)

the issue comes if you need several fields from the same row (tuple) - in which case the issue is "are several related dlookups less efficient than retrieving all the values viia a recordset", and consensus here would be that it is probably more efficient to open a recordset.- i often use dlookups though, if i have 2 or 3 values to get


to go further - dlookup definitely isnt slow

what is possibly slow (or possibly more correctly inefficient) is iterating a loop, and using a dlookup in each cycle of the loop. Generally if you can find a way of designing a query to do the work, then access will optimise the query for you.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.

Last edited by gemma-the-husky; 08-18-2009 at 02:38 PM.
gemma-the-husky is offline   Reply With Quote
Old 08-19-2009, 12:53 AM   #7
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Thumbs up Re: There are alternative to DLookup.... allegedly...

Quote:
Originally Posted by gemma-the-husky View Post
to go further - dlookup definitely isnt slow
A single DLookup isnt any slower than a single query...
10 DLookups vs 1 query is... much much much slower.

Instead of this DLookup
Code:
For n = 1 To m
    Me.Controls("Filter_" & DLookup("Field", "tblFilterSetup", "ID = " & n)) = Null
    On Error Resume Next        'if there is no record with that ID, continue
Next n
I would probably open the filtersetup table in a recordset and itterate the recordset instead of itterating 1 to M
Something like: (quick air code)
Code:
Dim Dao.Recordset
set rs = Currentdb.openrecordset ("Select Field from tblFilterSetup")
do while not rs.EoF
    Me.Controls("Filter_" & rs!Field) = Null
    rs.movenext
loop
Alternatively if you want to loop all the controls called filter you can use something like: (quick air code)
Code:
Dim x As Object
For Each x In Me.Controls
    If x.Name Like "Filter_*" Then
        x = Null
    End If
Next x
Which will look all your controls in the form and set all to Null that start with Filter_
Without the need for added tables or selects/DLookups.

This should be faster even, unless there are a 1000 controls on the the form.

__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 09-19-2009, 03:18 AM   #8
phual
Newly Registered User
 
Join Date: Jun 2009
Posts: 27
Thanks: 4
Thanked 0 Times in 0 Posts
phual is on a distinguished road
Re: There are alternative to DLookup.... allegedly...

Some time ago I asked this question, got to the verge of success, and then disappeared into a block hole for many fun and exciting adventures... that's my argument and I'm sticking to it!

Anyway, I finally returned to get the problem sorted and express my appreciation for assistance given.

I've put my final solutions below for both my original code and for the associated search function (which was a similar problem, but much more complicated to post). It all works with a good response time, but don't let that stop you pointing out improvements if any smack you in the face...

Revision of code posted earlier:
Code:
 
'Define a database and recordset and then read in the tblFilterSetup table
Dim rstFilterSetup As Dao.Recordset

Set rstFilterSetup = CurrentDb.OpenRecordset("tblFilterSetup", dbOpenDynaset)

'For each record in recordset (table), set value of filter to null
Do While Not rstFilterSetup.EOF
    Me.Controls("Filter_" & rstFilterSetup!Field) = Null
    rstFilterSetup.MoveNext
Loop

rstFilterSetup.Close    'Close the recordset (keep the memory tidy)
The more complicated search function (now without DLookup):
Code:
'Define array where:
   'There are enough elements to hold all of the values (could be over 50 for some)
   'column 1 is the field name from tblFilterSetup
   'column 2 is value of filter
Dim arrayFilterParam(1 To 100, 1 To 2) As String

'Define other variables
Dim varFilter As String 'used to build up the filter
Dim g As Integer 'incremental number representing different groups
Dim f As Integer 'number of groups
Dim n As Integer 'incremental number representing different fields within a group
Dim Startflag As Integer 'flag indicating that a starting "(" is required
Dim Endflag As Integer 'flag indicating that a final ")" is required
Dim ORflag As Integer 'flag set to show need for OR operator
Dim ANDflag As Integer 'flag set to show need for AND operator

'Define a database and recordset and then read in the tblFilterSetup table for current group
'See http://allenbrowne.com/ser-29.html
Dim rstFilterSetup As Dao.Recordset

'Find max value in groups (i.e. how many groups are there)
f = DMax("Group", "tblFilterSetup")

'Set values for start and end flags
Startflag = 1
Endflag = 0

'Run through the following process for each group
For g = 1 To f
   Set rstFilterSetup = CurrentDb.OpenRecordset( _
      "select * from tblFilterSetup where (Group = " & g & ")", dbOpenDynaset)

   '(Re)set record count within group to 1
   n = 1

   'Run through the whole recordset until you reach the end
   Do While Not rstFilterSetup.EOF
      'Find values for array
      arrayFilterParam(n, 1) = rstFilterSetup!Field
      arrayFilterParam(n, 2) = Me.Controls("Filter_" & rstFilterSetup!Field) & ""   'Need to add empty string to stop it from throwing as wobler

      'Uncomment to debug
      'MsgBox ("Row " & n & Chr(13) & arrayFilterParam(n, 1) & Chr(13) & arrayFilterParam(n, 2))

      'If a field is being searched (not null), then add this criteria to the filter criteria
      If (Eval("[Forms]![frmSearchInformation]![Filter_" & arrayFilterParam(n, 1) & "] Is Not Null")) Then
         'If ANDFlag was set during previous run, need to add AND to the end of the _
          filter criteria and reset both the And and Or flags _
          Need to open bracket for filter for OR grouping i.e. (... or ...) AND (... or ...)
         If ANDflag = 1 Then
            varFilter = varFilter & " AND ("
            ANDflag = 0
            ORflag = 0
         End If

         'If ORflag was set during previous run, need to add OR to end of the _
         filter criteria and reset flag
         If ORflag = 1 Then
            varFilter = varFilter & " OR "
         End If

         'Open brakets - only triggers if at least one criteria has been set due to location in workflow
         If Startflag = 1 Then
            varFilter = "("
            Startflag = 0
         End If

         'Add the criteria and indicate that the next filter additon requires an operator _
          (and / OR) by setting ORflag
         varFilter = varFilter & arrayFilterParam(n, 1) & " = " & Int(arrayFilterParam(n, 2))   'taken int from second part of this
         ORflag = 1

         'set the Endflag so that we can add a closing bracket
         Endflag = 1
      End If

      n = n + 1                  'increment n ready for next run through for this group
      rstFilterSetup.MoveNext    'go to the next record in the recordset

   Loop 'return to the start of the Do loop

   rstFilterSetup.Close    'Close the recordset (keep the memory tidy)

   'Uncomment to debug
   MsgBox (varFilter)

   'Need to close bracket for OR portion of filter
   If ANDflag = 0 And Endflag = 1 Then
      varFilter = varFilter & ")"
   End If

   'Set ANDflag so that we can use the AND operator for next group (if required)
   ANDflag = 1
Next g   'Repeat above for next group

'Uncomment to debug - shows final filter to be applied
MsgBox (varFilter)

'Set and turn on filter
Me.Form.Filter = varFilter
Me.FilterOn = True

'Esnure that there is no blank screen if there are no results
If DCount("Information_ID", "tblInformationLibrary", varFilter) = 0 Then
   MsgBox ("There are no results that match your query")
   Me.FilterOn = False
End If
The main thing that strike me that may be more efficient would be to load the recordset only once (I load it each time for each group) and possibly to make use of the recordset for finding max values instead of using DMax.

But anyway, I'm happy, so thanks again.

Stuart
phual is offline   Reply With Quote
Old 09-20-2009, 10:35 AM   #9
NigelShaw
Newly Registered User
 
NigelShaw's Avatar
 
Join Date: Jan 2008
Location: Leicester UK
Posts: 1,564
Thanks: 8
Thanked 71 Times in 47 Posts
NigelShaw is on a distinguished road
Re: There are alternative to DLookup.... allegedly...

Hi

there is a function called ELookup().. See
http://allenbrowne.com/casu-07.html

never read into it but it is suppose to be a more advance or quicker version than DLookup()

Nidge
__________________
Never, ever, argue with an idiot. They'll drag you down to their level and beat you to death with experience!

If i have helped you in any way, please tip my scales or say a little thanks
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Chars

Checkout my splash screen tool
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
for a free download.
NigelShaw is offline   Reply With Quote
Old 09-21-2009, 12:18 AM   #10
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: There are alternative to DLookup.... allegedly...

Me.Controls("Filter_" & rstFilterSetup!Field) = Null

This will not work, you cannot do "= null" you have to use Is Null (sql) or IsNull(anyfield) in VBA.

I am not quite sure what you are doing but it looks overly complex

__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Alternative to Dlookup which returns an Array of values. jxaxmxixn Modules & VBA 10 01-14-2014 06:09 AM
Alternative to DLookup? jal Modules & VBA 11 11-24-2008 05:16 PM
DLookup alternative wllsth Macros 1 10-18-2008 09:39 AM
Simple dlookup alternative question cheuschober Modules & VBA 3 03-20-2005 08:44 PM
Dlookup or alternative? Ziggy1 Modules & VBA 2 08-30-2004 06:30 AM




All times are GMT -8. The time now is 06:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World