Link to home
Create AccountLog in
Avatar of jaguar5554
jaguar5554Flag for United States of America

asked on

Open Form based on criteria from subform

Hello again~ I have a form with a list box that is populated as follows:

SELECT tblContactInfo.ContactName, tblSiteInfo.SiteName, tblContactInfo.ContactID, tblSiteInfo.SiteID
FROM tblSiteInfo INNER JOIN tblContactInfo ON tblSiteInfo.SiteID = tblContactInfo.SiteID;

(only the ContactName and SiteName (respectively) are visible in the list box)

Note: The ContactName and ContactID (child records to tblSiteInfo) are located on the subform. Also, a contact can be associated with more than one Site record.

What syntax do I use that will open a form (frmSiteInfo) to the specific record based on the Contact/Site (located in the subform (frmContactInfo subform) selection from the list box?

I read every post on your site regarding opening forms based on subform criteria and could not conform any solutions to what I am trying to accomplish -- your help is much appreciated.
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Assuming the code is located in the double-click event of the listbox on the subform:

Docmd.OpenForm "frmSiteInfo", WhereCondition:= "SiteID = " & Me.lstYourListboxName.Column(3)

Open in new window


Double-check the form and field names and also replace lstYourListboxName with the actual listbox name.
Avatar of jaguar5554

ASKER

Ok -- it opened the form which is 1000 times further than I got (thank you thank); however, (and I probably did not make myself very clear -- sorry), I would like the form to also find the contact record as well. So the form would open to the Site and specific Contact record based on the list box selection.  Can you help?
Try this:


Docmd.OpenForm "frmSiteInfo", WhereCondition:= "ContactID = " & Me.lstYourListboxName.Column(2) &  " AND SiteID = " & Me.lstYourListboxName.Column(3)

Open in new window

It's prompting me for the ContactID -- probably because that field resides on the Subform (frmContactInfo subform). Any thoughts?
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Works beautifully. You are a brain. Thank you so very much. (This site is awesome).
Glad to help :-)