Im using the following code from a listbox to find a record, the problem that I have is that the list box has a FirstNamettxt coloum and a Lastnametxt coloum. As I have some people with the same First name it wont find any others only the first one in the DB i.e. John Smith and John Nokes, if John Smith was the first person to be entered clickingon John Nokes will take me to John Smiths record.
Code:
Private Sub List93_AfterUpdate()
'Findtherecord that matches the control
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[FirstNametxt] = '" & Me![List93] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
I'm just guessing for the Column indexes that your first and last names are 1st and 2nd columns, respectively.
If FindFirst doesn't work with multiple criteria (I don't recall if it does or not), then I'd probably just assign a full-blown SELECT statement to a recordset and synchronize to your Clone.
Private Sub List93_AfterUpdate()
'Findtherecord that matches the control
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[FirstNametxt] = '" & Me![List93] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
I've never understood why people code find operations on the RecordsetClone in this manner (and you've code it wrong to begin with, as rs.EOF is not the right test -- you want to test rs.NoMatch). Much simpler:
Code:
With Me.Recordset.Clone
.FindFirst "[FirstNametxt] = " & Chr(34) & Me!List93 & Chr(34)
If Not .NoMatch Then
If Me.Dirty Then Me.Dirty = False
Me.Bookmark = .Bookmark
End If
End With
Several comments:
1. no need to initialize a database variable to point to a recordset that already exists.
2. even if you did do it, you would need to set it to Nothing after you're done using it.
3. it's better to use double quotes for delimiters for text criteria as single quotes (apostrophes) are more likely to occur in names than double quotes.
4. testing for rs.EOF is the wrong test. You should test whether .FindFirst returned a match (the .NoMatch) property).
5. you should force a save of an edited record before moving to the new record, because there's a rare bug that can cause errors produced in the implicit save to be lost during a bookmark navigation operation. That is, when you set Me.Bookmark = Me.RecordsetClone.Bookmark, the record you started from will be saved implicitly, and if errors occur in the save (e.g., data in the record violates validation rules), they very, very occasionally are not report. By saving explicitly, if needed, you force the save operation to occur before the record pointer is moved, and thus you aren't in danger of having errors lost.
Hi and thanks for your comments. The only thing I will say in my defence is, "Blame Microsoft for this, as this is alll taken from the wizzard", granted it may not be he best way,but its the only way I knew. Again thanks for your comments!
Hi and thanks for your comments. The only thing I will say in my defence is, "Blame Microsoft for this, as this is alll taken from the wizzard", granted it may not be he best way,but its the only way I knew. Again thanks for your comments!
Wow, you're right. That's really bad code -- terribly, absolutely dreadful. I haven't used the combo box wizard since Access 97 so hadn't noticed that they'd screwed it up.
Yep they messed with it when they (MS) tried to suppress DAO and claim ADO was the way to go (A2000). The wizard has been cranking out inappropriate code ever since ....
For what its worth, the .EOF is how you check for a successful match in an ADO recordset, but .FindFirst is not a valid method of an ADO recordset object ... so ... MS combined two different object models to create one block of bad code .... just as David has indicated.
