473,320 Members | 1,810 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,320 software developers and data experts.

how to reference a table row/column in a report header?

I have a report with several check boxes. I want the (vertical) heading over each to be "variable" -- that is, taken from another table, where the heading of the first box would be the textual contents of "tablex", row 1, column 1. The heading of the second would be the text in tablex, row 1, column 2 etc.

I tried making those headers text boxes and pointing the "control source" to the one-row table that contained the prompts, but I got an error.

Thanks.
Apr 26 '10 #1
10 5971
TheSmileyCoder
2,322 Expert Mod 2GB
Depending on why you need this, the solution might differ a bit. What springs to mind at first, would be to set the labels in the reports OnOpen event by using vbaCode. Example:
Expand|Select|Wrap|Line Numbers
  1. Me.Label1.Caption="Amount"
There are other ways to do this, either by bringing it into the query, and then grouping on it, or running a crosstab query instead, it all depends on your needs.
Apr 26 '10 #2
Jim Doherty
897 Expert 512MB
As has been pointed out the solution itself is variable.

Setting the caption of a label is easy enough and can be done in code when the report opens. Whether you put the labels in the report header or the report 'page' header is a matter for you to decide.

The principle of what you are saying it seems is this:-

your report references its data source from a table (or query) and therefore has fixed columns of data. You then want the text label display to be sourced from a completely separate one row table when the report opens.

The following code (devoid of error checking and the usual 'what if ' considerations depending on your needs) demonstrates the logic of retrieving your data to a report from one table but setting up the headers in the captions for the labels from a different table

Expand|Select|Wrap|Line Numbers
  1. Private Sub setupheaders()
  2. Dim db As DAO.Database
  3. Dim rst As DAO.Recordset
  4. strSQL = "Select * From tblHeading;"
  5. Set db = CurrentDb()
  6. Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
  7. rst.MoveFirst
  8.   Me.lblheading1.Caption = rst!h1
  9.   Me.lblheading2.Caption = rst!h2
  10.   Me.lblheading3.Caption = rst!h3
  11. rst.Close
  12. Set rst = Nothing
  13. Set db = Nothing
  14. End Sub
  15.  
  16. Private Sub Report_Open(Cancel As Integer)
  17. On Error Resume Next
  18. setupheaders
  19. End Sub 
Apr 26 '10 #3
Thanks! It sounds like you both had the gist of what I was trying to do. I'll work up the code this morning.
I assume I drop that code into the "onopen" event of the REPORT (not the header, detail, etc.).
This may also answer a question I had in Excel. It seems the days of literal references and inline macros are over. From here on in it's VBA.
I wanted the left footer in a report in Excel to equal the value of $S$4, so I know I'll address that the same way -- in code.
Apr 26 '10 #4
Jim Doherty
897 Expert 512MB
@acrobatus
attached is a very simple demo mdb with a report of what it is I think you desire
Attached Files
File Type: zip vheader.zip (18.4 KB, 196 views)
Apr 27 '10 #5
Thanks! I got the code sample to work today using "label" in place of "lblheading", which is pretty clear to me. But I'll certainly look at the example for any more input I can make use of.

Thanks again.
Apr 27 '10 #6
NeoPa
32,556 Expert Mod 16PB
I wanted the left footer in a report in Excel to equal the value of $S$4, so I know I'll address that the same way -- in code.
Even easier in Excel as you can find the object to set quite simply by recording a macro that sets the value. The created code gives you the correct reference for the header object required.
Apr 27 '10 #7
A macro showed me that the left footer is ActiveSheet.PageSetup.LeftFooter, but assigning the value of $S$4 blows up...
I tried another macro, copying the value I wanted and pasting it, and that showed me that the "copy" produced, Range("S4").Select, so I tried putting that after the = to assign that value to the footer. It ran and planted the word "TRUE" in the footer...
Then I figured if "Select" returns a boolean, Value might return a value, and it worked.
I agree that macros will reveal a great deal about references, and I'll enjoy using them in Excel, and lament their absence in Access, but I just got two VBA references, for Excel and Access, so I'll dig into them before asking more questions.
Thanks
Apr 27 '10 #8
NeoPa
32,556 Expert Mod 16PB
It's always a good idea to get into your own study (reading books whatever). There's really very little that can substitute for that, but we're happy to help where we can. Members who are clearly intent on learning rather than simply getting their work done for them are always more rewarding for us. Don't be put off from asking for help is what I'm saying ;)
Apr 28 '10 #9
I appreciate the help. I've run into the "wall" on SQL queries and asked for help elsewhere, and I try to help those I can as well.
Thanks for your time!
Apr 28 '10 #10
NeoPa
32,556 Expert Mod 16PB
That's a great attitude :)

You appreciate we do a lot with SQL here too? T-SQL in the SQL Server section, but also Jet-SQL in here.
Apr 29 '10 #11

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

Similar topics

3
by: Sandros | last post by:
Background: I'm collecting usability statistics for a group of applications. Each count has the following attributes: date, application, major heading, minor heading, count. My intent is to pull...
19
by: Owen T. Soroke | last post by:
Using VB.NET I have a ListView with several columns. Two columns contain integer values, while the remaining contain string values. I am confused as to how I would provide functionality to...
7
by: slitvinov | last post by:
I am learning Relax NG. The problem is that I cannot figure out how to make a schema for a table. In my case I would like to make a table with any name of child elements (columns) but columns...
0
by: Matthias | last post by:
Hi all, I have a problem with XSL FO. I wrote a handbook for my application in XML and formats it with xsl:fo (FOP) for PDF output. The handbook has some chapters. I have to write the actual...
3
by: Newbie | last post by:
I have two different levels of people who want to see my production report. We get orders which each call for one of five products. One level wants to see a spreadsheet type report with six...
12
by: kabradley | last post by:
Hello, Thanks for looking at my post and hopefully having an answer or at least a suggestion to my problem. I currently work at a financial planning office that deals with many clients and accounts....
7
by: Kamal | last post by:
Hello all, I have a very simple html table with collapsible rows and sorting capabilities. The collapsible row is hidden with css rule (display:none). When one clicks in the left of the...
0
by: miamikk | last post by:
I am XML newbie. I have question about inserting dynamic text in the header of HTML table. This is the site I have created (Only Report Type 1 is working)...
22
kcdoell
by: kcdoell | last post by:
I have been trying for the last several days to create a query that will give me all of the values I need to create a report. Background: The report is different than anything I have done but...
5
by: jrod11 | last post by:
hi, I found a jquery html table sorting code i have implemented. I am trying to figure out how to edit how many colums there are, but every time i remove code that I think controls how many colums...
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...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.