DLookup missing first record (1 Viewer)

[Cotty42]

Hi All

I am having an issue with DLookup, which, for some reason which I do not understand appears to missing the first record in the table.

What I am trying to do is to extract the e-mail address of all users who have 'Admin' status to the database. In the table ('LogIn') there are 3 users who have 'Admin' status and this is confirmed by the DCount function in the following code. There respective ID values in the table are 1, 49 and 51.

Yet when I run the Loop to get all e-mail addresses it misses ID 1 and just returns records for ID 49 and 51.

I have tried changing other users status to 'Admin' and it finds them but always misses the first record.

Here is the code I am using:-

    LastIDRef = 0
    strToEmail = Null
    strMailToAddress = Null
    AdminCount = DCount("[ID]", "[LogIn]", "[LogIn].[Security Level] = 'Admin'")
    Do
        strLinkCriteria = "[LogIn].[Security Level] = 'Admin' And [LogIn].[ID] > " & LastIDRef
        strToEmail = DLookup("[e-mail address]", "LogIn", strLinkCriteria)
        If IsNull(strToEmail) Then Exit Do
        If IsNull(strMailToAddress) Then
            strMailToAddress = strToEmail
        Else
            strMailToAddress = strMailToAddress & ";" & strToEmail
        End If
        LastIDRef = DLookup("ID", "LogIn", strLinkCriteria)
    Loop

Any help and advice gratefully recieved

Thanks

Dave:banghead:

 

[Minty]

Debug.print strLinkCriteria as soon as you have it set - see what it is pulling.

 

[spikepl]

Your algorithm assumes that the data is ordered in the table, and that DLookup finds them in that order. Newsflash : it isn't. And it doesn't!

Tables do not have any inherent order, so anything relying on ordering needs to be explicitly defined by an ORDER BY clause in an SQL statement. You ought to lopop on an ordered record set. Google "loop access recordset"

Well, just to have a little pity on you, Allen Browne made a function that allows ordering on a Dlookup. Find it on his site. Or don't, because you really should use a recordset.

 

[Cotty42]

Thanks Spikepl

Revised code below for the benefit of other numbskulls like me.

Note to self "Just because tables are shown in order it doesn't mean they are!"

    Dim rs As Recordset
    Dim strTblName As String
    
'   *****  Open LogIn table as recordset   *****
    strTblName = "LogIn"
    
    Set rs = CurrentDb.OpenRecordset(strTblName)
    strEMailAdd = Null
    strMailTo = Null
    
    With rs
    
        If Not .BOF And Not .EOF Then   ' ***  Check table has records in it
'           *****  Ensures search starts at the first record in the recordset   *****
            .MoveLast
            .MoveFirst
            
'   *****    Cycle through all records to get all admin e-Mail Addresses
            While Not .EOF
                If rs.Fields("Security Level") = "Admin" Then
                    strEMailAdd = rs.Fields("e-mail address")
                    If IsNull(strMailTo) Then
                        strMailTo = strEMailAdd
                    Else
                        strMailTo = strMailTo & ";" & strEMailAdd
                    End If
                End If
                
                .MoveNext
            Wend
        End If
        
        rs.Close        ' ***  Close recordset when finished
    End With
    Set rs = Nothing    ' ***  Clear recordset value
    
'   *****    Build Hyperlink Address
    If IsNull(strMailTo) Then
        Me.ContactAdmin.Visible = False
        Me.ContactAdmin.HyperlinkAddress = ""
    Else
        strSubject = "Database Enquiry"
        strHyperLinkText = "mailto:" & strMailTo & "?subject=" & strSubject
        Me.ContactAdmin.HyperlinkAddress = strHyperLinkText
    End If

Cheers

Dave

 

[spikepl]

Hmmm .. this may work today but not tomorrow, because your OPenRecordset opens a table - still with a random order of records. It shouldn't.

You need to do SQL : dbOPenRecordset("SELECT * FROM myTable ORDER BY whateverItISYouOrderBy")

Update: I m rambling - in what you do (now that I read the code) the sequence does not really matter, when you do it like you do it.

 

[namliam]

Why loop thru the table at all?

why not do something like:

Set rs = CurrentDb.OpenRecordset("Select * from LogIn where [Security Level] = ""Admin"" Order by Username")

FYI, a nice(r) naming convention wouldnt allow spaces in column names and would prefix table names (tblLogIn) as well as queries and other objects in your database.

 

[pr2-eugin]

Dave, why are you opening the entire table, when you are only interested in the Admins? Could this code be a bit more efficient?

[COLOR=Green]'    *****  To avoid ambiguity. Use appropriate library.         *****
'    *****  Declare all variables to theirt appropriate type.    *****[/COLOR]
    Dim rsObj As DAO.Recordset
    Dim strEMailAdd As String
    Dim strMailTo As String
    
[COLOR=Green]'   *****  Open only Admin Records as recordset   *****[/COLOR]
    Set rsObj = CurrentDb.OpenRecordset("SELECT [e-mail address] FROM LogIn WHERE [Security Level] = 'Admin'")
    strEMailAdd = vbNullString
    strMailTo = vbNullString
    
    With rsObj
[COLOR=Green]'   *****    Check if Records exists    *****[/COLOR]
        If .Recordcount > 0 Then               
[COLOR=Green]'   *****    Cycle through all records to get all admin e-Mail Addresses    *****[/COLOR]
            Do While Not .EOF
                strMailTo = strMailTo & rsObj.Fields(0) & ";"                
                .MoveNext
            Loop
        End If
[COLOR=Green]'     *****  Close recordset when finished       *****     [/COLOR]
        .Close        
    End With
    Set rsObj = Nothing    
    
[COLOR=Green]'   *****    Build Hyperlink Address[/COLOR]
    If Len(strMailTo) = 0 Then
        Me.ContactAdmin.Visible = False
        Me.ContactAdmin.HyperlinkAddress = ""
    Else
        strSubject = "Database Enquiry"
        strHyperLinkText = "mailto:" & Left(strMailTo, Len(strMailTo)-1) & "?subject=" & strSubject
        Me.ContactAdmin.HyperlinkAddress = strHyperLinkText
    End If

EDIT: Waaaaaaaaay toooooo SLOW ! Me thinks

 

[Cotty42]

Thanks again Spikepl

It doesn't matter what order the table is in as long as the code pulls all of the admin e-mail addresses. Surely by ensuring the loop starts at the first record (.MoveLast - .MoveFirst) and then moving to the next record in a loop will pick up all of the matching records in turn, irrespective of order, or am I just being dim again?

I guess using SQL would be better if I were dealing with a large table because I would then limit the query to the 'Admin' records (I assume this would be "WHERE [LogIn].[Security Level] = 'Admin'") but in this instance the table is never likely to have more than about 15 records with probably 2 or 3 'Admin' records in them.

Your comments are noted and accepted.

Cheers

Dave

 

[Cotty42]

Why loop thru the table at all?
FYI, a nice(r) naming convention wouldnt allow spaces in column names and would prefix table names (tblLogIn) as well as queries and other objects in your database.

Thanks Namliam.

I usually do but this is an inheritted database that I am just tweaking to a bit so not worth the effort of renaiming tables and fields.

Cheers

Dave

 

[namliam]

I guess using SQL would be better if I were dealing with a large table because I would then limit the query to the 'Admin' records (I assume this would be "WHERE [LogIn].[Security Level] = 'Admin'") but in this instance the table is never likely to have more than about 15 records with probably 2 or 3 'Admin' records in them.

It is a matter of good practice as well, think of your successor finding a loop instead of a select going "What idiot did this?"

 

[Cotty42]

Regrettably I have to admit the common sense did get the better of me so I have replaced the table with an SQL statement as discussed and gone through and renamed all tables, fields, queries, forms etc. etc..

Its been a slow morning!

Thanks All

Dave