Open Form with Matching Data from Separate Form

[pwhite1048]

I have a form on which I want to double-click a value in a field on the form and open a different form and show the matching 'record'.

Form1 contains an ID field where I've placed an On Dbl Click macro to OpenForm, Form name is Form2, Where is [Forms]![Form2]![ID]=[Forms]![Form1]![ID]. When I double-click the ID in Form1, it opens a filtered Form2, but it's a blank form (Record 1 of 1 - filtered - all ready for data entry). I don't want that record, I want the one where the IDs match. Does it make a difference that on Form1, the ID field is an AutoNumber and is unique?

I also tried Where [Forms]![Form1]![ID], but that gets me the first record in Form2, filtered but with all records in the filtering.

I played around with some other methods, and got one where it would come up and ask for the ID, and if I typed it, it would open that record, but I want it to do it automatically.

All help appreciated....

Trish

 

[PHV]

got one where it would come up and ask for the ID
Can you please elaborate this one ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MaryTee]

I needed to do something similar--open a second form that displayed information based on the current record on the first form. Rather than use the click event on the control(in my case it was UserID), I used a command button that accomplished the same thing. The linking field that the second form used as a "filter" was the UserID on form 1. Here is the code for the command button:

Private Sub cmdbutton_Click()
On Error GoTo Err_cmdbutton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmYour_PoPup_form"

stLinkCriteria = "[UserID]=" & Me![UserID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdbutton_Click:
Exit Sub

Err_cmdbutton_Click:
MsgBox Err.Description
Resume Exit_cmdbutton_Click

End Sub

 

[pwhite1048]

TO: PHV
got one where it would come up and ask for the ID
Can you please elaborate this one ?

I took out the WHERE and put in a Filter containing:
select * from Form2
where Form2.ID = Form1.ID

When I double-click on an ID in Form1, I get an Enter Parameter Value dialog asking for Form1.ID. When I enter an ID and click OK, it opens that record.

To: MaryTee
I would prefer to use the On Dbl Click as I am displaying multiple records on the form, but you've given me some ideas I will pursue. Thanks.

Trish

 

[Fekri]

Hi,

the mistake maybe because of this:
where [Form2]![ID}=[Forms1]![ID]

bye

 

[pwhite1048]

I did something similar to MaryTee's suggestion, but kept it on the On Dbl Click. It works!

Private Sub BugID_DblClick(Cancel As Integer)

Dim strDocName As String, strLinkCriteria As String

strDocName = "Form2"
strLinkCriteria = "[ID] = Forms![Form1]![ID]"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit_BugID_DblClick:
Exit Sub

On to other challenges - thanks all for your help.