[AccessD] Find First in an Array?

Shamil!  I forgot all about Seek.  This is one of those rare occasions where
I'm working with a non-linked table. But this table is in the front end -
local to the  user - so each user can have their own language. 

I would have to seek on two fields however - the form name and the control
name.  Should I make a concatenated field, then?

Best, 


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Salakhetdinov
Shamil
Sent: Thursday, February 19, 2009 3:33 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Find First in an Array?

Here are corrected DAO's .Seek code samples (sorry, previous version has had
a couple of bugs):

Public Function testSeek1()
Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Products", dbOpenTable)
    With rst
       .Index = "ProductName"
       .Seek "=", "Aniseed Syrup"
        If (.NoMatch) Then
           Debug.Print "Not Found"
        Else
           Debug.Print "Found"
        End If
    End With
End Function

Public Function testSeek2()
'Prepequisites:
' Compound index named 'Contact' created ' for ContactTitle and ContactName
fields ' of Customers table Dim dbs As DAO.Database Dim rst As DAO.Recordset
Dim sql As String Dim avar As Variant Dim i As Long Dim rowCount As Long Dim
foundCount As Long Dim notFoundCount As Long Dim cyclesCount As Long Dim k
As Integer
    cyclesCount = 1000

    Set dbs = CurrentDb
    sql = "select ContactTitle, ContactName from Customers"
    Set rst = dbs.OpenRecordset(sql, dbOpenDynaset)
    rowCount = dbs.TableDefs("Customers").RecordCount
    avar = rst.GetRows(rowCount)
    Debug.Print "Started at = " & Now
    Debug.Print "Row count = " & rowCount
    foundCount = 0
    notFoundCount = 0
    Set rst = dbs.OpenRecordset("Customers", dbOpenTable)
    For k = 1 To cyclesCount Step 1
        For i = LBound(avar, 2) To UBound(avar, 2) Step 1
            With rst
               .Index = "Contact"
               .Seek "=", avar(0, i), avar(1, i)
                If (.NoMatch) Then
                   notFoundCount = notFoundCount + 1
                Else
                   foundCount = foundCount + 1
                End If
            End With
        Next i
    Next k
    rst.Close
    Set rst = Nothing
    Debug.Print "Found = " & foundCount
    Debug.Print "Not found = " & notFoundCount
    Debug.Print "Finished at = " & Now
'
' test results:
'Started at = 19.02.2009 13:54:18
'Row count = 91
'found = 91000
'Not found = 0
'Finished at = 19.02.2009 13:54:19
    
End Function


-----Original Message-----
From: Salakhetdinov Shamil <mcp2004 at mail.ru>
To: Access Developers discussion and problem
solving<accessd at databaseadvisors.com>
Date: Thu, 19 Feb 2009 14:01:55 +0300
Subject: Re: [AccessD] Find First in an Array?

> 
> Hi Rocky,
> 
> Good old DAO's .Seek should do the trick.
> You'll have to create dedicated compound index for it.
> 
> --
> Shamil
> 
<<< tail trimmed>>>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com