bookmark Problem

[Steve]

Greetings:

I am using the following code to allow a user to display a particular record
by clicking a particular record in a listbox.

Private Sub lbxProblem_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Forms!frmMaster!.Recordset.Clone
rs.FindFirst "[intProblemID] = " & Str(Nz(Me![lbxProblem], 0))
If Not rs.EOF Then Forms!frmMaster!fsubOpen!fsubHPI.Form.Bookmark =
rs.Bookmark

End Sub

This code works fine so long as there is a matching record. But it can be
that lbxProblem contains records that are not present in

"Forms!frmMaster!.Recordset.Clone".

In that case, I would think that the phrase "if not rs.EOF" would evaluate
to false, or in other words rs.EOF would be true but this does not appear to
be the case. Even when the intProblemID is not in the RecordsetClone,

"Forms!frmMaster!fsubOpen!fsubHPI.Form.Bookmark" still runs.

I would like to be able to put code in as follows:

If rs.EOF = true then 'intProblemID does not exist in current recordset
' do something
End if

but again even when intProblemID does not exist in the record set rs.EOF
still is considered false and the 'do something part of the code does not
run. What am I missing here? Thanks for any help.

 

[Allen Browne]

Suggestions:
1. You searched the main form's recordsetclone, but matched the bookmark of
a different recordset (the subsubform's.) That's not reliable.

2. To get at the form inside a subform control, use the .Form bit each time
(not just at the end of the subsubform reference.)

3. Use a specific type of recordset, and check NoMatch rather than EOF.

4. I find it helps to avoid weird errors if you explicitly save.

Private Sub lbxProblem_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Dim strWhere As String

If Not IsNull(Me.lbxProblem) Then
With Forms!frmMaster!subOpen.Form!fsubHPI.Form
If .Dirty Then .Dirty = False 'save any edits
Set rs = .RecordsetClone
strWhere = "[intProblemID] = " & Me.lbxProblem
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found."
Else
.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End With
End If
End Sub

 

[Steve]

Thanks! That works great! Much appreciated.
--
Steve

Suggestions:
1. You searched the main form's recordsetclone, but matched the bookmark of
a different recordset (the subsubform's.) That's not reliable.

2. To get at the form inside a subform control, use the .Form bit each time
(not just at the end of the subsubform reference.)

3. Use a specific type of recordset, and check NoMatch rather than EOF.

4. I find it helps to avoid weird errors if you explicitly save.

Private Sub lbxProblem_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Dim strWhere As String

If Not IsNull(Me.lbxProblem) Then
With Forms!frmMaster!subOpen.Form!fsubHPI.Form
If .Dirty Then .Dirty = False 'save any edits
Set rs = .RecordsetClone
strWhere = "[intProblemID] = " & Me.lbxProblem
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found."
Else
.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End With
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Greetings:

I am using the following code to allow a user to display a particular
record
by clicking a particular record in a listbox.

Private Sub lbxProblem_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Forms!frmMaster!.Recordset.Clone
rs.FindFirst "[intProblemID] = " & Str(Nz(Me![lbxProblem], 0))
If Not rs.EOF Then Forms!frmMaster!fsubOpen!fsubHPI.Form.Bookmark =
rs.Bookmark

End Sub

This code works fine so long as there is a matching record. But it can be
that lbxProblem contains records that are not present in

"Forms!frmMaster!.Recordset.Clone".

In that case, I would think that the phrase "if not rs.EOF" would evaluate
to false, or in other words rs.EOF would be true but this does not appear
to
be the case. Even when the intProblemID is not in the RecordsetClone,

"Forms!frmMaster!fsubOpen!fsubHPI.Form.Bookmark" still runs.

I would like to be able to put code in as follows:

If rs.EOF = true then 'intProblemID does not exist in current recordset
' do something
End if

but again even when intProblemID does not exist in the record set rs.EOF
still is considered false and the 'do something part of the code does not
run. What am I missing here? Thanks for any help.

.