Simple Report Question - Fields from multiple tables

[Laure]

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 NotaryExpiratio n 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 NotaryExpiratio n 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?? ;-)

[ADezii]

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 NotaryExpiratio n 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 NotaryExpiratio n 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 [NotaryExpiratio n] 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 [NotaryExpiratio n] 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 [NotaryExpiratio n] 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.

[Laure]

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, NotaryExpiratio n (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.

[Laure]

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.

[ADezii]

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, NotaryExpiratio n (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 [NotaryExpiratio n] {DATE/TIME} in a SystemInformati on 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:
   [CODE=text]=DLookUp("[NotaryExpiratio n]","SystemInform ation")[/CODE]
5. Hope this helps.

[Laure]

DLookUp!!! Thank you, thank you, thank you! This is the "easy way" I was looking for.

[ADezii]

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.