How to open a form to a specific record?

[Jamesremec]

I'd like to make it so that when viewing a particular datasheet form, you can double-click on one of the records and open a form that has all the data from that record summarized for easy viewing and editing (so you don't have to scroll to the right on the table.) I understand how to start the macro, I have it set up so that after double clicking on one of the cells, it opens the proper form. Only problem is, I can't get it to open the form to the same record as the one I clicked on in the datasheet (it just opens to the first record.) I'm sure it's just a matter of one or two commands that I don't know. By the way, the table has two primary keys.

Any help that you could offer would be greatly appreciated!

Thanks,
James

 

[DBL]

On the double click event of the form put:

Dim stdocname As String
Dim strData As String


strData = "3," & Me![primarykeyfieldname]

stdocname = "formtoopenname"
DoCmd.OpenForm stdocname, , , , , , strData

On the On Open event of the second form put

If Mid(Me.OpenArgs, 2, 1) = "," Then
Dim rs As Object
Set rs = Me.RecordsetCloners.FindFirst "[PrimaryKeyField] = " & Val(Right(Me.OpenArgs, Len(Me.OpenArgs) - 2))
Me.Bookmark = rs.Bookmark

not sure how do to it as a Macro

Shout if you get stuck

 

[Jamesremec]

DBL

Thanks for the quick reply. Only problem is, I am extremely new at Access (taught myself how to use it 5 weeks ago, thats why I was trying to use a macro) and have never done VBS code. Could you explain a little bit about what your wrote above? I'm pretty confused with all the variables.

Thank you very much,
James

 

[Jamesremec]

DBL,

I tried using the code you gave me and got an error on the second form's code.

Where you said, "Set rs = Me.RecordsetCloners.FindFirst "[PrimaryKeyField] = " &Val(Right(Me.OpenArgs,Len(Me.OpenArgs)-2))

I get an error that highlights
"[Saleen #] = "
Saying "Expected: End of statement"

Any ideas?

Thanks!

 

[DBL]

are you using an autonumber as your primary key field?

 

[Jamesremec]

I'm actually doing this for two tables. In one the primary key is a serial number. The other has two primary keys, a serial number and an owner number.

 

[DBL]

[PrimaryKeyField] needs to be the name of the linking field between the two forms. So if [autonumber] on the first form is the same as [serialnumber] on the second form then those are the two fields you should specify in the code.

Let me know how you get on with that.

 

[Jamesremec]

Well, I tried fooling around with it and that line of code just doesn't want to work. "Saleen #" is what I was putting in the brackets. It's the primary key, used for both forms. Like I said, when I put that into the primary key brackets, I get an error, "Expected: End of Statement" Any ideas?