Postcode lookup stopped working (1 Viewer)

wackywoo105

Registered User.
Local time
Yesterday, 16:59
Joined
Mar 14, 2014
Messages
203
[Solved] Postcode lookup stopped working

The excellent piece of code contained here that allows uk postcode address lookup:

https://access-programmers.co.uk/forums/showthread.php?t=216444

has recently stopped working.

It has stopped working on a windows 7 machine. It generates a run-time error saying "An error occurred in the secure channel support". This happens at the .send line here:

Code:
    Set winReq = New WinHttpRequest
    With winReq
        .Open "GET", sStr, False
        .Send
        HTM = Split(Replace(.ResponseText, """", "'"), "<")
        If .Status <> 200 Then
            MsgBox ("Address not found")
            Exit Sub
        End If
    End With

It still works fine on my windows 10 machine.

I have spent some time on google but am struggling to come up with a solution. Can anyone help?

Please note it previously stopped working in 2014 but the following fixed it:

Replace:

"td class='address'>"

with

"td class='js-ont-full-address ont-hidden-on-smaller-than-tablet'>"

I think there are 3 that need to be changed.

I did open my old thread up, but thought it may be better to start new one, that isn't marked as solved.
 
Last edited:

wackywoo105

Registered User.
Local time
Yesterday, 16:59
Joined
Mar 14, 2014
Messages
203
Sorry url was wrong. Fixed now.

https://access-programmers.co.uk/forums/showthread.php?t=216444

I have looked at yours recently, which is amazing and much more complex but haven't got the time to try and implement it atm.

I also only really need the address lookup from postcode and was unsure where to start with just having that.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 00:59
Joined
Jan 14, 2017
Messages
18,246
OK just tried again on a different PC and the link worked
Not seen this before but it uses similar code to my examples

192.com is now a secure site
Change the line below adding the 's'
Code:
sStr = "http[COLOR="Red"]s[/COLOR]://www.192.com/places/"

I've added a Debug.Print line: Debug.Print .ResponseText
There are no addresses being picked up in the response text

I've tried changes I often use in my code:
a) Replaced .Open "GET", sStr, False with .Open "POST", sStr, False
b) Replaced .Send with .Send ""
Unfortunately, neither helped

I'm sure its fixable but I'm busy with other projects at the moment
Perhaps you now need an API key to perform this search using code.
Have you checked the 192.com documentation?
 

wackywoo105

Registered User.
Local time
Yesterday, 16:59
Joined
Mar 14, 2014
Messages
203
Thanks for your help. Having done some more research I found the following elsewhere on this forum:

Changing

Code:
Dim winReq As WinHttpRequest
Set winReq = New WinHttpRequest

to

Code:
Dim winReq As Object
Set winReq = CreateObject("MSXML2.XMLHTTP")

Fixed the issue along with going https.

Do you possibly have a cut down version of your postcode lookup similar to the one I'm using?
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 00:59
Joined
Jan 14, 2017
Messages
18,246
I had to do a bit more to get it working including the changes you mentioned in post 1:

Replace:
"td class='address'>"
with
"td class='js-ont-full-address ont-hidden-on-smaller-than-tablet'>"

However, bearing in mind the address data is free, I thought I'd do a little more to it:



As you can see I've added a list count and a map.
Is that all you actually need?

However, in my opinion, it needs a bit more work to fix the address list :
a) save in alphabetical order (for house names) or number order
b) Fix issue where certain addresses have more fields than others

I would also suggest saving the addresses for future use

Compare with the main form on my postal address app for the same postcode



Would you use any of the additional info on that screen?
 

Attachments

  • CaptureUKPAF.jpg
    CaptureUKPAF.jpg
    104.3 KB · Views: 416
  • Capture.jpg
    Capture.jpg
    59.9 KB · Views: 401
Last edited:

wackywoo105

Registered User.
Local time
Yesterday, 16:59
Joined
Mar 14, 2014
Messages
203
I made a few changes in how I use it. After I enter/update a postcode the code triggers. It gets sorted but annoyingly it can go 19, 2, 20 and 29, 3, 30 etc. When I double click a list item, it populates the individual address fields in my form, and adjust the address dependent on how many items there are. My address consists of add1, add2, town, county and postcode so the data split across these.

I like as it means addresses are consistent and it reduces input errors. I tried your database and much prefer the output but have no idea how to shrink it down to just creating the list box.
 

isladogs

MVP / VIP
Local time
Today, 00:59
Joined
Jan 14, 2017
Messages
18,246
It gets sorted but annoyingly it can go 19, 2, 20 and 29, 3, 30 etc. When I double click a list item, it populates the individual address fields in my form, and adjust the address dependent on how many items there are. My address consists of add1, add2, town, county and postcode so the data split across these.

I like as it means addresses are consistent and it reduces input errors. I tried your database and much prefer the output but have no idea how to shrink it down to just creating the list box.

The 'strange sorting' is because its a string value so the numbers are being sorted 'alphabetically'

The list in my existing app shows the full address string but is sorted behind the scenes by building number or name as appropriate. The address components are saved automatically to various fields in a table as well as the address line & address block.There are many additional fields including lat/long data as these form part of the ideal postcodes dataset I normally use

I can do much the same for this cut down version if its of any use to you.
The dataset includes the following fields using a random address from the postcode I used in my test:

Code:
<input type="hidden" name="subBuild" value="">				                        
<input type="hidden" name="buildName" value="">				                        
<input type="hidden" name="buildNo" value="34">				                        
<input type="hidden" name="street" value="Berkeley Road">				                        
<input type="hidden" name="subStreet" value="">				                        
<input type="hidden" name="postTown" value="Bristol">				                        
<input type="hidden" name="locality" value="">				                        
<input type="hidden" name="postcode" value="BS7 8HE">				                        
<input type="hidden" name="apGre" value="3587922">                  
<input type="hidden" name="apGrn" value="1753793">				                        
<input type="hidden" name="gre" value="358879">				                        
<input type="hidden" name="grn" value="175352">			                            
<input type="submit" value="Buy a Property Report">	38, Berkeley Road, Bristol, Avon, BS7 8HE				
<td class="last-sold">	05 Nov 2015
<td class="price">	£740,000

I've no idea what apGre, apGrn, Gre, Grn are. Do you know?

However, from your answer, I'm not sure whether this offers anything more than you already have (or need).
Or to put it another way, if my existing app has too many features for your needs, what don't you need?

I'm happy to complete it as it offers a free method of getting addresses & house price info. At this stage, I haven't decided what to include/exclude

If interested in following up, I suggest you reply by PM or using my email link in the signature line below
 
Last edited:

wackywoo105

Registered User.
Local time
Yesterday, 16:59
Joined
Mar 14, 2014
Messages
203
Not that it matters much but if you search say BL4 0AX you will get X, Maple Road, Bolton, Lancashire, BL4 0AX. If using your version you get X, Maple Rd, Farnworth, Bolton BL4 0AX.

It's not a big deal but has always annoyed me slightly. The 192 data doesn't differentiate between local areas in a town/city.

So effectively I would just like the same as is already in the code but perhaps pulling the data from elsewhere that includes this more localized info.

I am also worried a day will come when the 192 version will fail and I can't fix it. I've had to fix it twice now. At least if I have it coming from a separate local database it will continue to work until I change something.
 

isladogs

MVP / VIP
Local time
Today, 00:59
Joined
Jan 14, 2017
Messages
18,246
:)
Not that it matters much but if you search say BL4 0AX you will get X, Maple Road, Bolton, Lancashire, BL4 0AX. If using your version you get X, Maple Rd, Farnworth, Bolton BL4 0AX.

It's not a big deal but has always annoyed me slightly. The 192 data doesn't differentiate between local areas in a town/city.

So effectively I would just like the same as is already in the code but perhaps pulling the data from elsewhere that includes this more localized info.

I am also worried a day will come when the 192 version will fail and I can't fix it. I've had to fix it twice now. At least if I have it coming from a separate local database it will continue to work until I change something.

Different providers give different levels of info:
a) 192 data is free but less detailed and has no latitude/longitude data
b) Ideal postcodes provides much more info though it costs 3p per postcode search .... you can pay me later ;)
Having said that you can get 20 free postcode searches a day using the developers API key (built in to my code)
c) Other providers also charge and all have their own data formats e.g. GetAddress output for that postcode is:
Code:
{"latitude":53.5469574,"longitude":-2.4169722,"addresses":["1 Maple Road, , , , Farnworth, Bolton, Lancashire","2 Maple Road, , , , Farnworth, Bolton, Lancashire","3 Maple Road, , , , Farnworth, Bolton, Lancashire","4 Maple Road, , , , Farnworth, Bolton, Lancashire","5 Maple Road, , , , Farnworth, Bolton, Lancashire","6 Maple Road, , , , Farnworth, Bolton, Lancashire"]}

Notice, it doesn't even include the postcode in the output. I took this up with them last summer & their rather dumb response was
the postcode is in the search key so its not needed in the address

During my research I tried several more providers. Ideal Postcodes was in my view the best in terms of info, support & was I felt reasonably priced

So your pay your money (or not) and make your choice

I expect at some point 192.com will charge for this data as well.
Therefore, I suggest using my UKPAF database with Ideal Postcodes data and just ignoring anything you don't need.
The Standard version is very reasonably priced and if you do no more than 20 searches a day there are no additional costs.
Its available from all good websites now (well from mine anyway) :D
 
Last edited:

wackywoo105

Registered User.
Local time
Yesterday, 16:59
Joined
Mar 14, 2014
Messages
203
Sorry to revive this but it has stopped working again. It just gives address not found. When I first put the url into chrome it works fine and shows the addresses. After a bit it says I'm blocked.

Why have I been blocked?​

This website is using a security service to protect itself from online attacks. The action you just performed triggered the security solution. There are several actions that could trigger this block including submitting a certain word or phrase, a SQL command or malformed data.

What can I do to resolve this?​

You can email the site owner to let them know you were blocked. Please include what you were doing when this page came up and the Cloudflare Ray ID found at the bottom of this page.

Code:
Set winReq = CreateObject("MSXML2.XMLHTTP")
    With winReq
        .Open "GET", sStr, False
        .Send
       
        Debug.Print .ResponseText
        Debug.Print .GetAllResponseHeaders
       
        HTM = Split(Replace(.ResponseText, """", "'"), "<")
        If .Status <> 200 Then
            MsgBox ("Address not found")
            'Exit Sub
        End If
    End With

Can anyone help with a way to get the html data into variable without triggering the block?

Debug.Print .ResponseText
Debug.Print .GetAllResponseHeaders
give:

<!DOCTYPE html>
<!--[if lt IE 7]> <html class="no-js ie6 oldie" lang="en-US"> <![endif]-->
<!--[if IE 7]> <html class="no-js ie7 oldie" lang="en-US"> <![endif]-->
<!--[if IE 8]> <html class="no-js ie8 oldie" lang="en-US"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js" lang="en-US"> <!--<![endif]-->
<head>
<title>Attention Required! | Cloudflare</title>
<meta charset="UTF-8" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<meta name="robots" content="noindex, nofollow" />
<meta name="viewport" content="width=device-width,initial-scale=1" />
<link rel="stylesheet" id="cf_styles-css" href="/cdn-cgi/styles/cf.errors.css" />
<!--[if lt IE 9]><link rel="stylesheet" id='cf_styles-ie-css' href="/cdn-cgi/styles/cf.errors.ie.css" /><![endif]-->
<style>body{margin:0;padding:0}</style>


<!--[if gte IE 10]><!-->
<script>
if (!navigator.cookieEnabled) {
window.addEventListener('DOMContentLoaded', function () {
var cookieEl = document.getElementById('cookie-alert');
cookieEl.style.display = 'block';
})
}
</script>
<!--<![endif]-->


</head>
<body>
<div id="cf-wrapper">
<div class="cf-alert cf-alert-error cf-cookie-error" id="cookie-alert" data-translate="enable_cookies">Please enable cookies.</div>
<div id="cf-error-details" class="cf-error-details-wrapper">
<div class="cf-wrapper cf-header cf-error-overview">
<h1 data-translate="block_headline">Sorry, you have been blocked</h1>
<h2 class="cf-subheadline"><span data-translate="unable_to_access">You are unable to access</span> 192.com</h2>
</div><!-- /.header -->

<div class="cf-section cf-highlight">
<div class="cf-wrapper">
<div class="cf-screenshot-container cf-screenshot-full">

<span class="cf-no-screenshot error"></span>

</div>
</div>
</div><!-- /.captcha-container -->

<div class="cf-section cf-wrapper">
<div class="cf-columns two">
<div class="cf-column">
<h2 data-translate="blocked_why_headline">Why have I been blocked?</h2>

<p data-translate="blocked_why_detail">This website is using a security service to protect itself from online attacks. The action you just performed triggered the security solution. There are several actions that could trigger this block including submitting a certain word or phrase, a SQL command or malformed data.</p>
</div>

<div class="cf-column">
<h2 data-translate="blocked_resolve_headline">What can I do to resolve this?</h2>

<p data-translate="blocked_resolve_detail">You can email the site owner to let them know you were blocked. Please include what you were doing when this page came up and the Cloudflare Ray ID found at the bottom of this page.</p>
</div>
</div>
</div><!-- /.section -->

<div class="cf-error-footer cf-wrapper w-240 lg:w-full py-10 sm:py-4 sm:px-8 mx-auto text-center sm:text-left border-solid border-0 border-t border-gray-300">
<p class="text-13">
<span class="cf-footer-item sm:block sm:mb-1">Cloudflare Ray ID: <strong class="font-semibold">82278fd44f660764</strong></span>
<span class="cf-footer-separator sm:hidden">&bull;</span>
<span id="cf-footer-item-ip" class="cf-footer-item hidden sm:block sm:mb-1">
Your IP:
<button type="button" id="cf-footer-ip-reveal" class="cf-footer-ip-reveal-btn">Click to reveal</button>
<span class="hidden" id="cf-footer-ip">148.252.128.195</span>
<span class="cf-footer-separator sm:hidden">&bull;</span>
</span>
<span class="cf-footer-item sm:block sm:mb-1"><span>Performance &amp; security by</span> <a rel="noopener noreferrer" href="https://www.cloudflare.com/5xx-error-landing" id="brand_link" target="_blank">Cloudflare</a></span>

</p>
<script>(function(){function d(){var b=a.getElementById("cf-footer-item-ip"),c=a.getElementById("cf-footer-ip-reveal");b&&"classList"in b&&(b.classList.remove("hidden"),c.addEventListener("click",function(){c.classList.add("hidden");a.getElementById("cf-footer-ip").classList.remove("hidden")}))}var a=document;document.addEventListener&&a.addEventListener("DOMContentLoaded",d)})();</script>
</div><!-- /.error-footer -->


</div><!-- /#cf-error-details -->
</div><!-- /#cf-wrapper -->

<script>
window._cf_translation = {};


</script>

<script>(function(){var js = "window['__CF$cv$params']={r:'82278fd44f660764',t:'MTY5OTM4MDkyOC42OTUwMDA='};_cpo=document.createElement('script');_cpo.nonce='',_cpo.src='/cdn-cgi/challenge-platform/scripts/jsd/main.js',document.getElementsByTagName('head')[0].appendChild(_cpo);";var _0xh = document.createElement('iframe');_0xh.height = 1;_0xh.width = 1;_0xh.style.position = 'absolute';_0xh.style.top = 0;_0xh.style.left = 0;_0xh.style.border = 'none';_0xh.style.visibility = 'hidden';document.body.appendChild(_0xh);function handler() {var _0xi = _0xh.contentDocument || _0xh.contentWindow.document;if (_0xi) {var _0xj = _0xi.createElement('script');_0xj.innerHTML = js;_0xi.getElementsByTagName('head')[0].appendChild(_0xj);}}if (document.readyState !== 'loading') {handler();} else if (window.addEventListener) {document.addEventListener('DOMContentLoaded', handler);} else {var prev = document.onreadystatechange || function () {};document.onreadystatechange = function (e) {prev(e);if (document.readyState !== 'loadin
g') {document.onreadystatechange = prev;handler();}};}})();</script><script defer src="https://static.cloudflareinsights.com/beacon.min.js/v84a3a4012de94ce1a686ba8c167c359c1696973893317" integrity="sha512-euoFGowhlaLqXsPWQ48qSkBSCFs3DPRyiwVu3FjR96cMPx+Fr+gpWRhIafcHwqwCqWS42RZhIudOvEI+Ckf6MA==" data-cf-beacon='{"rayId":"82278fd44f660764","b":1,"version":"2023.10.0","token":"7f72d5af8d60487a9417eeb974bbe6c7"}' crossorigin="anonymous"></script>
</body>
</html>

date: Tue, 07 Nov 2023 18:15:28 GMT
content-type: text/html; charset=UTF-8
x-frame-options: SAMEORIGIN
referrer-policy: same-origin
cache-control: max-age=15
expires: Tue, 07 Nov 2023 18:15:43 GMT
server: cloudflare
cf-ray: 82278fd44f660764-MAN
alt-svc: h3=":443"; ma=86400
 

isladogs

MVP / VIP
Local time
Today, 00:59
Joined
Jan 14, 2017
Messages
18,246
I am aware that 192.com blocked access to their data a few weeks ago. I always suspected this would happen at some point.
The code still opens the correct URL as you can see if you open the same URL direct. However, I think it’s very unlikely that this app will ever work again in the future. I don’t think there is anything I can do to overcome the ‘firewall’ that has been imposed by 192.com. I will update my web article to state that isn’t currently working and may never do so again.

In my opinion, you need to do one of the following
1. Go to the 192.com website to get the addresses (currently free)
2. Pay 192.com for a number of searches (very expensive)
3. Pay for another address provider service e.g. Royal Nail or Ideal Postcodes.
4. if you choose the latter, you could use it with my UKPAF app but that is a commercial application
 

isladogs

MVP / VIP
Local time
Today, 00:59
Joined
Jan 14, 2017
Messages
18,246
I have to say, that's not how it came across! Anyway, I gave you 4 suggested alternatives
 

wackywoo105

Registered User.
Local time
Yesterday, 16:59
Joined
Mar 14, 2014
Messages
203
I have to say, that's not how it came across! Anyway, I gave you 4 suggested alternatives
I know it's been a while but I went with option 5. Recode it using IE, but keep the browser hidden (something you can't do with edge). It now works again.
 

isladogs

MVP / VIP
Local time
Today, 00:59
Joined
Jan 14, 2017
Messages
18,246
It has indeed been a while! I'm unclear what your answer means.
The free Postcode Address Lookup app used web scraping. It never used a browser control although various other similar apps do so.

If you're saying you've managed to get it working again with 192.com using the old browser control, I'd be interested in seeing it.
Please could you email it to me using the link in my signature line.
 

Users who are viewing this thread

Top Bottom