Open PopUp form with criteria from Subform and criteria in Main fo

E

Emma Aumack

Hi all,

I have a main form called LOC_maintenance, a subform called
Frm_MultActSelect and a Popup form called Frm_LUComplianceHistory. The
LOC_Maintenance forms fields are as follows:

LOC_GPO (a combo box)
GPO_Name (autofills based on =Column(1) of LOC_GPO)
Group_Prod_Cat (Autofills based on Column(2) of LOC_GPO)

Subform Frm_MultActSelect is a datasheet form that allows users to add
accounts that are affiliated with LOC_GPO from a tbl_account_Master and has a
combo box field named LOCAct_AccountNo. All other field on this form
autofill based on LOCAct_AccountNo entered.

The Popup form Frm_LUComplianceHistory pulls up compliance history for an
account on the subform Frm_MultActSelect when the field LOCAct_AccountNo is
double clicked with the following code:

Private Sub LOCAct_AccountNo_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Frm_LUComplianceHistory"
stLinkCriteria = "[Account_Number] =" & Me![LOCAct_AccountNo]
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormReadOnly

End Sub

Frm_LUComplianceHistory has the following fields:

Account_Number
GPO_Number
GPO_Name
Product

When LOCAct_AccountNo is double clicked I get all the Account's compliance
history but I need to further filter that history on the Main form's
Group_Prod_Cat field. So the stLinkCriteria needs to be something like

stLinkCriteria = "[Account_Number] =" & Me![LOCAct_AccountNo]

and

Me!frm_LUComplianceHistory.Product = Form!LOC_Maintenance.Group_Prod_Cat

How do I incorporate this into my stLinkCriteria or do I need to use another
method to filter out the product category?

Your help is greatly appreciated!
 
B

Boyd Trimmell aka HiTechCoach via AccessMonster.co

Try something like:

stLinkCriteria = "[Account_Number] =" & Me![LOCAct_AccountNo] and [Product] =
" & Form!LOC_Maintenance.Group_Prod_Cat

or

stLinkCriteria = "[Account_Number] =" & Me![LOCAct_AccountNo] and [Product] =
" & Me.Parent.Group_Prod_Cat


If Group_Prod_Cat is text then:

stLinkCriteria = "[Account_Number] =" & Me![LOCAct_AccountNo] and [Product]
= " & CHR(34) & Form!LOC_Maintenance.Group_Prod_Cat & CHR(34)


This may help:
http://www.mvps.org/access/forms/frm0031.htm

Emma said:
Hi all,

I have a main form called LOC_maintenance, a subform called
Frm_MultActSelect and a Popup form called Frm_LUComplianceHistory. The
LOC_Maintenance forms fields are as follows:

LOC_GPO (a combo box)
GPO_Name (autofills based on =Column(1) of LOC_GPO)
Group_Prod_Cat (Autofills based on Column(2) of LOC_GPO)

Subform Frm_MultActSelect is a datasheet form that allows users to add
accounts that are affiliated with LOC_GPO from a tbl_account_Master and has a
combo box field named LOCAct_AccountNo. All other field on this form
autofill based on LOCAct_AccountNo entered.

The Popup form Frm_LUComplianceHistory pulls up compliance history for an
account on the subform Frm_MultActSelect when the field LOCAct_AccountNo is
double clicked with the following code:

Private Sub LOCAct_AccountNo_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Frm_LUComplianceHistory"
stLinkCriteria = "[Account_Number] =" & Me![LOCAct_AccountNo]
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormReadOnly

End Sub

Frm_LUComplianceHistory has the following fields:

Account_Number
GPO_Number
GPO_Name
Product

When LOCAct_AccountNo is double clicked I get all the Account's compliance
history but I need to further filter that history on the Main form's
Group_Prod_Cat field. So the stLinkCriteria needs to be something like

stLinkCriteria = "[Account_Number] =" & Me![LOCAct_AccountNo]

and

Me!frm_LUComplianceHistory.Product = Form!LOC_Maintenance.Group_Prod_Cat

How do I incorporate this into my stLinkCriteria or do I need to use another
method to filter out the product category?

Your help is greatly appreciated!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top