CODEWITHSUNDEEP
vbaformsms-access
Steve Hewitt
Steve Hewitt

Reputation: 69

Running a report directly and from a navigation form - reference issue

I have a form in MS Access (365) which accepts various selection criteria and runs a report with those criteria built into the wherecondition of a DoCmd.OpenReport command. The report is run via a button with On Click VBA code, since the wherecondition has to be built according to which, if any, criteria have been chosen. That all works fine when I open the selection criteria form directly. I want to open the selection criteria form via a navigation form, since there will be other, similar reports with selection criteria that I want to run from within the same navigation form.

As stated above, everything works when I open the selection criteria form directly and run the report but when I tried it via a navigation form, it didn't work. That's ok, I found a solution on the MS Dev Center site which works when I run the selection criteria form (and then the report) from the navigation form. All fine. But then (of course) the references within the button On Click code don't work when I open the selection criteria form directly and run the report. I would like to be able to run the selection criteria form and then the report from both positions - directly from MS Access and via the navigation form. There will presumably be some way to achieve this but (as I said above, I am new to MS Access and VBA) I could spend a lot of time clutching at shadows. Hopefully, someone will be able to tell me the simplest way to do this?

Code sample with relevant comments below. On runnning the selection criteria form and report directly, the line commented as AAA works ok, MsgBox ABB and DDD and those beyond all show; on running them via the navigation form, line BBB works ok, MsgBox ABB and DDD and those beyond all show (well, they would except that I haven't yet coded the [NavigationSubform] option into all the other whereconditon building stuff). When I switch the lines AAA and BBB (ie, comment the other one out) MsgBox AAA show ok, then it fails with:

Can't find referenced form "frmSelectSpeciesSiteDates"

before reaching MsgBox DDD.

MsgBox "ABB"

'If Not Forms![frmSelectSpeciesSiteDates]![cboCommonName] = "" Then 
         ' AAA Works when frmSelectSpeciesSiteDates is run directly

If Not Forms![frmNavSelectiveReports]![NavigationSubform].[Form]![cboCommonName] = "" Then
         ' BBB Works when frmSelectSpeciesSiteDates is called from a navigation form

   strWhereCondition = strWhereCondition & "[common name] = " & Chr(34) & Me![cboCommonName] & Chr(34)

End If

MsgBox "DDD"

Upvotes: 2

Views: 223

Answers (1)

HansUp
HansUp

Reputation: 97101

You have a form named frmSelectSpeciesSiteDates which has a combobox named cboCommonName. The form also includes VBA code which uses the combobox value to modify a string variable ...

strWhereCondition = strWhereCondition & "[common name] = " & Chr(34) & Me![cboCommonName] & Chr(34)

However you only want to modify the string when the combobox contains something other than an empty string. And that is where you're struggling. You reference the combobox one way when the form is opened directly as a top-level form, and another way when it is contained in a navigation form ...

Forms![frmSelectSpeciesSiteDates]![cboCommonName]
Forms![frmNavSelectiveReports]![NavigationSubform].[Form]![cboCommonName]

I suggest you abandon both of those and refer to the combobox the same as where you modify the string (Me!cboCommonName) ... 

If Not Me!cboCommonName = "" Then
    strWhereCondition = strWhereCondition & "[common name] = " & Chr(34) & Me!cboCommonName & Chr(34)
End If

Upvotes: 1

Related Questions