Return to the same record next time form is opened

[Harry Shmedlap]

The above great article on the Allen Browne site has one problem. I don't know if someone has posted it before but if the lookup is text rather than numeric then search is not found.
Here is a modified version:

Sub Form_Load()
Dim varID As Variant
Dim searchString As String
varID = DLookup("Value", "tblSys", "[Variable] = 'CustomerIDLast'")
If IsNumeric(varID) Then
searchString = "[CustomerID] = " & varID
Else
searchString = "[CustomerID] = " & "'" & varID & "'"
End If
With Me.RecordsetClone
.FindFirst searchString
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

 

[Dennisk]

why not change it to your specific requirement.
if you require a numneric lookup then remove the single quotes.

 

[missinglinq]

why not change it to your specific requirement.
if you require a numneric lookup then remove the single quotes.

Actually, Mr. Shmedlap neglected to mention the fact that Allen's code includes the line that appears in bold below:

Sub Form_Load()
Dim varID As Variant
Dim strDelim As String
'Note: If CustomerID field is a Text field (not a Number field), remove single quote at start of next line.
'strDelim = """"

 

[Harry Shmedlap]

I don't see that comment on Allen's website.
Where do you see it?

 

[missinglinq]

Sub Form_Load()
Dim varID As Variant
Dim strDelim As String
'Note: If CustomerID field is a Text field (not a Number field), remove single quote at start of next line.
'strDelim = """"

varID = DLookup("Value", "tblSys", "[Variable] = 'CustomerIDLast'")
If IsNumeric(varID) Then
With Me.RecordsetClone
.FindFirst "[CustomerID] = " & strDelim & varID & strDelim
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

http://allenbrowne.com/ser-18.html