Recordset Clone

[Ann]

I'm trying to follow an example that someone else set up
but my code is not working. I have a Company form and when
I dbl_click one of the fields, a form with a listbox pops
up. That all works fine but then in addition I would like
to select a record from the list box and then click a Find
button that would find the value in the parent form and
close itself. The error I get is that it's not a valid
bookmark. My code looks like:

Private Sub cmdFind_Click()
Dim rst As DAO.Recordset
'Find the record in the Parts form and close Find form
Set rst = Forms!frmParts.RecordsetClone
rst.FindFirst "[PartID] = " & "'" & lstFind & "'"
Forms!frmParts.Bookmark = rstBookmark
DoCmd.Close acForm, "frmFindPart"
Set rst = Nothing

End Sub

What am I missing?

 

[Sandra Daigle]

Hi Ann,

A few things that I noticed are 1) if Partid is numeric, then you don't need
to wrap the value in quotes 2) You aren't checking the NoMatch property of
the recordsetclone so if the find fails, you have no guarantee of a valid
bookmark. 3) Not a problem but you don't need the rst variable, you can use
With..End With and avoid the extra variable. 4) I think you had a typo -
rstBookmark should have been rst.bookmark (stumbled on that while modifying
code!). Here's the modified code:

Private Sub cmdFind_Click()
'Dim rst As DAO.Recordset
'Find the record in the Parts form and close Find form
' Set rst = Forms!frmParts.RecordsetClone
with forms!frmParts.recordsetclone
.FindFirst "[PartID] = " & lstFind
if not .nomatch then
Forms!frmParts.Bookmark = .Bookmark
endif
end with
DoCmd.Close acForm, "frmFindPart"
' Set rst = Nothing

End Sub

 

[Ann]

Sandra,
Wow! I'm impressed with the speed and thoroughness of your
response. The *!#*@# typo was my main problem but I
appreciate the other pointers as well. As you can tell I'm
very much in a learning phase.
Thanks again.

-----Original Message-----
Hi Ann,

A few things that I noticed are 1) if Partid is numeric, then you don't need
to wrap the value in quotes 2) You aren't checking the NoMatch property of
the recordsetclone so if the find fails, you have no guarantee of a valid
bookmark. 3) Not a problem but you don't need the rst variable, you can use
With..End With and avoid the extra variable. 4) I think you had a typo -
rstBookmark should have been rst.bookmark (stumbled on that while modifying
code!). Here's the modified code:

Private Sub cmdFind_Click()
'Dim rst As DAO.Recordset
'Find the record in the Parts form and close Find form
' Set rst = Forms!frmParts.RecordsetClone
with forms!frmParts.recordsetclone
.FindFirst "[PartID] = " & lstFind
if not .nomatch then
Forms!frmParts.Bookmark = .Bookmark
endif
end with
DoCmd.Close acForm, "frmFindPart"
' Set rst = Nothing

End Sub





--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I'm trying to follow an example that someone else set up
but my code is not working. I have a Company form and when
I dbl_click one of the fields, a form with a listbox pops
up. That all works fine but then in addition I would like
to select a record from the list box and then click a Find
button that would find the value in the parent form and
close itself. The error I get is that it's not a valid
bookmark. My code looks like:

Private Sub cmdFind_Click()
Dim rst As DAO.Recordset
'Find the record in the Parts form and close Find form
Set rst = Forms!frmParts.RecordsetClone
rst.FindFirst "[PartID] = " & "'" & lstFind & "'"
Forms!frmParts.Bookmark = rstBookmark
DoCmd.Close acForm, "frmFindPart"
Set rst = Nothing

End Sub

What am I missing?

.