There are alternative to DLookup.... allegedly... (1 Viewer)

phual

Registered User.
Local time
Today, 09:23
Joined
Jun 20, 2009
Messages
27
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
 

namliam

The Mailman - AWF VIP
Local time
Today, 17:23
Joined
Aug 11, 2003
Messages
11,696
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?
 

phual

Registered User.
Local time
Today, 09:23
Joined
Jun 20, 2009
Messages
27
I want to understand how to use an alternative to DLookup :p 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.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:23
Joined
Sep 12, 2006
Messages
15,613
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.
 

phual

Registered User.
Local time
Today, 09:23
Joined
Jun 20, 2009
Messages
27
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
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:23
Joined
Sep 12, 2006
Messages
15,613
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.
 
Last edited:

namliam

The Mailman - AWF VIP
Local time
Today, 17:23
Joined
Aug 11, 2003
Messages
11,696
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.
 

phual

Registered User.
Local time
Today, 09:23
Joined
Jun 20, 2009
Messages
27
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! :D

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
 

namliam

The Mailman - AWF VIP
Local time
Today, 17:23
Joined
Aug 11, 2003
Messages
11,696
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 :(
 

Users who are viewing this thread

Top Bottom