By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,216 Members | 557 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,216 IT Pros & Developers. It's quick & easy.

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

P: 5
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.

Apr 26 '10 #1
Share this Question
Share on Google+
10 Replies

Expert Mod 100+
P: 2,321
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
Expert 100+
P: 897
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
  16. Private Sub Report_Open(Cancel As Integer)
  17. On Error Resume Next
  18. setupheaders
  19. End Sub 
Apr 26 '10 #3

P: 5
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
Expert 100+
P: 897
attached is a very simple demo mdb with a report of what it is I think you desire
Attached Files
File Type: zip (18.4 KB, 166 views)
Apr 27 '10 #5

P: 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

Expert Mod 15k+
P: 31,770
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

P: 5
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.
Apr 27 '10 #8

Expert Mod 15k+
P: 31,770
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

P: 5
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

Expert Mod 15k+
P: 31,770
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

Post your reply

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