Seek Method does not go to record

[mx3gsr]

I set up a table called tblEmployeeTimecard and a form of the same table to allow the user to search for an employee name and enter a date in respective text fields. Seek is suppose to bring up any existing record that matches the name and date. If it doesn't find a match, it goes to a new record.

I added the multi-column index as I should. When it doesn't find a match, it does go to a new record. So SEEK seems to be working. But when it FINDS a match, nothing happens. My form is still at the first record. What am I doing wrong? How can I display the record that matches the user's entry? Here's what I have:


Private Sub txtDate_AfterUpdate()

Dim dbs As Database
Dim rst As Recordset

If IsNull([txtDate]) = False Then

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEmployeeTimecard", dbOpenTable)

rst.Index = "Employee_ID_Date"
rst.Seek "=", txtEmployee_ID, txtDate

If rst.NoMatch Then

'Adds new record if no matching Employee ID and Date in tblEmployeeTimecard
DoCmd.GoToRecord , , acNewRec

End If

rst.Close
Set dbs = Nothing
End If
End Sub

 

[JerichoJ]

The problem is that you never told the FORM to do anything if there is a match. You created a recordset object and told it (the recordset object) to look for the first record to match your criteria. Microsoft Access did what you told it to. The recordset object's current record is now pointing to the record matching your criteria. Unfortunately, this has nothing to do with the form.

You are correctly checking the NoMatch property to determine if a match was found. If NoMatch is true, you are telling the form to move to a new record. You need to provide code to tell the form what to do if NoMatch is False.

You would have to actually perform a second search with something like:

If rst.NoMatch Then
DoCmd.RunCommand acCmdRecordsGotoNew
Else
Me.RecordSetClone.FindFirst "[KeyField] = " & rst("KeyField&quot
If Not Me.RecordSetClone.NoMatch Then
Me.BookMark = Me.RecordSetClone.BookMark
End If
End If

When rst.NoMatch is "False", rst("KeyField&quot is pointing to the matching record. So, you can have the form find that record and move to it.

However, this is inefficient because you have now searched for this record twice.

Unless there are other things involved that you have not mentioned that require your current approach, I would recommend the following:

Dim mssql as String

mssql = "[Employee_ID] = " & me.txtEmployee_ID
mssql = mssql & " AND [EDate] = #" & me.txtDate & "#"
Me.RecordSetClone.FindFirst mssql
If Me.RecordSetClone.NoMatch Then
DoCmd.RunCommand acCmdRecordsGoToNew
Else
Me.BookMark = Me.RecordSetClone.BookMark
End if

A couple of things to keep in mind
1. If you go with my suggestion, you can remove your special index. This will help table performance because that is one less index it will have to maintain.

2. The seek method ONLY works with Table-Type recordsets that are physically located in the running .mdb file. Meaning, you can't open table-type recordsets on linked tables even if they are Access tables.

3. If you are dealing with a very large amount of data, you will probably notice that FindFirst performs slower than Seek. However, given 1 & 2 above, as well as the fact that you have to have the form do a second search after Seek finds the record you are looking for, you will have to make a judgement call on where you think the performance will be impacted the most.

Both methods have their pro's and con's.