Goto a specific record is a form

[Redbeard]

I have a button on a popup form that when clicked runs a code to duplicate the current record that I am viewing on my main form. It first asks me to enter a new number (primary key) into a pop-up box and then duplicates the record replacing the primary key with the new number. I use this in cases where the next record only requires a few field changes, so I don’t have to retype the hold record. The problem is that when the code runs to do this, it ends by filtering to the duplicated record. I would like it to go to that record without filtering out all the other records. That way I can move back a couple of records if I need to without un-filtering and then re-finding that record. The code at the end that filters to the new record is:

DoCmd.OpenForm "frmDescription", acNormal, , "[Accession Number]='" + MyValue + "'"

MyValue is the new record number.
I did not write this code and I do not understand code very well but am slowly learning.
I think that I need to replace it with a Findfirst code but am not sure?
I have been working with this code but have had no luck…

DoCmd.Close acForm, "frmDescription"
DoCmd.OpenForm "frmDescription"
 
Dim rst As Recordset
            Dim strSearchName As String
            Set rst = Me.RecordsetClone
            strSearchName = Str([Accession Number] = MyValue)
            rst.FindFirst "Accession Number = " & strSearchName
                     Me.Bookmark = rst.Bookmark

Can anyone one help?

[patjones]

Hi Redbeard,

Is MyValue a number or a string?

Pat

[Redbeard]

It is a string

Dim MyValue As String 

'represents the new accession number

[patjones]

When you do a comparison for string values, you need to enclose the target value in quotes. I would eliminate line 7, and write line 8 like this:

rst.FindFirst "[Accession Number] = '" & MyValue & "'"

Try it and let us know how it works (or doesn't, as the case may be).

Pat

[Redbeard]

Still an error… Run Time Error 246: The expression you entered refers to an object that is closed or does not exist.
When I go to debug it highlights this line:

Set rst = Me.RecordsetClone

[patjones]

What form did you drop this code into? The "Me." formalism will only work if the code is in the module for the form that you want to populate.

Pat

[Redbeard]

Thanks Pat! The "Me." was my problem, I changed it to

Dim rst As Recordset
            Set rst = Forms!frmDescription.RecordsetClone
            rst.FindFirst "[Accession Number] = '" & MyValue & "'"
 
                Forms!frmDescription.Bookmark = rst.Bookmark

It now runs Perfectly!

Thanks!

[patjones]

That's no problem. Let me know if there are any other issues.

[NeoPa]

For something pretty similar I use a technique whereby I simply extend the existing filter to include the newly added record. I include an example to illustrate. In the example [ClipboardID] is the PK of the table being maintained (in this case a numeric value) :

Private Sub Form_AfterInsert()
    Me.Filter = Me.Filter & " OR ([ClipboardID]=" & lngID & ")"
End Sub