Recordset Clone using Autonumber

[egibson]

I'm using a combo box on my form to populate the entire form with the
information the user selects in the combo box. I am having trouble with my
code and it is not finding the record. I'm not sure why this is happening.
My ID is an autonumber and I'm not sure if this is why or not. Any help
would be greatly appreciated. Here is my code:

Private Sub Combo216_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID]= " & Me![Combo216].Column(0) & ""
If Not rs.EOF Then
MsgBox "Record not found " & Me![Combo216].Column(0) & ""
Else
Me.Bookmark = rs.Bookmark
End If

Me!Combo216 = ""
Refresh

Me!Provider.SetFocus

End Sub

 

[Dirk Goldgar]

I'm using a combo box on my form to populate the entire form with the
information the user selects in the combo box. I am having trouble with
my
code and it is not finding the record. I'm not sure why this is
happening.
My ID is an autonumber and I'm not sure if this is why or not. Any help
would be greatly appreciated. Here is my code:

Private Sub Combo216_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID]= " & Me![Combo216].Column(0) & ""
If Not rs.EOF Then
MsgBox "Record not found " & Me![Combo216].Column(0) & ""
Else
Me.Bookmark = rs.Bookmark
End If

Me!Combo216 = ""
Refresh

Me!Provider.SetFocus

End Sub

Your code testing for a non-match is incorrect. Do this:

Set rs = Me.RecordsetClone
rs.FindFirst "[ID]= " & Me![Combo216].Column(0)
If rs.NoMatch Then
MsgBox "Record not found " & Me![Combo216].Column(0)
Else
Me.Bookmark = rs.Bookmark
End If

The code generated by the wizard in Access 2000 and later versions,
referring to the EOF property, is simply incorrect; and on top of that, you
added a "Not" in there, reversing the meaning.

 

[egibson]

Oh wonderful! It works! Thanks so much for your help!

I'm using a combo box on my form to populate the entire form with the
information the user selects in the combo box. I am having trouble with
my
code and it is not finding the record. I'm not sure why this is
happening.
My ID is an autonumber and I'm not sure if this is why or not. Any help
would be greatly appreciated. Here is my code:

Private Sub Combo216_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID]= " & Me![Combo216].Column(0) & ""
If Not rs.EOF Then
MsgBox "Record not found " & Me![Combo216].Column(0) & ""
Else
Me.Bookmark = rs.Bookmark
End If

Me!Combo216 = ""
Refresh

Me!Provider.SetFocus

End Sub

Your code testing for a non-match is incorrect. Do this:

Set rs = Me.RecordsetClone
rs.FindFirst "[ID]= " & Me![Combo216].Column(0)
If rs.NoMatch Then
MsgBox "Record not found " & Me![Combo216].Column(0)
Else
Me.Bookmark = rs.Bookmark
End If

The code generated by the wizard in Access 2000 and later versions,
referring to the EOF property, is simply incorrect; and on top of that, you
added a "Not" in there, reversing the meaning.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Marshall Barton]

I'm using a combo box on my form to populate the entire form with the
information the user selects in the combo box. I am having trouble with my
code and it is not finding the record. I'm not sure why this is happening.
My ID is an autonumber and I'm not sure if this is why or not. Any help
would be greatly appreciated. Here is my code:

Private Sub Combo216_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID]= " & Me![Combo216].Column(0) & ""
If Not rs.EOF Then
MsgBox "Record not found " & Me![Combo216].Column(0) & ""
Else
Me.Bookmark = rs.Bookmark
End If

Me!Combo216 = ""
Refresh

Me!Provider.SetFocus

End Sub

The check for EOF is inappropriate. Try using something
more like:

Private Sub Combo216_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[ID]= " & Me![Combo216].Column(0)
If .NoMatch Then
MsgBox "Record not found " &
Me![Combo216].Column(0) & ""
Else
Me.Bookmark = .Bookmark
End If
End With
Me!Combo216 = ""
Me!Provider.SetFocus

End Sub

Note that if the combo box's BoundColumn corresponds to the
ID field (i.e. it is 1), then you do not need to use
..Column(0)