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

Simple Report Question - Fields from multiple tables

P: 4
Hi all,

I'm a C++ programmer trying to help out a friend with a VBA app someone else wrote for him years ago. Don't know a lot about VBA. There is a report in this app that has a particular table (a table of clients) as its "Record Source", and when the report is opened, it uses a filter on that table:

strLog = [LOG_NO]
strFilter = "[LOG_NO] = " & "'" & strLog & "'"


I want to create a new table that just has a single row for "system-wide" information, that there's only one instance of, and that might change someday, such as the office address, the date the notary's commission expires (this is the one I need now!!), office phone number, etc. I'd like to pull this NotaryExpiration field from this table and put it on the report.

I tried creating a text field that references the table and the field; I tried creating a query that selects the NotaryExpiration field from the table and referencing that on the report, but no matter what I do, I get "#Error" on the report. Is it possible to pull data from multiple tables onto a single report? Do I need to put something extra into my filter string?

Alternatively, is there some other good way of handling such data, so that I don't have to show up every few years, find the .mdb file, change the Notary Commission Expires date on the report, and recompile it into an .mde file?? ;-)
Mar 17 '08 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,597
Hi all,

I'm a C++ programmer trying to help out a friend with a VBA app someone else wrote for him years ago. Don't know a lot about VBA. There is a report in this app that has a particular table (a table of clients) as its "Record Source", and when the report is opened, it uses a filter on that table:

strLog = [LOG_NO]
strFilter = "[LOG_NO] = " & "'" & strLog & "'"


I want to create a new table that just has a single row for "system-wide" information, that there's only one instance of, and that might change someday, such as the office address, the date the notary's commission expires (this is the one I need now!!), office phone number, etc. I'd like to pull this NotaryExpiration field from this table and put it on the report.

I tried creating a text field that references the table and the field; I tried creating a query that selects the NotaryExpiration field from the table and referencing that on the report, but no matter what I do, I get "#Error" on the report. Is it possible to pull data from multiple tables onto a single report? Do I need to put something extra into my filter string?

Alternatively, is there some other good way of handling such data, so that I don't have to show up every few years, find the .mdb file, change the Notary Commission Expires date on the report, and recompile it into an .mde file?? ;-)
  1. The new table you are creating, where will you be deriving its data from?
  2. What other Fields besides [NotaryExpiration] will this Table consist of?
  3. Will this new Table to linked to the Record Source of your Report, namely the Clients Table?
  4. You seem to imply that the data in this Table will be relatively static, how often will it change?
  5. Where exactly this [NotaryExpiration] date be placed on the Report? Will it be the Report Header, Report Footer, Group Header, Group Footer, etc.?
  6. I'm assuming the Data Type of [NotaryExpiration] is Date/Time, is this correct?
  7. These are only a few of the questions for which we need an answer, there is simply too little information to give an adequate response on.
Mar 17 '08 #2

P: 4
Hi, thanks for the reply. As for the questions:
1) I guess ultimately I will have a form on which the user can change the values in this new table.
2) Does it matter? Let's say for now, there is only one field, NotaryExpiration (and only one record in the table). I'd like to know the general principle behind getting data from a second table onto the report, and if it turns out to be relatively easy, I'll go through all the reports and figure out what data might change someday, such that it should be in a table.
3) I don't understand the question. There won't be any key from one table to the other, if that's what you mean. There isn't any logical relationship between the date the legal secretary's notary commission expires and any of the clients' information.
4) I don't know. At least once every four years, since that is how often the Notary commission expires.
5) It will be in the body of the report. (the "Detail" I guess?)
6) Yes, Date/Time.
Mar 18 '08 #3

P: 4
I'm back again... Let me try to clarify what I would like to do.

When I look at the existing report's Object Dependencies, it currently depends on the "Combo1" table, which is a table with client information in it. Is there any way to add a dependency to the report, so that it depends on "Combo1" AND the "SystemInfo" table? I could not see a way to do that when creating a new report, even. Alternatively, could I change this existing report's dependency to be on a query instead of on a table?

I tried creating a new dummy report, and having it depend on a query, "Select * from Combo1, SystemInfo", and it seems like it will work. I guess the quick question on this would be: Is depending on the Table "Combo1" equivalent to depending on a Query "Select * from Combo1"? If so, I guess it will work the same way if I select from Combo1 and SystemInfo both -- I've just got one row in SystemInfo, so I think it will work as if each row of Combo1 just had some additional fields from SystemInfo.

I would just copy the contents of the old report onto this new report, but the formatting is very tricky, and I end up with the margins being screwed up. I would love to just change the dependency in the existing report. How do you get at that? It seems the Object Dependencies view just lets you look at the dependencies, not change them.
Mar 18 '08 #4

ADezii
Expert 5K+
P: 8,597
Hi, thanks for the reply. As for the questions:
1) I guess ultimately I will have a form on which the user can change the values in this new table.
2) Does it matter? Let's say for now, there is only one field, NotaryExpiration (and only one record in the table). I'd like to know the general principle behind getting data from a second table onto the report, and if it turns out to be relatively easy, I'll go through all the reports and figure out what data might change someday, such that it should be in a table.
3) I don't understand the question. There won't be any key from one table to the other, if that's what you mean. There isn't any logical relationship between the date the legal secretary's notary commission expires and any of the clients' information.
4) I don't know. At least once every four years, since that is how often the Notary commission expires.
5) It will be in the body of the report. (the "Detail" I guess?)
6) Yes, Date/Time.
Let's assume, as you indicate in Item #2, that you have a single Field called [NotaryExpiration] {DATE/TIME} in a SystemInformation Table. To retrieve the value from that Field, and place it in a Txt Box on a Report:
  1. In Report Design View.
  2. Create an 'Unbound' Text Box in the appropriate Report Section (be advised that if you place the Text Box in the Report's Detail Section it will be duplicated for each and every Record).
  3. Right Click on the Text Box ==> Properties ==> Data Tab.
  4. Set the Control Source Property equal to:
    Expand|Select|Wrap|Line Numbers
    1. =DLookUp("[NotaryExpiration]","SystemInformation")
  5. Hope this helps.
Mar 18 '08 #5

P: 4
DLookUp!!! Thank you, thank you, thank you! This is the "easy way" I was looking for.
Mar 18 '08 #6

ADezii
Expert 5K+
P: 8,597
DLookUp!!! Thank you, thank you, thank you! This is the "easy way" I was looking for.
You are quite welcome, it doesn't get much easier than that.
Mar 18 '08 #7

Post your reply

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