Report Caption: Code to display table values in Report Label

[DeniseTurner]

I have to change some titles of my reports from hard coded values to something easily changed by the user, as the database I am using will now be distributed to different areas within the business.

I have created a General Information Table, that Users can maintain.

The table has two columns, with similar information contained as shown below

ATTRIBUTENAME       ATTRIBUTEVALUE
DepartmentName      RECOVERY
DepartmentHead      Linda Lost
DepartmentEmail     Email@Department
DepartmentAddress   10 Collins Street, Melbourne

The sort of query I am looking at is:

SELECT tblGeneralInformation.Attributename
FROM tblGeneralInformation
WHERE (((tblGeneralInformation.attributevalue)='DepartmentName'));

This gives me RECOVERY, which is the label/caption I want in the report

But how do I write this in vba?? Can anyone help me fill in the ? below.

Thanks

Private Sub Report_Open(Cancel As Integer)

lblDepartmentName.Caption = ?

End Sub

Thanks

[colintis]

try using DLookUp

lblDepartmentName.Caption =DLookup("tblGeneralInformation.Attributename", "tblGeneralInformation", "tblGeneralInformation.attributevalue='Departme ntName'")

[DeniseTurner]

Thank you - works perfectly :)

[NeoPa]

A good answer, but I'd consider losing some of the extra table references :

lblDepartmentName.Caption = DLookup("[Attributename]", _
                                    "[tblGeneralInformation]", _
                                    "[AttributeValue]='DepartmentName'")

[DeniseTurner]

Combining Dlookup and Additional Text

Thank you.

Both options work well.

For any other newbies like me, the next step to then add additional text after your lookup field is as follows.

lblDepartmentName.Caption = DLookup("[Attributename]", _
                                    "[tblGeneralInformation]", _
                                    "[AttributeValue]='DepartmentName'") & _
                                    " Other Text" & ""

It took me a while to figure that out, I kept getting my syntax wrong but I hate just asking unless I give it a good crack :)

Thanks again

[NeoPa]

DeniseTurner: It took me a while to figure that out, I kept getting my syntax wrong but I hate just asking unless I give it a good crack :)

A good attitude Denise :)

Another alternative, which can sometimes be easier to read and understand is :

Dim strLbl As String

strLbl = DLookup("[Attributename]", _
                 "[tblGeneralInformation]", _
                 "[AttributeValue]='DepartmentName'")
lblDepartmentName.Caption = Replace("%A Other Text", "%A", strLbl)