#error help

[reel knotty]

I am calling a DLL from a form in access. in this form I am selecting the zip code from the previous record using the following code:

Function PrevRecVal(shipmentlookup As Form, Trans As String, KeyValue, _
Zip As String)
Dim pZip As DAO.Recordset

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0

' Get the form recordset.
Set pZip = shipmentlookup.RecordsetClone

' Find the current record.
Select Case pZip.Fields(Trans).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
pZip.FindFirst "[" & Trans & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
pZip.FindFirst "[" & Trans & "] = #" & KeyValue & "#"
'Find using text data type key value?
Case DB_TEXT
pZip.FindFirst "[" & Trans & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
pZip.MovePrevious

' Return the result.
PrevRecVal = pZip(Zip)

Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function

Then using the pZip and Zip to call the DLL with the following Code:

Public Function fmiles(pZip As String, Zip As String) As Double
Dim x As Byte
Dim Miles As Double
Dim hours As Byte
Dim mins As Byte
Dim parms As String
Dim dpath As String
Dim errorfile As String

parms = "-xx -h14.0 -p"
dpath = "C:\program Files\Prophesy\Common\Tripsdb"
errorfile = "err.txt"

x = RouteStopPairByParm(pZip, Zip, parms, errorfile, dpath, Miles, hours, mins)


If x = 1 Then
fmiles = Miles

Else
fmiles = 0


End If

End Function

my problem is for the first record in the list there is no previous record to get the zip. So when I run the form it returns #error in the fmiles field. This is making it impossible to sum the fmiles in the form. I need to either set the pZip to the current zip if there is no previous record or set miles to 0 on error. How can I accomplish this??

Thanks in advance,
Nate

 

[WayneRyan]

Reel,

I was going to suggest .MoveNext & .MovePrevious, but there is
no valid "neighbor".

I don't know that there is any "proper" solution. How about for
the first one, (or if prior is null) then you pass the same
zip as both. The distance would be zero for that one ... OK?

Wayne

 

[reel knotty]

That would work fine but I'm having issues with the code for that I have tried several ideas but none of them have worked.

The first record would be the start of a new shipment so a zero fmile value is correct.

 

[WayneRyan]

reel,

Public Function fmiles(pZip As String, Zip As String) As Double 
Dim x As Byte 
Dim Miles As Double 
Dim hours As Byte 
Dim mins As Byte 
Dim parms As String 
Dim dpath As String 
Dim errorfile As String 

parms = "-xx -h14.0 -p" 
dpath = "C:\program Files\Prophesy\Common\Tripsdb" 
errorfile = "err.txt" 

' 
' New Code, If same zips, exit with 0 don't call other function.
'
If pZip = Zip Then
   fmiles = 0
   Exit function
End If

x = RouteStopPairByParm(pZip, Zip, parms, errorfile, dpath, Miles, hours, mins) 


If x = 1 Then 
   fmiles = Miles 
Else 
   fmiles = 0 
End If 

End Function

Wayne

 

[reel knotty]

Thanks Wayne,
But I still get the same result. In the first field pZip would actually be null since the pZip value comes from the previous record and there is no previous record for the first record to pull from. I have tried:

If pZip is null then
pZip = Zip

If pZip = 0 then
Pzip = Zip

and none of these work (I get a mismatch error on compile)

but the above is kinda what i'm looking for.

Any ideas?

 

[WayneRyan]

reel,

Try:

If Nz(PZip, 0) = 0 Then
   PZip = Zip
End If

Or

If IsNull(PZip) Then
   PZip = Zip
End If

Wayne

 

[reel knotty]

That didn't seem to do it either. I did (right or wrong) add a new field ppZip with the control source as Iff(pZip is null, zip, Pzip) and that seems to work. Now I need to get the miles to total at the bottom of my form.


Thanks for the help Wayne.