:banghead:I have researched this to death I have a module that goes out to the USPS website and populates a table with all the zip codes. At one time I did have it working but now I can't seem to get it to work keep getting Run-Time error 424 "Object Required"
Anyways here is the code I am working with I am just not seeing it I declared the object, even put Option Explicit and messaged the string out to make sure the SQL line was right. I didn't see anything.
Anyways here is the code I am working with I am just not seeing it I declared the object, even put Option Explicit and messaged the string out to make sure the SQL line was right. I didn't see anything.
Code:
Option Compare Database
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub GetZip()
Dim Database2 As DAO.Database
Dim rstRecords As DAO.Recordset
Dim CityRecords As DAO.Recordset
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Update As String
Dim StateName As String
Dim Website As String
Dim ZipCode As Variant
Dim CityName As String
Dim Element As Variant
Dim FindRecordCount As Integer
Dim FindRecordCountB As Integer
Dim IEObject As Object
Dim objCollection As Object
Dim objElement As Object
Set Database2 = CurrentDb
Set rstRecords = Database2.OpenRecordset("Table1")
If rstRecords.EOF Then
FindRecordCount = 0
Else
rstRecords.MoveLast
FindRecordCount = rstRecords.RecordCount
End If
rstRecords.MoveFirst
For i = 0 To FindRecordCount
StateName = rstRecords.Fields(1).value
Set CityRecords = Database2.OpenRecordset("tb" & StateName)
If CityRecords.EOF Then
FindRecordCountB = 0
Else
CityRecords.MoveLast
FindRecordCountB = CityRecords.RecordCount
End If
CityRecords.MoveFirst
Set IEObject = CreateObject("InternetExplorer.application")
IEObject.Visible = 1
IEObject.navigate ("[URL]http://www.unitedstateszipcodes.org/[/URL]")
Do While IEObject.ReadyState <> 4
Sleep 2000
Loop
For j = 0 To FindRecordCountB
CityName = CityRecords.Fields(1).value
StateAb = CityRecords.Fields(2).value
IEObject.Document.GetElementByID("q").value = CityName & " " & StateAb
Set objCollection = IEObject.Document.getElementsByTagName("button")
For Each Element In objCollection
If Element.Type = "submit" Then
Element.Click
Exit For
End If
Next
Sleep 7000
ZipCode = IEObject.Document.GetElementByID("zip").value
Set objElement = "INSERT INTO tb" & StateName & " " _
& "([Zip Code])" & vbCrLf & "VALUES " _
& "('" & ZipCode & "')"
Database2.Execute objElement, dbFailOnError
Database.Close
CityRecords.MoveNext
Next
IEObject.Quit
rstRecords.MoveNext
Next
Set rstRecords = Nothing
Set CityRecords = Nothing
End Sub