Access to nested subform values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I cannot find any help on the syntax for getting the value contained within
the current form and its nested subforms to my OpenRecord 'where' clause. I
can only get values from the main forms data as in:

DoCmd.OpenReport stDocName, acPreview, ,"CompanyName =
Forms!BusinessForm!CompanyName.value"

I want to combine CompanyName with LastName to create the 'where' clause.
LastName is in the subform PersonForm which is in the subform
BusinessLocationSubform which is in the main form, BusinessForm.

Thank you for your help. I've spent the last three days looking for this
answer before mailing this group...Ellie
 
Try this syntax:

Dim strCriteria as String

strCriteria = "[CompanyName] = '" & Forms!BusinessForm!CompanyName & "'"

'If you are running the report from the businessForm then use this line
'instead of the one above:

strCriteria = "[CompanyName] = '" & Me.CompanyName & "'"

'To concatenate more values use:
strCriteria = strCriteria & _
" and [LastName] = '" & me!subformname!lastname & "'"

'and so on for all the fields you want filter...

DoCmd.OpenReport stDocName, acPreview, ,strCriteria

Make sure your report has the fields that you are including in your criteria,
 
Back
Top