Double Click to Open record

[mpquin]

I am very new the complicated world of Access and am trying to set up a database for contacts. Here's what I would like to happen:

I have a form (frmContactsAll) with a subform (see pic). The subform will have multiple records (contacts) for each company. The subform contains an overview of the client and I have another form (frmContactDetail) which holds several more fields of data for each specific client.

I would like to be able to double click on the clients name in the frmContactsAll form and it will open the specific record in the frmContactDetail form. I was able to set it up to open the frmContactDetail, but it is the first record of all contacts, for all firms.

I am assuming that I would need to create a query, but I need to know how to reference the record that I am double clicking on. I do not want a "pop up" to begin the query.

Thanks,
Mike

 

[Rich]

Use the Wizard to add a command button, simply copy the code it creates and add it to the double click event of your textbox. Delete the button and its code after you've gotten it to work

 

[ansentry]

Is frmContactDetail based on the same table as the subform?

If so then you can use code similar to below to open the second form when you click on a control in the subform. Put the code in the OnClick event of the control (eg ClientName) on the subform.

Change the "blue" to your control names. This code assumes that you are using a number as your primary key. If both form are based on the same table this the primary keys will be the same (e.g ContactDetailsID). Need further help post back.

    Dim strDocName As String
    Dim strCriteria As String

    strDocName = "FrmContactDetail"
    
    strCriteria = "[[COLOR="Blue"]FrmContractDetailsPrimaryKey[/COLOR]]=" & Me![[COLOR="Blue"]SubFormPrimaryKey[/COLOR]]
    DoCmd.OpenForm stDocName, , , strCriteria

 

[rolaaus]

What I like to do - and is probably not commonly done, when I want to open a 2nd form (typically a detail form) from a starting form (usually a list type form with limited displayed data in grid type format), I use the OpenArgs argument when calling the 2nd form.

Basically, in your On_Current of form #1, you would use

DoCmd.OpenForm FormName:="myForm", OpenArgs:=me.recordsource.IDfield

Then, in the On_open event of Form #2, you can check for something within the OpenArgs by writing

me.recordsource = "Select * from myTable WHERE IDfield = " & me.OpenArgs

I have, in the past, sometimes had to open a secondary form using differing criteria, if you find yourself in this situation, then you can use a string manipulation technique to provide mutliple On_Open procedures, like this

Select case Left(me.OpenArgs)
case 1
  me.recordsource = "Select * from myTable WHERE IDfield = " & Right(me.OpenArgs, len(me.OpenArgs)-1)
case 2
  'do something else
end select