473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Report Caption: Code to display table values in Report Label

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
Expand|Select|Wrap|Line Numbers
  1. ATTRIBUTENAME       ATTRIBUTEVALUE
  2. DepartmentName      RECOVERY
  3. DepartmentHead      Linda Lost
  4. DepartmentEmail     Email@Department
  5. DepartmentAddress   10 Collins Street, Melbourne
The sort of query I am looking at is:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblGeneralInformation.Attributename
  2. FROM tblGeneralInformation
  3. 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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.  
  3. lblDepartmentName.Caption = ?
  4.  
  5. End Sub
Thanks
May 25 '10 #1
5 3208
colintis
255 100+
try using DLookUp

Expand|Select|Wrap|Line Numbers
  1. lblDepartmentName.Caption =DLookup("tblGeneralInformation.Attributename", "tblGeneralInformation", "tblGeneralInformation.attributevalue='Departme ntName'")
May 25 '10 #2
Thank you - works perfectly :)
May 25 '10 #3
NeoPa
32,556 Expert Mod 16PB
A good answer, but I'd consider losing some of the extra table references :
Expand|Select|Wrap|Line Numbers
  1. lblDepartmentName.Caption = DLookup("[Attributename]", _
  2.                                     "[tblGeneralInformation]", _
  3.                                     "[AttributeValue]='DepartmentName'")
May 25 '10 #4
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.
Expand|Select|Wrap|Line Numbers
  1. lblDepartmentName.Caption = DLookup("[Attributename]", _
  2.                                     "[tblGeneralInformation]", _
  3.                                     "[AttributeValue]='DepartmentName'") & _
  4.                                     " 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
May 25 '10 #5
NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strLbl As String
  2.  
  3. strLbl = DLookup("[Attributename]", _
  4.                  "[tblGeneralInformation]", _
  5.                  "[AttributeValue]='DepartmentName'")
  6. lblDepartmentName.Caption = Replace("%A Other Text", "%A", strLbl)
May 25 '10 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Lachlan Hunt | last post by:
Hi, I was wondering if ::before and ::after pseudo-elements can apply to elements styled with the display: table-* properties. None of my tests worked in either Firefox or Opera, yet I could not...
4
by: dennis.joseph | last post by:
Hello Everyone, I have scoured through a million messages (ok...thats a bit exaggerated) but I just cannot figure out how to display null values in a query. I have tried Nz, IIF, Count(*) but it...
5
by: JayDawg | last post by:
Is there any way to create a pivot table in a report? I have a query with the fields Date, Name, and Data, and I am trying to create a report that has the dates running across the top (like...
12
by: Orchid | last post by:
Hello all, I have different version of reports which used for different months. For example, I am using report version 1 up to September, but we have some design changes on the report for October,...
2
ak1dnar
by: ak1dnar | last post by:
CREATE TABLE `products` ( `p_id` int(11) NOT NULL, `p_name` varchar(15) default NULL, `p_features` varchar(100) default NULL ) from this table I am going to fetch the records and display...
7
by: JH001A | last post by:
I have a report of selected employees printed using a label report four columns wide. The label report width is 1.5 inches. How can I merge a report header ahead of the labels? Thank you for any...
11
by: Gord | last post by:
When I open a certain report, it runs some code that generates the records that will be displayed in that report. This works fine. When I go to print preview the report it appears that the code...
0
by: adsaca | last post by:
there basic sections in Crystal report namely Report Header Page Header Group Header Details
22
Atli
by: Atli | last post by:
Hi. I'm setting up a small photo-album-type thing, where I use PHP to set up a list of images for visitors to click through. That's all simple enough. However, I'm having a weird bug in IE8. ...
3
by: dibwas | last post by:
I have very limited knowledge on asp .net using c#. I am trying to fetch data from my mssql database and display the value on my page. I have succeeded to display it using datagrid. But I want it to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.