Crosstab Reports

[ellenr]

Hubby has MS, and several years ago I did a db so he could chronicle symptoms and meds. Then I added a form in which he could select symptoms crosstabbed with when he took what medication as information to take to his doctor. It all worked great. Somewhere along the trip to Office 365, it lost its mojo. The crosstab reports died. I have spent a bunch of time and can't reconnect the reports to the crosstab queries. I did discover that DAO was no longer in referrences. VBA code had disappeared. I have worked on Report1, called by the first button on the reports form, and hope someone out there can give me some insight. I am attaching the db, with several years of data deleted from the tables to get the size down.
Thanks

 

[MajP]

Your drugChoice table appears to have all the choices nulled out.

 

[ellenr]

click the reports button, then choose some symptoms and some drugs and from and to dates to populate the queries. The drugChoice table gets filled in that way.

 

[June7]

I did that and reports are blank. ReportDrugs1 UNION query is blank. There are no records in DrugRequestedDts table. What should be in this table? When should a record be created?

 

[ellenr]

June7, I downloaded the db and opened it. Nothing runs until I click on File and allow All in the security warning. Which brings up another question: how to "allow all" automatically when the program opens? I get it anytime I move locations. It is complaining about the vba.

 

[isladogs]

That's the standard warning for all newly downloaded or moved files
To automatically trust the file save it to a trusted location

To set trusted locations, go to Access options, Trust Centre, Trust Centre Settings, Trusted Locations then Add New Location

So if you set G:\MyFiles" as a trusted location, all files placed there will automatically open as trusted documents

 

[MajP]

I get it anytime I move locations.

I may be wrong, but I believe you can add yourself as a trusted publisher.
https://support.office.com/en-us/ar...S&rs=en-US&ad=US#bkmk_viewinfosecuritywarning

https://www.groovypost.com/howto/howto/office-2010-outlook-self-signed-digital-certificate/

 

[ellenr]

isladogs, I understand about Trusted Locations. June7 and others need to click allow in the security warning before going to the reports section--otherwise the vba routines won't run (or put it in one of their trusted locations).

MajP, that is interesting--I will do it. I do wish it would transfer.

 

[MajP]

MajP, that is interesting--I will do it. I do wish it would transfer.

If you are successful post back. I am curious because I have never tried it.

 

[isladogs]

All the trust settings work by creating entries in the registry.
So you can transfer those entries from one PC to another by exporting that section as a text file then running that on the other PC to import the entries on that.

I believe the Trusted Publishers item will only work with verified certificates that would need to be purchased e.g. from VeriSign or similar. Although you can 'self certificate' I don't believe the certificates created will work for that purpose

EDIT:
I actually do have such a certificate which I purchase annually from Comodo for use with applications distributed via my website.

There are two versions: one costs around £100 per year; the other is about £300 per year.
Neither are worth purchasing unless you intend to sell applications via the web

 

[ellenr]

MajP, for a cert that will only work on the computer on which it was made, just do a search for selfcert.exe.

 

[isladogs]

See my previous reply

 

[ellenr]

Thanks to all of you for your input! Now, how do I get my crosstab reports to work?