DAO Code taking a long time

[Castanz]

When I execute this code it takes about a 30 seconds to execute. The first part of the code goes through about 1500 records looking to see if NSN and command match.

Is there a better way to do a two factor search or filter?

Private Sub ChangeNSN_Click()
Dim selectedCommand As String
Dim NSN, NOMENCLATURE, PartNumber As String
Dim rst As ADODB.Recordset
Dim strCriteria As String
Dim Command As String

Command = GetCmd()

Set rst = New ADODB.Recordset

With rst
    .Open "AuthorizedUserList", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    .MoveFirst
       
   Do While Not .EOF
        If IsFound(.Fields("NSN"), Command) = True Then 'IF Record is selected
          .Fields("selected") = -1
        Else
          .Fields("selected") = 0
        End If
    .MoveNext
    Loop
          
End With
rst.close
Set rst = Nothing
DoCmd.close
DoCmd.OpenForm "frmChooseQuickList"
End Sub

Function IsFound(NSN As String, Command As String) As Boolean
Dim Db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

IsFound = False
  strSQL = "SELECT * FROM QuickNSN Where NSN = '" & NSN & "' AND COMMAND = '" & Command & "'" 
Set Db = CurrentDb()
Set rst = Db.OpenRecordset(strSQL, dbOpenDynaset)

With rst         
    If .EOF Then
     IsFound = False
    Else
     IsFound = True    
    End If
End With

rst.close
Set rst = Nothing
End Function

 

[Andrzejek]

Not tested, just an idea....

Private Sub ChangeNSN_Click()
Dim selectedCommand As String
Dim NSN AS String, NOMENCLATURE As String, PartNumber As String
Dim rst As ADODB.Recordset
Dim rst1 As ADODB.Recordset
Dim strCriteria As String
Dim Command As String

Command = GetCmd()

Set rst = New ADODB.Recordset

strSQL = "SELECT * FROM QuickNSN" 
Set Db = CurrentDb()
Set rst1 = Db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
    .Open "AuthorizedUserList", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    .MoveFirst
       
   Do While Not .EOF
        rst1.Filter = "NSN = '" & .Fields("NSN") & "' AND COMMAND = '" & Command & "'" 
        If rst1.RecordCount = 0 Then
            .Fields("selected") = 0
        Else
            .Fields("selected") = -1
        End IF

       .MoveNext
    Loop
          
End With
rst.close
Set rst = Nothing

rst1.close
Set rst1 = Nothing

DoCmd.close
DoCmd.OpenForm "frmChooseQuickList"
End Sub

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[strongm]

Erm - surely we can do this with a single update query, eg:

[blue]Private Sub ChangeNSN_Click()
    Dim strSQL As String
    Dim Command As String
    Dim db As Database
    
    Set db = CurrentDb()
    
    Command = GetCmd() 

    strSQL = "UPDATE AuthorizedUserList INNER JOIN QuickNSN ON AuthorizedUserList.NSN = QuickNSN.NSN SET AuthorizedUserList.Selected = QuickNSN.Command='" & Command & "'"
    db.Execute strSQL
End Sub[/blue]

 

[Castanz]

Andrzejek, thank you for your reply. For some reason the filter option didn't work no matter what I did.

Here is the revised code. The findfirst method seemed to do great.

Private Sub ChangeNSN_Click()
Dim selectedCommand, strSQL, strSQL1 As String
Dim NSN As String, NOMENCLATURE As String, PartNumber As String
Dim rst As ADODB.Recordset
Dim strCriteria As String
Dim Command As String
Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset

Command = GetCmd()

Set dbs = CurrentDb()

Set rst = New ADODB.Recordset

Set rst1 = dbs.OpenRecordset("QuickNSN", dbOpenDynaset)

strSQL = "SELECT [NSN],[Command] FROM QuickNSN"

With rst
    .Open "AuthorizedUserList", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    .MoveFirst
       
   Do While Not .EOF
        NSN = .Fields("NSN")
        rst1.FindFirst "[NSN] = '" & NSN & "' AND Command = '" & Command & "'"
        
        If rst1.NoMatch = True Then
            .Fields("selected") = 0
        Else
            .Fields("selected") = -1
        End If

       .MoveNext
   Loop
          
End With
rst.Close
Set rst = Nothing

rst1.Close
Set rst1 = Nothing

DoCmd.Close
DoCmd.OpenForm "frmChooseQuickList"
End Sub

 

[Andrzejek]

If it works faster - that's great.
But I would take a closer look at strongm's post

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.