dblClick event on continuous form subform

[metrodub]

I have a form (frmVendorManagement) that has a combobox(cboCompanyName)with a list of Vendors/Companies in it. When a user chooses a company from the combobox, a subform (sfrmPartners) displays the employees (in a continuous form) for the chosen company.

The Link Child Field is tblCompany.CompanyID and the Link Master Field is cboCompanyName. The tables share a many to many relationship.

Everything works fine and the appropriate employees are shown in the subform.

What I would like to do, and seem to be getting errors on, is to allow the user to double click on an employees name and have their information show in a new form (frmPartnerNew).

The error that I am getting is:

The specified field '[PartnerID]' could refer to more than one table listed in the FROM clause of your SQL statement

Here is my SQL statement for the subform:

SELECT tblPartners.PartnerID, tblCompany.CompanyID, tblPartners.PFName & " " & tblPartners.PLName AS Partner, tblPartners.Title FROM tblPartners INNER JOIN (tblCompany INNER JOIN tblPartnerCompany ON tblCompany.CompanyID=tblPartnerCompany.CompanyID) ON tblPartners.PartnerID=tblPartnerCompany.PartnerID ORDER BY tblPartners.PLName;

And the DblClick Event Code:

Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmPartnerNew"
    stLinkCriteria = "[PartnerID]=" & [Forms]![frmVendorManagement]![sfrmPartners].Form![PartnerID]

    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "frmVendorManagement"

My table structure is set up as such:
tblCompany
CompanyID
CompanyName
Address1
etc

tblPartnerCompany
PartnerCompanyID
CompanyID
PartnerID

tblPartner
PartnerID
PFName
PLName
Address1
etc.

Any suggestions or ideas as to how to go about fixing this error?

Thanks in advance!

 

[PHV]

You may try this:
stLinkCriteria = "tblPartners.PartnerID=" & [Forms]![frmVendorManagement]![sfrmPartners].Form![PartnerID]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886