Find record on form VBA

[Phred]

Access 2007, VBA, find record on form. The database tracks PINs (Property Index Numbers) and related data about the property.

I have a Modal form "Edit_Frame_FRM". It acts as a frame containing two sub forms, "Edit1_Frm" and "Edit2_Frm".

I have a button in each row of the "Edit1_Frm. You click the button on Edit1_Frm and it grabs the PIN from that record and finds the corresponding PIN in Edit2_FRM.

Both forms are always open side-by-side. Please see attached screen snap.

The following code from PBaldy works perfect.

DoCmd.OpenForm "Edit2_FRM", , , "PIN = '" & Me.PIN & "'"

Except it opens a new form. I can't figure out how to make it find the record in the already open Edit2_FRM.

Thanks
Fred

 

[pbaldy]

You could adapt this:

http://www.baldyweb.com/Bookmark.htm

where instead of opening another form you just manipulate the bookmark of the other subform.

 

[Phred]

Thanks, I still have a problem.

The PIN field is a string not a number. The variable picks up the PIN good. But I think something is wrong with my reference to the other form although I can't find anything wrong with it.


Dim rs As Object

Dim stBookmark As String

'set a variable to the current record

stBookmark = Me.PIN

'open the new form

' DoCmd.OpenForm "frmEmployeesDetail"

'take it to the selected record

Set rs = Forms!Edit_Frame_FRM!Edit2_FRM.RecordsetClone

'GET ERROR - Runtime error '438'
'Object doesn't support this property or method.

rs.FindFirst "PIN = " & stBookmark
Set rs = Nothing

Thanks, Fred

 

[MLUCKHAM]

May be missing something, but searching in the recordsetclone is one thing, but you then need to set the form recordset to the record you have found. So the line missing at the end is something like: -

Forms.Bookmark = rs.bookmark which will sink the recordsetclone to the real recordset on the form. You need to do this otherwise you are only searching a recordset in memory.

The Bookmark in this is the actually recordset bookmark (not a variable).

 

[pbaldy]

This might need to be:

Set rs = Forms!Edit_Frame_FRM!Edit2_FRM.Form.RecordsetClone

and as noted you need something like this:

Forms!Edit_Frame_FRM!Edit2_FRM.Form.Bookmark = rs.Bookmark

 

[Phred]

Dim rs As Object

Dim stBookmark As String

stBookmark = Me.PIN

Set rs = Forms!Edit_Frame_FRM!Edit2_FRM.Form.RecordsetClone

Forms!Edit_Frame_FRM!Edit2_FRM.Form.Bookmark = rs.Bookmark

rs.FindFirst "PIN = " & stBookmark GET DATA TYPE MISMATCH.

"Run Time Error 3464 Data Type mismatch in criteria expression."

The PIN field in both forms is Text. the stBookmark = Me.PIN is picking up the correct PIN in the VB watch. And the PIN to find in Edit2_FRM does exist. I have tried some variations on the "PIN = " & stBookmark to no luck.

Set rs = Nothing

 

[pbaldy]

Try

rs.FindFirst "PIN = '" & stBookmark & "'"

 

[Phred]

Dim rs As Object
Dim stBookmark As String
stBookmark = Me.PIN

Set rs = Forms!Edit_Frame_FRM!Edit2_FRM.Form.RecordsetClone

Forms!Edit_Frame_FRM!Edit2_FRM.Form.Bookmark = rs.Bookmark

rs.FindFirst "PIN = '" & stBookmark & "'"

The above code eliminated the Data Type mismatch error. The code runs completely but nothing changes on the screen. It does not go to the rs.FindFirst "PIN" which does exist. I have tried refreshing the form but nothing selects the corresponding PIN on the form.

Set rs = Nothing

 

[Phred]

Dim rs As Object

Dim stBookmark As String

stBookmark = Me.PIN

Set rs = Forms!Edit_Frame_FRM!Edit2_FRM.Form.RecordsetClone

Forms!Edit_Frame_FRM!Edit2_FRM.Form.Bookmark = rs.Bookmark

Now the above line errors out with Error 3021 No Current Record.

rs.FindFirst "PIN = '" & stBookmark & "'"

Set rs = Nothing

 

[pbaldy]

You dropped the FindFirst line. Can you post the db to play with?

 

[MLUCKHAM]

Find the record first THEN synch your recordsets. E.g. The bookmark synch needs to come after you have used your find first.

 

[MLUCKHAM]

Dim rs As Recordset

Dim stBookmark As String

stBookmark = Me.PIN

Set rs = Forms!Edit_Frame_FRM!Edit2_FRM.Form.RecordsetClone

rs.FindFirst "PIN = '" & stBookmark & "'"

Forms!Edit_Frame_FRM!Edit2_FRM.Form.Bookmark = rs.Bookmark

Set rs = Nothing

 

[Phred]

Thank you that did the trick I moved up the FindFirst line. Thank you both for the help. Can I bother you with one more question? I wanted to write the exact reverse of this so that if you click the button on Edit2_FRM it finds the corresponding record in Edit1_FRM.

So I copied the code that works and reversed the references to the forms. But now it errors: "Cannot find Edit1_FRM." Every thing else is the same.

Dim rs As Object
Dim stBookmark As String
stBookmark = Me.PIN

Set rs = Forms!Edit_Frame_FRM!Edit1_FRM.Form.RecordsetClone
rs.FindFirst "PIN = '" & stBookmark & "'"

Forms!Edit_Frame_FRM!Edit1_FRM.Form.Bookmark = rs.Bookmark
Set rs = Nothing

 

[MLUCKHAM]

You need to double check the name of the sub form container. Don't forget the name Edit1_Frm in Forms!Edit_Frame_FRM!Edit1_FRM.Form.RecordsetClone is not the name of the form, but the name of the sub form control. They can and often are the same, but if for example you copied the control on the form it would name it something different.

Check the name of this control - I bet you the control is not called Edit1_Frm...

Code looks fine so this has to be the explanation...

 

[Phred]

A generous thank you to MLUCKHAM and pbaldy. Your help is so appreciated.