run-time error 424 object required

[niftysam]

Dim orsCompany, oConn
Set orsCompany = CreateObject("ADODB.Recordset")
Set oConn = CurrentProject.Connection ' within current project therefore no need to open connection
Dim branch As String, zipcode As Long, tempdistance As Double
orsCompany.Open "select * from [ZipCodesUSUnique];", oConn, 3, 1 'selects all invalid zipcode records
orsCompany.MoveFirst
Do While Not orsCompany.EOF
    zipcode = orsCompany("ZipCode")
    branch = orsCompany("AssignedBranch")
    tempdistance = Update_Distance(zipcode, branch)
MsgBox ("sam = UPDATE ZipCodesUSUnique SET ZipCodesUSUnique.[Distance] = " & tempdistance & " WHERE ZipCodesUSUnique.[ZipCode] = " & zipcode)
    orsUpdate.Execute "UPDATE ZipCodesUSUnique SET Distance = [" & tempdistance & "] WHERE ZipCode = [" & zipcode & "]", oConn, 3, 1
    orsCompany.MoveNext
Loop
orsCompany.Close

Does anyone see anything wrong with this?

Distance is a double and zipcode is a long integer.

Thanks!

Sam

 

[chappy]

Correct me if I'm wrong. From the code you wrote, it appears that you want to update each distance field in the ZipCodesUSUnique table with the result of a function that accepts the zipcode and branch field data as arguments. If this is the case then you could write one UPDATE query to do the same thing:

UPDATE ZipCodesUSUnique SET Distance = Update_Distance(zipcode,AssignedBranch)

The query will update each record based upon that record's zipcode and assignedbranch fields, so you don't need a WHERE clause.

Update_Distance has to be a publicly declared function in order for the query to use it. Hope this helps.

-Chappy