Run-Time error 424 "Object Required" (1 Viewer)

[Ecal]

: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.

 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

 

[MarkK]

For best result with reporting run-time errors: Indicate the line on which the error occurs.

 

[Ecal]

Lines 79 80 and 81 are what it highlights something wrong

 

[Ecal]

79 80 81 are where the trouble is

 

[spikepl]

Ah one of the ones with black text, right?

 

[JHB]

Lines 79 80 and 81 are what it highlights something wrong

Sorry - I'm not counting lines, show the code for the lines!

 

[MarkK]

I'm not counting lines either. And an error will be on a single line.

 

[The_Doc_Man]

OK, this is obviously not going forward. Here is my take on the problem.

"Object required" clearly means that you have a case of syntax "object.property" or "object.method" in your code and the object is not properly instantiated.

To find which one is the culprit, run this code and let it take an error. When the box pops up, click DEBUG.

Now you are in a code window. Scroll that so that the error is near the top of the window. Now open the Local Window and visit each object. One of the objects will not be defined properly. That is the first part of the problem.

Now you know what is broken. The next part is to decide how it got broken. So END your code. Put a breakpoint on the Set command that instantiates the Database2 variable. Now with the local window open and the code window open, single-step the code, noting each time the offending object changes. At some point in the code, something will break that object and you will know which step did it. If you are still stuck from there, give us another shout-out.

 

[spikepl]

这是更有趣的答案写下来的中国人不完整的问题

 

[Ecal]

Sorry guys I can't figure out how to show the code so it shows a number on every line Does anyone know how? I really like to have all my code with numbers next to every line

 

[Ecal]

This is the line that the debugger stops on:

 Set objElement = "INSERT INTO tb" & StateName & " " _
            & "([Zip  Code])" & vbCrLf & "VALUES " _
            & "('" & ZipCode & "')"

 

[MarkK]

Remove the "Set" keyword.

objElement = _
   "INSERT INTO tb" & StateName & " " & _
      "([Zip  Code])" & _
   "VALUES " & _
      "('" & ZipCode & "')"

The SQL statement is a string, not an object.

 

[Ecal]

If I adjust the code I get a Run-time error '91': Object variable or With block variable not set

I don't have any with blocks and if I put set in front of it but get a different error tried Dim objElement As Object I have tried Dim objElement As Variant without set and still get an error

 

[MarkK]

You've declared . . .

Dim objElement As Object

. . . but you are trying to assign to it a string value. If it will hold a string, declare it as a string . . .

Dim sqlInsert As String

sqlInsert = _
   "INSERT INTO tb" & StateName & " " & _
      "([Zip  Code])" & _
   "VALUES " & _
      "('" & ZipCode & "')"

 

[jdraw]

Ecal,

At the website in your code, there is a download of zip codes available in csv or excel format. Have you considered downloading the file and importing to a table or, with some processing into several tables? I don't know how current it is.

 

[Ecal]

I see what your saying but I am also pulling info of another site with a population of 10000 or above. because what I am using it for there is no need to have a zip code for that area if it is below that