[AccessD] Open Report from MainForm based on SubForm

Virginia,

What Max was trying to convey to you is that in the syntax for referencing a subform and its controls uses the name of the control on the main form which contains the sub form.

So while you will undoubtedly have a form object showing in the database window, visible in the forms tab, with the name "frm_VehicleInspectionSub", the *control* on the main form in which the sub form is displayed may in fact have a different name. It is that containing control's name that you need to use.

The expression...

Forms![frm_VehicleInspection]![frm_VehicleInspectionSub].Form![VehicleInspID]

... Translates to something like "there is a form called "frm_VehicleInspection" which has a sub-form control on it called "frm_VehicleInspectionSub". That control has a form in it (who's name we need not know) that in turn has a control on it called VehicleInspID: return the value of VehicleInspID."

HTH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis, Virginia
Sent: Wednesday, October 14, 2009 9:24 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Open Report from MainForm based on SubForm

I checked and the subform is named correctly (frm_VehicleInspectionSub).
The first part of the code sets the focus to the subform & this works.

 

With your example I get - "Can't find field VehicleInspID referred to in your expression". I checked the names & the control sources and they all show VehicleInspID;

********

The syntax you want is:-
 
DoCmd.OpenReport "MyRpt", acViewPreview, , "VehicleInspID=" & "Forms![frm_VehicleInspection]![frm_VehicleInspectionSub].Form![VehicleI
nspID]="
& me![VehicleInspID]
 
Virginia, make SURE that your have the correct NAME of the sub form.
Often people think they know what it is called but it isnt called that at all.  Check the data AND "Other" Tab in the properties.
 
I have a sub form where the DataSource = "MCM_PeoplePopNAD" But I call the form (Under the Other Tab in the properties) "PeoplePopSub2"
 
Max
 
*********
I have a command button on the main form that I want to open a report  filtered to one record based on a value on the SubForm.
 
 frm_VehicleInspection (mainform)
 frm_VehicleInspectionSub (subform)
 rpt_VehicleInspection (report to open)
 VehicleInspID is the key to open the report.
 
 
 
 ****************
 
 On Error GoTo Err_cmdPrint_Click
     Dim stDocName As String
     Dim stLinkCriteria As String
 'If
IsNull(Forms!frm_VehicleInspection.frm_VehicleInspectionSub!VehicleID)
 Then
  '       MsgBox "You must must enter a record before printing.",
 vbExclamation + vbOKOnly, "Entry Required"
 
   '    Exit Sub
 'End If
  '   vbresponse = acDataErrContinue
 '    DoCmd.RunCommand acCmdSaveRecord
 
     stDocName = "rpt_VehicleInspection"
 
     Forms![frm_VehicleInspection]![frm_VehicleInspectionSub].SetFocus
 
 This didn't work (it opens the report, but shows all the records on the
 report): 'DoCmd.OpenReport stDocName, acPreview, ,
 
"Forms![frm_VehicleInspection]![frm_VehicleInspectionSub].Form![VehicleI
 nspID]=" &
 
 
Forms![frm_VehicleInspection]![frm_VehicleInspectionSub]![VehicleInspID]
 
  This didn't work:   'DoCmd.OpenReport stDocName, acPreview, ,
 
"Forms![frm_VehicleInspection]![frm_VehicleInspectionSub].Form![VehicleI
 nspID]=" & Me![VehicleInspID]
 
 This didn't work either:    DoCmd.OpenReport stDocName, acPreview, ,
 
"Forms![frm_VehicleInspection]![frm_VehicleInspectionSub].Form![VehicleI
 nspID]=" & strDocName!VehicleInspID.[VehicleInspID]
 
 
 Exit_cmdPrint_Click:
     Exit Sub
 
 Err_cmdPrint_Click:
 
     MsgBox Err.Description
 
     Resume Exit_cmdPrint_Click
 
 End Sub
 
 Virginia

 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com