Access 2010 Navigation Form Question

[Ray1127]

I have an Access 2010 Database with 2 forms 1 of the forms is used to allow the user to select specific criteria elements for a Report. When the user clicks on the preview report or print report buttons the report opens and the SQL for the report is built in a module. The SQL uses the following sample select statement There are 3 but there all similar

Select Case Forms!frm_reporting.frame_Reporting
Case Is = 1
strWhere_rpt = ""
Case Is = 2
strWhere_rpt = "tbl_check_register.Payeeid "
& Forms!frm_reporting!cbo_Payto
Case Is = 3
strWhere_rpt = "tbl_check_register.dept_id = "
& Forms!frm_reporting!cboDept
Case Is = 4
strWhere_rpt = "tbl_check_register.locationid = "
& Forms!frm_reporting!cbo_Location
End Select


After adding the Navigation Form the Forms!frm_reporting gives an error stating that the form does not exist. The form does exist it is one of the 2 forms on the navigation page.

What am I missing when adding the navigation form does Access 2010 automatically subform the forms there? how would I refer to a subform in this case.

 

[TheAceMan1]

How are ya Ray1127 . . .

[blue]After adding the Navigation Form the Forms!frm_reporting gives an error stating that [purple]the form does not exist.[/purple][/blue]

Is the form open at the time the code is run?

In what form does the code reside?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Ray1127]

Yes there is a command button on the form to preview and print the report. When you click on the button the error comes up. If the form is open outside of the Navigation form it works perfectly. However due to the user the Navigation Form is a requirement.

I suspect it's because in Access 2010 instead of the old switchboard it has this Navigation form. Which by the way I like a lot better than the old switchboard. The issue I think is that the Navigation form is the main form and all of the forms on the Navigation form are subforms. So if the query for a report uses controls on the form for criteria it's a different syntax than what I'm used to because I've always done this on a form not a subform. That's the problem.

 

[TheAceMan1]

Ray1127 . . .

I havn't used the [blue]Navigation Form[/blue] yet, but I believe your right about the subform issue. If this true, your saying [blue]frm_reporting[/blue] is a subform on the [blue]Navigation Form[/blue] and hence the [blue]Navigation Form[/blue] has to be open. Syntax in your Select Statements should look like:

[blue]   Dim frm As Form
   
   Set frm = Forms![purple][b]NavigationFormName[/b][/purple]!frm_reporting.Form

   Select Case frm.frame_Reporting
      Case Is = 1
         strWhere_rpt = ""
      Case Is = 2
         strWhere_rpt = "[tbl_check_register.Payeeid]=" & frm!cbo_Payto
      Case Is = 3
          strWhere_rpt = "[tbl_check_register.dept_id]=" & frm!cboDept
      Case Is = 4
          strWhere_rpt = "[tbl_check_register.locationid]=" & frm!cbo_Location
   End Select[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

Well, if the code is in the form, why not simply replace all Forms!frm_reporting with Me ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ray1127]

PHV The code is not in a form it is in a module. When I had it in the Form The Report would error Even though I used:

Public Function Build_SQL() As String

The Function would error in the Load Event of the Report saying it didn't exist. Moving it to the module worked.

Here's the call I used to make it work.

'The following is from the Navigation Form Reporting to link the
'Criteria for the reports to the Record Source for the reports.
'This Function called from the load event of the Report.

Forms.Item(0).Controls.Item(6)!frame_Reporting