Navigation to DS on subform of navigation form

[Ihussein]

good afternoon,
While I'm trying to reference to a control on from of navigation sub form is show an error message that "Microsoft Access can n't find field referred to in your expression". Once you open the attached db open frmMain then selected Duplicated Red Report from the list on the right side, then DS will browse to frmDuplicatedRed and it will allow you to select Locname from the drop down menu of the combo box, then if you click print preview it show the above mentioned error.

 

[sneuberg]

In cmdPrintPreview_Click if you change

DoCmd.OpenReport "rptDuplicatedRecords", acViewPreview, "", _
   "LocID_FK=" & Forms!frmMain!NavigationSubform.Form!frmReportsHome.Form!DS.cboLocName, acNormal

to

 DoCmd.OpenReport "rptDuplicatedRecords", acViewPreview, "", _
   "LocID_FK=" & Forms![frmMain]![NavigationSubform].[Form]![DS].[Form]![cboLocName], acNormal

it will get you passed the error message you are getting. In this reference [NavigationSubform].[Form] refers to the form frmReportsHome so that shouldn't appear in the reference.

But you have other problems. First the references in the report are wrong and they are easy to fix but the reference Forms![frmMain]![NavigationSubform].[Form]![DS].[Form]![cboLocName] returns the LocID and you don't have that in tblRed. Instead you have the location name (ES, NS, WS) as the foreign key. My suggestion is to get rid of the LocID in tlbLoc and use LocName as the primary key. Just my opinion but I think it's silly having a surrogate key (autonumber) for this type of table.

 

[Ihussein]

First of all thank you so much for the code now it's working. yes in my tblRed is shows (ES, NS, WS) as it's more user friendly, but the actual data are numbers. I following this approach as the database size/ speed really matters for me.
hope it clarifies, once again thanks for the support and help
Regards