i have moved the back end of my database into Sharepoint, and currently got approx 90% of the funtionality.
One issue i have is code that has always workedi is below is now giving me a type mismatch error 13 at the Set index code highlighted in Red.
Any advise appreciated.
One issue i have is code that has always workedi is below is now giving me a type mismatch error 13 at the Set index code highlighted in Red.
Any advise appreciated.
Code:
Function UpdateClaimValue()
On Error GoTo handler
Dim Index As Recordset
Dim ClaimValue As Currency
Dim Remaining As Currency
Dim tbAmount As Currency
Dim Amount As Currency
If IsNull(Forms!frmmain!tbRequisition_Number) Then Exit Function
ClaimValue = 0
DoEvents
Set Index = CurrentDb.OpenRecordset("SELECT tblPoDetail.Cost, tblPoDetail.Requisition_Number " & _
"FROM tblPoDetail WHERE tblPoDetail.Requisition_Number = " & Forms!frmmain.tbRequisition_Number & ";", dbOpenSnapshot, dbReadOnly)
If Not Index.EOF Then
Index.MoveFirst
While Not Index.EOF
ClaimValue = ClaimValue + (Nz(Index("Cost"), 0))
Index.MoveNext
Wend
End If
Index.Close
Set Index = Nothing
WarningsOff
Amount = Nz(DLookup("Amount", "tblPONumbers", "Requisition_number=" & Forms!frmmain!tbRequisition_Number & " "), 0)
Forms!frmmain!TBSpent = ClaimValue
Remaining = Amount - ClaimValue
DoCmd.RunSQL "UPDATE Qrytblponumbers SET Remaining = " & Remaining & " " & _
"WHERE Qrytblponumbers.Requisition_number=" & Nz(Forms!frmmain!tbRequisition_Number, 0) & ";"
WarningsOn
DoEvents
Forms!frmmain!TBSpent.Requery
Forms!frmmain!tbremaining.Requery
Exit Function
handler:
MsgBox "Unexpected error - " & Err.Number & Err.Description & vbCrLf & vbCrLf & Error$, vbExclamation, "UpdateClaimValue"
Exit Function