Print Custom Header for Reports

[SierraJuliet]

I have a series of reports and want to print the companies header with each report.



I have a table (<Company Header>) with accommodating form (<Company Header>) that allows different entities to add their own customized header for when they print reports.

Each form has a print button that opens a report in order to print the data of the current view. When the print button on the form is clicked each current view should print with customized header from a different table/form (<Company Header>) added to the top of the report.

Right now I have the fields of the table/form (<Company Header>) added to one report but does not populate. The Control Source for each field is set to the table (<Company Header>) but says to enter a parameter value while displaying the source table's name. It says the table does not exist when I double checked by setting it up with the Expression Builder and navigating to the table and each subsequent field myself.

[June7]

Cannot reference table object directly in textbox. The [Company Header] table would have to be included in the form or report RecordSource then bind textbox to field or use DLookup() in textbox ControlSource.

Advise not to use spaces in naming convention.

[SierraJuliet]

Thanks June 7, I will give the DLookup a try. The space was just to denote the company header form and table. No spaces in any of my names - field or otherwise.

[SierraJuliet]

I got it to work.

There is a form and table. The table holds the data for the form that is customized to each customer that uses it in their enterprise.

I added VBA to the report. Here is the VBA pasted into each reports coding.

Private Sub Report_Load()
    Me![txtCompanyHeaderCompanyName] = DLookup("CompanyHeaderName", "tblACompanyHeader", "CompanyName" <> "")
    Me![txtCompanyHeaderStreet] = DLookup("CompanyHeaderStreet", "tblACompanyHeader", "CompanyStreet" <> "")
    Me![txtCompanyHeaderCity] = DLookup("CompanyHeaderCity", "tblACompanyHeader", "CompanyCity" <> "")
    Me![txtCompanyHeaderState] = DLookup("CompanyHeaderState", "tblACompanyHeader", "CompanyState" <> "")
    Me![txtCompanyHeaderPostal] = DLookup("CompanyHeaderPostal", "tblACompanyHeader", "CompanyPostal" <> "")
    Me![txtCompanyHeaderCountry] = DLookup("CompanyHeaderCountry", "tblACompanyHeader", "CompanyCountry" <> "")
    Me![txtCompanyHeaderOfficePhone] = DLookup("CompanyHeaderOfficePhone", "tblACompanyHeader", "CompanyOfficePhone" <> "")
    Me![txtCompanyHeaderWebsite] = DLookup("CompanyHeaderWebsite", "tblACompanyHeader", "CompanyWebsite" <> "")
End Sub

[June7]

Don't really need VBA. DLookup expression can be in textbox ControlSource property.

If the table has only 1 record, don't need WHERE CONDITION argument. In fact, I am surprised they even work with that criteria structure.

[SierraJuliet]

I am surprised they even work with that criteria structure.

It does work and it was giving me errors before when I tried DLookup in Control Source even without the WHERE Condition.

This is the same working code without the WHERE Condition and it works as well.

Private Sub Report_Load()
    Me![txtCompanyHeaderCompanyName] = DLookup("CompanyHeaderName", "tblACompanyHeader")
    Me![txtCompanyHeaderStreet] = DLookup("CompanyHeaderStreet", "tblACompanyHeader")
    Me![txtCompanyHeaderCity] = DLookup("CompanyHeaderCity", "tblACompanyHeader")
    Me![txtCompanyHeaderState] = DLookup("CompanyHeaderState", "tblACompanyHeader")
    Me![txtCompanyHeaderPostal] = DLookup("CompanyHeaderPostal", "tblACompanyHeader")
    Me![txtCompanyHeaderCountry] = DLookup("CompanyHeaderCountry", "tblACompanyHeader")
    Me![txtCompanyHeaderOfficePhone] = DLookup("CompanyHeaderOfficePhone", "tblACompanyHeader")
    Me![txtCompanyHeaderWebsite] = DLookup("CompanyHeaderWebsite", "tblACompanyHeader")
End Sub

[June7]

Those exact DLookup expressions should work in textbox. Just be sure to use = sign.

[SierraJuliet]

They did not work in Control Source fields for some reason, not a big deal I prefer to put code in VBA for troubleshooting because it is a pain to sort through an error with multiple forms of code entered throughout MS Access.