Correct format for DoCmd.BrowseTo acReport (1 Viewer)

[Michael Barkemeyer]

Hello,
I have paired down an existing db to be able to attach it here. My issue is that I don't know what the proper syntax is when using DoCmd.BrowseTo acReport that is pulling data from a query containing an Expression. The code that is producing an error is tied to the first DropDown called cboOwner within a subform as part of the Main form.
Thank you for your help.

The SQL for the query that is the record source for the form is:

---

SELECT tblInvoices.InvoiceID, Format([ServiceDate],"mmm yyyy") AS Expr1, Sum(tblInvoices.TotalCharge) AS SumOfTotalCharge, Sum(tblInvoices.TimeMin) AS SumOfTimeMin, tblInvoices.[Project Owner Name], tblInvoices.InvoiceTitle, Format([ServiceDate],"yyyy") AS Expr2, tblProjectOwners.[Project Owner Name], tblProjectOwners.ID
FROM tblInvoices LEFT JOIN tblProjectOwners ON tblInvoices.[Project Owner Name] = tblProjectOwners.ID
GROUP BY tblInvoices.InvoiceID, Format([ServiceDate],"mmm yyyy"), tblInvoices.[Project Owner Name], tblInvoices.InvoiceTitle, Format([ServiceDate],"yyyy"), tblProjectOwners.[Project Owner Name], tblProjectOwners.ID
HAVING (((Format([ServiceDate],"yyyy"))=2020));

The Expression that I am trying to reference in the DoCmd.BrowseTo acReport line is:

---

Expr2: Format([ServiceDate],"yyyy")

Line in code containing syntax error is:

---

Case Not IsNull(cboOwner) And Not IsNull(cboYear)
        DoCmd.BrowseTo acReport, "Year End Invoice Grouping", "Main.NavigationSubform>ReportCenter.NavigationSubform>YEInvoicing.DS", "[tblProjectOwners].[ID]=cboOwner.Value and (((Format([ServiceDate],"yyyy"))=cboYear.Value", "", 1
End Select

 

[CJ_London]

try

DoCmd.BrowseTo acBrowseToReport, "Year End Invoice Grouping", Me.DS, "[tblProjectOwners].[ID]=" & cboOwner '& " and year([ServiceDate]=" & cboYear

Note the year is commented out since it is not populated

also better to use year([ServiceDate]) rather than Format([ServiceDate],"yyyy") AS Expr2 since it is a numeric value - format returns a text value

 

[Pat Hartman]

You don't have Option Explicit defined. This ensures that ALL variables are defined and keeps you from getting runtime errors. This is BEST PRACTICE so change the database setting to require declarations. The point of Option Explicit is to get compile errors which are easier to fix instead of runtime errors which will just embarrass you in front of your users.

Your code does not compile Because ServiceDate is not defined.

The RecordSource query of the report includes criteria that looks for 2020. You CANNOT override that with a where argument. The criteria in the Where and Filter arguments are ON TOP OF whatever is already in the query. If the query returns only records for 2020, you can't make it bring in 2021 using this method.

Compiling is part of the development process. ALWAYS compile (and save) before you test new code. This is best practice and could save your bacon if "this" code change goes bad and locks up the database forcing the dreaded cntl-alt-del to bring up Task Manager to force Access to close without saving your precious code.

Do you have a specific reason for using BrowseTo rather than OpenReport? I've read the help entry and it looks like BrowseTo was probably created to support some web access as it moves between open objects sort of like the windows key on your keyboard. It also seems to work on a Navigation form. Most experts don't use the Navigation form since it brings too many issues. You can use a regular form with a tab control to create your own Navigation form without the extra baggage.

DoCmd.BrowseTo method (Access)

Office VBA reference topic

docs.microsoft.com

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

I would use the OpenReport Action unless there is some feature you need from the BrowseTo.

 

[Michael Barkemeyer]

try

DoCmd.BrowseTo acBrowseToReport, "Year End Invoice Grouping", Me.DS, "[tblProjectOwners].[ID]=" & cboOwner '& " and year([ServiceDate]=" & cboYear

Note the year is commented out since it is not populated

also better to use year([ServiceDate]) rather than Format([ServiceDate],"yyyy") AS Expr2 since it is a numeric value - format returns a text value

Thank you CJ.
I have made the suggested changes to include year([ServiceDate]). But am getting a data type error when attempting to filter by year.

 

[CJ_London]

as I said, you are using the format function in your query which is referenced in your year combo.

also, if you include the year the code can run before you have selected a value - so you need to cater for that eventuality. perhaps it's to show all years, perhaps it doesn't run if a year has not been entered. you need to think it through better

 

[arnelgp]

without using BrowseTo.

 

[Pat Hartman]

I have made the suggested changes to include year([ServiceDate]). But am getting a data type error when attempting to filter by year.

If you look at #3, you'll see why.