file name in page footer

[tward]

I am a new user of Access.
I have created a data base with one table that will be updated each month. I plan to rename the data base file each month to indicate that month's data. I have a number of queries and reports that are made from the one table.

Is there a way to show the file name of the data base in the page footer so that it updates automatically when the file name is changed? For example: this month file is March Reprts.mdb, next month it will be April Reports.mdb. I want people who look at a report to be able to see the file name on the report. I don't want to have to go into each report and change a text box each time the DB is updated.

What I want to do is very simple in Word and Excel. So far, I haven't figured out how to do it in Access!

 

[jimmythegeek]

Are going to keep all the databases? If so, why are you going to create a new one each month? You could

1) Create a field that tells the month of the records in your table, then run a report only for that month, thus only having one database with a growing table, OR

2) Create a new table each month, but still only have one database.

To your question, why don't you just put a label in either the page or report footer that says whatever name you want (AprilReports.mdb, etc).

Their is a way to get the db name in code, if you want it I will give it to you. It takes some knowledge of VBA though. There is no quick way, like Excel or Word's ability to insert the file name.

Let me know. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[tward]

Thanks for your response.
This is a very large data base, that will soon have over 75,000 records. It is a State government position and employee file that includes 200+ fields. We need to update each month after the payroll is run. The size precludes just adding a new table each month. Right now with only 52,000 records in one table, the file size is 99Mb. I could put a label in each report and change it each month, but we may eventually have many reports from this table. If there is a way to do it automatically, why not?

I did find the answer on this site after I posted my question. It seems someone else asked the identical question last December. I didn't find it the first time I search this site. Here is a link to that post.
thread703-39433

 

[jimmythegeek]

That was a cool answer to your question, thanks for putting the link on your response.

Curiosity question: One table has 200 fields? Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[tward]

I know 200 fields are not the most efficient, but I'm new at this. The state converted to a new HR/Payroll system and the information is available in a data warehouse. There are 600+ fields in there. We anticipate the cost for accessing the data warehouse each time we want info will be pretty high. It was decided that once a month we would dump the info from the 200 fields we might use into Access so that we can run our queries from our PC. Cost is the primary reason for doing it this way. The dump in done as a delimited text file, by someone else, and I import it into Access. I tried letting Access wizard make tables and it came up with 17 of them. One table, though maybe not efficient, is much simpler for a novice.

This seems to be working, so for now, "if it ain't broke, don't fix it"

Thanks for replies and interest.

 

[jimmythegeek]

If it works for you, then who are we to question! Good Luck. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[tward]

It works for now. Someday I will learn more and make improvements.
I am not a techie. I am a Personnel Analyst. Until two months ago I had never even used Access!