I am looking for a quicker workaround for .findfirst when using Recordset Clone. The current code is:
Code:
Set rstClone = Me.RecordsetClone
Set dbs = CurrentDb
strFind = "MonthFrom = #" & Me.Go_To_Month & "# AND DiversionPointNum = " & Me.Go_To_DiversionPointNum
With rstClone
.FindFirst strFind
If .NoMatch Then
.AddNew
!MonthFrom = Me.Go_To_Month
!DiversionPointNum = Me.Go_To_DiversionPointNum
.Update
Me.Bookmark = .LastModified
Else
Me.Bookmark = .Bookmark
End If
End With
Just to give more context, the code worked fine with local access tables. But, when tables were moved to SQL and were linked in that's when the .FindFirst started to run super slow (2 seconds vs. 40 seconds) I tried to convert this process into a SQL query using the code below, but since the previous code was using recordsetClone and .Bookmark, doing the same thing with the SQL query did not give the same result.
Code:
strFind2 = "SELECT MonthFrom, DiversionPointNum FROM " & rstClone.Name & " where MonthFrom = #" & Me.Go_To_Month & "# And DiversionPointNum = " & Me.Go_To_DiversionPointNum
Set qdf1 = dbs.CreateQueryDef("", strFind2)
Set xyz = qdf1.OpenRecordset(dbOpenDynaset, dbSeeChanges)
Any information on .findfirst being an issue with linked tables or using a bookmark with recordsetClone would be helpful. Or if there's another workaround possible to do the same process without using .findfirst. I've already tried using .findnext, .filter and the results have been same as using .findfirst.