X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Ms Access Automation Question, Access 2007    
post Nov 28 2017, 08:11 PM

Posts: 7
Joined: 15-November 17

I apologise in advance if this question has been answered previously.

I have some code which checks if an address is valid against the USPS website. The code works great and I view it via a datasheet view on a form and trigger it using On Got Focus (this way I can use the down arrow and get the result and repeat). I would like to automate this process so I click (or get focus) once and can then go to bed and leave it to do it's thing. I have tried inumerable ways to get to the next record, trigger the code and repeat to no avail.

My code is:

Private Sub Shipping_Agent_Code_GotFocus()
If Not IsNull(Me![Shipping Agent Code]) Then

Dim strURL, readText As String
Dim objShell, objIE As Object
Dim MyPos1
Dim AddError As String
Dim PauseTime, Start, Finish, TotalTime

readText = ""
strURL = "https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&companyName=&address1=" & [Edited Ship] & "&address2=&city=&state=Select&urbanCode=&postalCode=&zip=" & [Short Zip]

Set objShell = CreateObject("Wscript.Shell")
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Visible = False
.Silent = True
.Navigate (strURL)
Do While .readyState <> 4
With .Document.Body
readText = .Innertext
End With
End With

Set objIE = Nothing
Set objShell = Nothing

MyPos = InStr(readText, "To learn about")
If MyPos > 0 Then
AddError = "Wait"
End If

MyPos = InStr(readText, "Several addresses")
If MyPos > 0 Then
AddError = "Incomplete"
End If

MyPos = InStr(readText, "Here's the full address")
If MyPos > 0 Then
AddError = "Valid"
End If

MyPos = InStr(readText, "Unfortunately, this address wasn't found")
If MyPos > 0 Then
AddError = "Not Found"
End If

MyPos = InStr(readText, "The address you provided is not recognized")
If MyPos > 0 Then
AddError = "Not Recognised"
End If

MyPos = InStr(readText, "This service is currently unavailable")
If MyPos > 0 Then
AddError = "Site Down"
End If

If AddError = "Wait" Then
PauseTime = 15
Start = Timer
Do While Timer < Start + PauseTime
Finish = Timer
TotalTime = Finish - Start
GoTo VerifyAddress
[Forms]![Orders Query]![Status] = AddError

End If
End If

End Sub

Any help would be truly appreciated.
Go to the top of the page
post Nov 28 2017, 09:06 PM

Posts: 2,507
Joined: 27-February 09

It seems to me that you would want to do something like this:
1. Open a recordset of all the addresses you want to validate (use a query to get just the ones you want?)

2. loop through that recordset and ...

3. pass the important pieces to a function that
(a) builds the complete web address
(b) submits it
© grabs the result
(d) uses the result to either return as is, or returns a boolean (true/false)

4. Update the current record with the result from the function.
5. Save the current record.
6. go to the next record.

Your validation function would be something like

Function IsAddressValid(ByVal strAddress As String, ...) As Boolean
... do all that building the concatenated address HTML thing
... execute it in IE (or whatever it was.. sorry, wasn't paying great attention!)
... return some value. If it's not a boolean, you can compare it to whatever the "Success" return value is.
End Function

then your outer code that calls this would be something like:

Dim rsAddresses as dao.recordset
dim qdfAddresses As DAO.Querydef ' points to a query object.

Set qdfAddresses = dbengine(0)(0).QueryDefs("NameOfSelectQueryThatReturnsTheData")
set rsAddresses = qdf.OpenRecordset ' open a recordset of the addresses to process
'-- process each address in the recordset
While not rsAddresses.EOF
rsAddresses.Fields("Valid") = IsAddressValid(rsAddresses.Fields("StreetAddress")) '-- set the value of the "Valid" field in the table (I made the name up!) to the result of the function
rsAddresses.Update ' save the changes
rsAddresses.MoveNext ' go to the next record
End While

Apologies for the untested crummy code, but I'd rather make sure that's what you're after before chasing after something you didn't want.
Go to the top of the page
post Nov 28 2017, 09:43 PM

Posts: 7
Joined: 15-November 17

Hi MadPiet,

Thank you for your speedy (and completely mindbogling) response.

Most of what you suggest is absolutely true, I need to loop through the recordset, update the current record and go to the next record.

The Boolean logic bit makes my head hurt so I am sure I can do without that bit.

Save the record and go on to the next record.

I am not actually testing the address but rather getting a response (in text format) whether that address is valid.

The HTML thing is simply what the USPS site expects to see when you go to their Check A Zip Code page and enter the first line of address and 1st 5 digits of zip code.

It is the loop I am interested in and how do I get this code to run for each of the 1500 (or so) records I have.
Go to the top of the page
post Nov 28 2017, 10:01 PM

UtterAccess VIP
Posts: 10,468
Joined: 6-December 03
From: Telegraph Hill


I agree with Piet - create the function. You can make it return AddError instead of a boolean.

Then you can just do the update in an update query - no need even for a recordset
UPDATE YourTable
SET Status = IsAddressValid([Edited Ship], [Short Zip])
WHERE [Shipping Agent Code] IS NOT NULL;


Go to the top of the page
post Nov 28 2017, 10:30 PM

Posts: 2,507
Joined: 27-February 09

Silly me! Right, use an update query!!!
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 12:07 AM