Access Report to PDF loses unique URLs

[nickaccess]

Make a copy with just the relevant parts of your db and save the required tables as local so its self contained.

Info on posting here: https://www.access-programmers.co.uk/forums/faq.php?faq=vb3_reading_posting#faq_vb3_attachments

Use the Advanced post editor to see all the buttons etc

Max file size for Access db is 2MB but you can zip it if necessary

https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=67036&stc=1&d=1501506112

https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=67037&stc=1&d=1501506112

Sorry it will not allow me to paste as a screen shot only as the above. I feel such a nerd, I am following the instructions.

I also cannot save the database, the screenshots show what I have done so far. Everything works apart from the PDF conversion where I lose the links.

Shout at me if you must but I am feeling a little bit of a nerd not being able to post screen shots or save the database. Thanks as always, Mr Nerd

 

[isladogs]

SHOUT??? WOULDN'T DREAM OF IT.
I might scream though...

Persevere and you'll get the hang of it.
Took me a few attempts to do inline images as well

So why can't you post it the db as you said its not confidential.
Doesn't matter if its not perfect -if it was you wouldn't need to ask any questions.

Not sure what the query design is there for.
It would be very helpful for you to at least post the code for your report 'button'
Also the datatype that the web address field is stored in. Text or hyperlink?

 

[nickaccess]

SHOUT??? WOULDN'T DREAM OF IT.
I might scream though...

Persevere and you'll get the hang of it.
Took me a few attempts to do inline images as well

So why can't you post it the db as you said its not confidential.
Doesn't matter if its not perfect -if it was you wouldn't need to ask any questions.

Not sure what the query design is there for.
It would be very helpful for you to at least post the code for your report 'button'
Also the datatype that the web address field is stored in. Text or hyperlink?

ridders, you are being a great help. I just run the report based on dates and the result is the report shown. Its a unique hyperlink for each record and it works brilliantly, until I pdf it as you well know by now. I used the hyperlink builder to create a url. I have tried doing text but did not understand what I was doing and it did not work. Maybe I need step by step instructions hahahahaha. I feel like a child now.

https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=67039&stc=1&d=1501508424
https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=67040&stc=1&d=1501508424

If I copy the shortcut nothing happens until I paste and all I get it this. Sorry again, thick-oh working.

 

[Minty]

When I had an issue with this I created labels with the hyperlink address on them. Seems to work.

 

[isladogs]

I suggest you scrap the hyperlink address code.
Often its more trouble than its worth

Instead either use a label (as Minty's suggests) or a textbox with an event procedure to run when you click on 'Click here to view planning application'

I've just run this exact code using a report label (and also on a textbox)
Both worked!

If you want to use your current label, then its:

Sub Label57_Click()

Application.FollowHyperlink "https://planning.plymouth.gov.uk/online-applications/applicationDetails.do?activeTab=summary&keyVal=" & [KEYVAL] & ""

End Sub

NOTE:
1. It should be a ? after 'do' as above - not = as you had
2. You can always format your label caption to look like a hyperlink (blue/underlined)
3. Make sure KEYVAL is a TEXT field datatype, not a NUMBER and not a HYPERLINK

 

[nickaccess]

I suggest you scrap the hyperlink address code.
Often its more trouble than its worth

Instead either use a label (as Minty's suggests) or a textbox with an event procedure to run when you click on 'Click here to view planning application'

I've just run this exact code using a report label (and also on a textbox)
Both worked!

If you want to use your current label, then its:

Sub Label57_Click()

Application.FollowHyperlink "https://planning.plymouth.gov.uk/online-applications/applicationDetails.do?activeTab=summary&keyVal=" & [KEYVAL] & ""

End Sub

NOTE:
1. It should be a ? after 'do' as above - not = as you had
2. You can always format your label caption to look like a hyperlink (blue/underlined)
3. Make sure KEYVAL is a TEXT field datatype, not a NUMBER and not a HYPERLINK

Maybe I am just getting it wrong and need step by step instructions. In the event tab (on click) I am putting the Application.FollowHyperlink I then put the hyperlink as above into the hyperlink address (do I say YES it is a hyperlink), with yes click it would not work, with no clicked it would not work. SORRY I cannot apologise enough. I am definitely giving up on this one. I done it as a label and as a text box all I get is this (jpg attached). SORRY again, I am being thick. There is no icon with tears on it.

Thanks a million for everything suggestion etc.
https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=67075&stc=1&d=1501580560

 

[JHB]

Post your database as suggested.

 

[Gasman]

I have just tried the code below which works

I got a reference from your webiste and then the keyval from the url
So despite that being a string using it as a number works?
If I add the ' around the variable as you would with a string it does not work as that becomes part of the url?

The ' gets replaced by %27
https://planning.plymouth.gov.uk/on...s.do?activeTab=summary&keyVal='OSO1BSMMJ0U00'

Sub TestHyperlink()
Dim url As String, keyval As String
keyval = "OSO1BSMMJ0U00"
url = "https://planning.plymouth.gov.uk/online-applications/applicationDetails.do?activeTab=summary&keyVal=" & keyval
Debug.Print url
Application.FollowHyperlink url
End Sub

Doh, just seen Ridders has already carried out an almost similar test

 

[isladogs]

Maybe I am just getting it wrong and need step by step instructions. In the event tab (on click) I am putting the Application.FollowHyperlink I then put the hyperlink as above into the hyperlink address (do I say YES it is a hyperlink), with yes click it would not work, with no clicked it would not work. SORRY I cannot apologise enough. I am definitely giving up on this one. I done it as a label and as a text box all I get is this (jpg attached). SORRY again, I am being thick. There is no icon with tears on it.

It would have been quicker to fix if you had posted your db
Don't give up!

I'll try & put you out of your misery!
Attached are detailed instructions (zipped so I could upload it to the forum) - normally I wouldn't do this but for you I'm making an exception!

If you follow these it SHOULD work.

 

[nickaccess]

Yes you can post screenshots like this one which is the page opened using my code in post #11

See here for instructions https://www.access-programmers.co.uk/forums/showthread.php?t=194680

However it might save time if you could post a stripped down version of your db removing any confidential data

Hi, I have followed your instructions to the tee, it all works wonderfully like it did last time with my coding, but as soon as its PDF this time it loses the link so it just a dead box. The db is 9MB is size so cannot send it. Also I do not have On Load, I only have On Click, On Dbl Click, Mouse Up, Mouse Dn, Mouse Move. I think I owe someone a big pint hahahaha.

I think I just may give up on this one.

 

[isladogs]

No you're not giving up - that would be silly

Unattached labels do have an On Click event.
Apologies - the instructions I sent you should have said that - not On Load - my error
I've amended the contents of the zip file in #29

Hopefully that will FINALLY fix it for you.
If not, go ahead & post it.

BUT as I wrote in a PM, don't post the whole db.
We only want the relevant parts
So in your case - the report plus any tables / queries and possibly forms needed to go with it
Copy these to a new database. Run it to check it works without error.

Then compact it - Tools - Compact & Repair
If its still over 2MB, zip it then post

 

[nickaccess]

No you're not giving up - that would be silly

Unattached labels do have an On Click event.
Apologies - the instructions I sent you should have said that - not On Load - my error
I've amended the contents of the zip file in #29

Hopefully that will FINALLY fix it for you.
If not, go ahead & post it.

BUT as I wrote in a PM, don't post the whole db.
We only want the relevant parts
So in your case - the report plus any tables / queries and possibly forms needed to go with it
Copy these to a new database. Run it to check it works without error.

Then compact it - Tools - Compact & Repair
If its still over 2MB, zip it then post

Here it is. There are two reports they are both the same. The TEST one is one I have been messing around with. The main one has the original code in it looking for the keyVal and it works brilliantly, but as you all know by now, I have been saying it a million times (sorry) when I PDF it, it all dies by adding the file path to the front of the URL when I save it, so it will not work. Let me know if you need anything else. You will not be able to test it(?) as it is normally linked to a live database (OBDC) to gather the information. Good Luck, sorry if I have been a pain and sorry if you can see immediately that I am really bad at working in Access.

Thank you.
https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=67100&stc=1&d=1501658540

 

[isladogs]

OK - today's silly error ....
The link on your last post is to the post itself so if you click on it, it also downloads the attached file!

I've modified BOTH reports adding the one line of code I posted back in post 25
As you didn't upload any tables, I can't test it.

This is what you should have done to upload the linked table with your database copy:
1. Import the table - as it's a link this actually re-creates the link.
2. Right click on the linked table in the Navigation Pane & select Convert to Local Table
3. Delete all but a few records. Keep about 10. Modify any confidential data in the remaining records if necessary.
4. Compact the db then upload (or zip & upload)

You MAY get an error when you click on the reports saying it was formatted for a specific printer on my PC (Epson AL1600). That's because it couldn't find your printer on my PC. If so, allow Access to change it back

I strongly recommend to devote time to studying some YouTube videos on working with Access. Those by Steve Bishop are particularly useful as they form a complete course - actually 3 courses - beginner/intermediate / advanced.

Here is the list of videos in the first of these courses:
https://www.youtube.com/playlist?list=PLYMOUCVo86jEeMMdaaq03jQ_t9nFV737s

Even experienced programmers find his videos useful as they are so wide ranging & comprehensive in scope

The videos work best if you have sound - maybe wear earphones if in the office

 

[nickaccess]

OK - today's silly error ....
The link on your last post is to the post itself so if you click on it, it also downloads the attached file!

I've modified BOTH reports adding the one line of code I posted back in post 25
As you didn't upload any tables, I can't test it.

This is what you should have done to upload the linked table with your database copy:
1. Import the table - as it's a link this actually re-creates the link.
2. Right click on the linked table in the Navigation Pane & select Convert to Local Table
3. Delete all but a few records. Keep about 10. Modify any confidential data in the remaining records if necessary.
4. Compact the db then upload (or zip & upload)

You MAY get an error when you click on the reports saying it was formatted for a specific printer on my PC (Epson AL1600). That's because it couldn't find your printer on my PC. If so, allow Access to change it back

I strongly recommend to devote time to studying some YouTube videos on working with Access. Those by Steve Bishop are particularly useful as they form a complete course - actually 3 courses - beginner/intermediate / advanced.

Here is the list of videos in the first of these courses:
https://www.youtube.com/playlist?list=PLYMOUCVo86jEeMMdaaq03jQ_t9nFV737s

Even experienced programmers find his videos useful as they are so wide ranging & comprehensive in scope

The videos work best if you have sound - maybe wear earphones if in the office

Hello,

Thank you so much for the links I will watch them all.

I think I have done everything that needs to be done. Can you please have a look and let me know. Don't worry if its wrong, I will give it a rest and look at it another time. There should be some things to look at and work on. Good Luck and thank you so much for all of your help.

https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=67105&stc=1&d=1501678461

 

[isladogs]

Thank you so much for the links I will watch them all.

Great but not all on the same day ....

I think I have done everything that needs to be done. Can you please have a look and let me know.

Does it work at your end?

This is your query SQL:

SELECT UNIFORM_DCAPPL.REFVAL, UNIFORM_DCAPPL.keyval, UNIFORM_DCAPPL.DATEAPRECV, UNIFORM_DCAPPL.DATEAPVAL, UNIFORM_DCAPPL.DATEEXPIRY, UNIFORM_DCAPPL.PROPOSAL, OfficerNameV7([offcode]) AS Officer_Name, [List_WARDS (Ward Names)].XTRATEXT, ONELINEADDRESS([ADDRESS]) AS [SITE ADDRESS], UNIFORM_DCAPPL.DATEAPACKN, UNIFORM_DCAPPL.ADDRESS
FROM UNIFORM_DCAPPL INNER JOIN [List_WARDS (Ward Names)] ON UNIFORM_DCAPPL.WARD = [List_WARDS (Ward Names)].CODEVALUE
WHERE (((UNIFORM_DCAPPL.REFVAL) Not Like "*CDM*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*MAJ*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*MIN*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*HOU*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*31*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*GP2*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*PRDE*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*AMD*") AND ((UNIFORM_DCAPPL.DATEAPACKN) Between [Start Date] And [End Date]));

If you had tried it before posting. you'd realise its looking for a table called 'List_WARDS (Ward Names)' which you didn't include
Can you add that table , check the file and re-post

Once I have everything, it will only take a couple of seconds to check it.

P.S. I also strongly recommend that you don't have spaces or special characters like ( ) in table & field names
e.g. List_WARDS (Ward Names) should be something like ListWardNames or WardNames

 

[nickaccess]

Great but not all on the same day ....



Does it work at your end?

This is your query SQL:

SELECT UNIFORM_DCAPPL.REFVAL, UNIFORM_DCAPPL.keyval, UNIFORM_DCAPPL.DATEAPRECV, UNIFORM_DCAPPL.DATEAPVAL, UNIFORM_DCAPPL.DATEEXPIRY, UNIFORM_DCAPPL.PROPOSAL, OfficerNameV7([offcode]) AS Officer_Name,[List_WARDS (Ward Names)].XTRATEXT, ONELINEADDRESS([ADDRESS]) AS [SITE ADDRESS], UNIFORM_DCAPPL.DATEAPACKN, UNIFORM_DCAPPL.ADDRESS
FROM UNIFORM_DCAPPL INNER JOIN[List_WARDS (Ward Names)] ON UNIFORM_DCAPPL.WARD =[List_WARDS (Ward Names)].CODEVALUE
WHERE (((UNIFORM_DCAPPL.REFVAL) Not Like "*CDM*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*MAJ*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*MIN*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*HOU*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*31*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*GP2*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*PRDE*" And (UNIFORM_DCAPPL.REFVAL) Not Like "*AMD*") AND ((UNIFORM_DCAPPL.DATEAPACKN) Between [Start Date] And [End Date]));

If you had tried it before posting. you'd realise its looking for a table called 'List_WARDS (Ward Names)' which you didn't include
Can you add that table , check the file and re-post

Once I have everything, it will only take a couple of seconds to check it.

P.S. I also strongly recommend that you don't have spaces or special characters like ( ) in table & field names
e.g. List_WARDS (Ward Names) should be something like ListWardNames or WardNames

I think I have managed to get the Ward Names in. If I take out the () would that affect anything? I have a lot of lists that have () in them, carried over from a old system (not my way of doing them). Thank you again, the light is shining at the end of the tunnel
https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=67121&stc=1&d=1501744881

 

[isladogs]

I still can't test the report as there's another table missing: UNI7LIVE_CNCODE or something like that. This is used by the 'List_WARDS (Ward Names)' query which is needed for your report

Add the table and please CHECK the queries / reports open without error before reposting .... again!

 

[nickaccess]

I still can't test the report as there's another table missing: UNI7LIVE_CNCODE or something like that. This is used by the 'List_WARDS (Ward Names)' query which is needed for your report

Add the table and please CHECK the queries / reports open without error before reposting .... again!

Sorry I am making a pigs ear out of this one

 

[nickaccess]

Sorry I am making a pigs ear out of this one

Hi ridders

Not sure what is happening now. I have put the CNCODE into the system and deleted everything on it apart from Wards. It will not run without error, as in the screenshot. I am sure there is enough records to pull through but still unsure why this is happening. Sorry, I have caused you a lot of work but I am thinking of just scrapping this whole thing now, its getting too much and a lot of time especially from you. I am really grateful and I wish I could at least buy you a pint hahaha. Thanks as always, Nick

https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=67125&stc=1&d=1501755390

 

[isladogs]

If you can fix the table issue I'm happy to look at it.
Having got this far, it seems a shame to give up on it.

Alternatively if you can copy the whole thing with all tables converted to local tables, compact the db then zip it will it fit under 2Mb limit?