Reports Not Opening Correctly

[Trek-Fan]

Hi,
I hope i am not asking a question that has been covered before (I have spent a good deal of time googling this to no avail), but i have to deffer to the experts. And unfortunatly this may be a bit of a story to get to my point. Anyways here goes.

We have a Access database where I work that organizes jobs moving through the plant. We have (6) different operations and each respective foreman prints a report which shows him daily for the week what jobs he has scheduled. This is a good working system, but the wrench in the gears is that those darn pesky customers constantly change their minds about what they want,, meaning that on any given day the foreman report would change at least once.

I was tasked to build a digital status board to display on the wall, and in my bright optimistic eyes I had hoped this would be an easy task. Undaunted I looked into a interface between Powerpoint and Access to run Powerpoint in Kiosk mode. That doesn't seem to be a viable option since those two don't really want to play well with each other. Then in my dimmer, and more realistic eyes I looked into true status board software, which would require I learn (or pay someone to) PHP and build, then host and apache web server internally to display a web page. Not to mention the cost of the status board software. So then, with my blood shot, and depression eyes, i stumbled upon a concept!!

I could write a small VBA script that would PREVIEW each report full screened with a small delay in between. Thusly I had achieved the impossible. I had my status board, and i used software i already owned!! I felt like King Arthur after tugging Excalibur from the rock ! All i had to do was buy (or scavenge) a old pc to put in a corner and mount its display on the wall in a endless loop. Life again was grand !!

I have now been working on this script for the most part of the day with limited success. Knowing my own limitations, before i descend back into the pits of depression and despair, I decided to deffer to the expert knowledge in these forums. I have attached the sample database I am working on to test the script for the report. The formatting and data are irrelevant in so far as getting the script to work properly.

So far the "launch" form has a command button to start the script. The script then opens 3 reports, one after the other with a (3) second pause in between. That part is working perfectly. My hurdle so far is that the report does not display (at least on my computer, i have not tested it on other machines since i am at home on my laptop). If i make a script to open one report it will display properly, but this script has it not displaying anything inside the report window. Very vauge and cryptic description i know, So that is why i am attaching the sample database.

My hopes are, that if successful this may help other people with similar issues. Having basically a free (just the cost of a scavenged pc, and access to run on it) you can build custom, real time updating, status boards !!! Maybe nobody else is as excited about the prospect as I am, but hey, a boy can dream, right?

Anyways, i hope you have not been bored by my ramblings, I thought i would do a bit more than the standard surely, "Hey, I have problem! You guys fix !!" type post, by giving a small narrative of my saga for your entertainment at the torture of other human beings !! I also hope that this should have not be posted in the VBA section, as I will have to endure the ear flicking that comes with posting an incorrect section!!

Thank you in advance, and I would really appriciate any suggestions, (even if it is to stop with all the story line, because i have caused such an immense amount of boredom, your brain has tried to escape out your ear canals!!)

Thanks again,

Rob

 

[plog]

The script then opens 3 reports, one after the other with a (3) second pause in between. That part is working perfectly. My hurdle so far is that the report does not display...

I'm having a real hard time reconciling the "working perfectly" part with the "report does not display" part. Seems those are the definition of mutually exclusive.

Looking at your code, the problem is your form. You never specify what object to act on (which to open, which to maximize, etc.) so it does it on the default one, which is the form because its the one executing the code. The solution is to close the form as soon as possible.

I took your code and made it do what you want. My code should exist inside a module so don't put this in the form's code, put it in a new module. This is the code to run your slideshow:

Option Compare Database

Public Sub run_Slideshow()
    ' runs slideshow of reports, opens one after the other
    
DoCmd.Close acForm, "launch", acSaveNo
    ' closes launching form
    
open_Report (1)
    ' starts loop of opening reports with first report
End Sub


Public Sub open_Report(in_ReportNum)
    ' opens designated report (in_ReportNumber) for specified time frame, closes, then opens next one
    
int_PauseTime = 5               ' length of time to keep report open
int_StartTime = Timer           ' time when report is initially opened

int_NextReportNum = (in_ReportNum Mod 3) + 1
    ' sets next report to next report, if on last number sets next report number to 1
    
DoCmd.OpenReport "Table" & in_ReportNum, acViewPreview
DoCmd.Maximize
    ' opens and maximizes designated report
    
Do While Timer < (int_StartTime + int_PauseTime)
    ' pauses system for int_PauseTime seconds
    DoEvents
    Loop
    
DoCmd.Close
    ' closes opened report

open_Report (int_NextReportNum)
    ' calls function to open next report
    
    
End Sub

Hopefully my code is documented well enough that you can follow the logic. If not, feel free to ask my what you need.

You will need to modify the code on your form--you will still use it to launch the slideshow. On your button, replace the code you wrote with this:

Private Sub Command0_Click()

run_Slideshow

End Sub

All that does is trigger the module's code to take over.

 

[Trek-Fan]

That right there is why I had to deffer to the experts!! Works great. I cannot wait to get back to work Monday and try to implement that with some real data. Thank you for the quick reply, and I hope other people may find this useful as a poor man's status board!!

Thumbs up.

Thanks again,

Rob

 

[Trek-Fan]

Once again, my saga continues. I hope this will be the last issue I have, I cannot think of anything else that may bite me, but here goes:

Everyone was extremely happy with the results, thanks again for the help. But I am having an issue with the report that I want to display. I have attached the work in process database, and the report in question in the "status board" report. It has several subreports pulling from queries. The subreports seem to function normally if you open them, but they seem to only halfway work on the status board report. I was hoping someone could look at the work I have done and point me in the right direction before I go too much farther down the wrong road. As you can tell if you open the database, the "status board" report is not complete.

Thank you in advance, and I would really appreciate any feedback. I am hoping this is my last hurdle.

Thanks,
Rob

 

[Trek-Fan]

As a follow up to the last post, I know that dates can go out farther, but the status board only needs to show what is late, and what is upcoming in the following 6 days.