Type mismatch error

[tucker61]

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.

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

 

[tucker61]

Set Index = CurrentDb.OpenRecordset("SELECT tblPoDetail.Cost, tblPoDetail.Requisition_Number " & _
                                        "FROM tblPoDetail WHERE tblPoDetail.Requisition_Number = " & Forms!frmmain.tbRequisition_Number & ";", dbOpenSnapshot, dbReadOnly)

 

[theDBguy]

Hi. Try using this.

Dim Index As DAO.Recordset

 

[strive4peace]

a few more things to try:

• choose a different name. Index is a reserved word. Maybe rsIndex?
• create a string variable for the SQL statement and assign it in a different statement that is used to open it so you can see if the error is the SQL statement itself
• even though you won't change the recordset in code, instead of dbOpenSnapshot, dbOpenDynaset is faster to process

 

[Isaac]

Is Requisition_Number a Number field?

 

[cheekybuddha]

Dim Index As DAO.Recordset

I suggest re naming your object variable.

Index is a reserved word in Access and will surely cause an error.

 

[strive4peace]

@tucker61 , agree with @cheekybuddha -- Index needs another name. Older versions were more forgiving

 

[onur_can]

I suggest re naming your object variable.

Index is a reserved word in Access and will surely cause an error.

You're perfect! cheekybuddha

 

[cheekybuddha]

@tucker61 , agree with @cheekybuddha -- Index needs another name. Older versions were more forgiving

I didn't see you had already suggested that, Crystal

 

[tucker61]

thanks all, sorted with the above.

 

[theDBguy]

thanks all, sorted with the above.

Congratulations! Good luck with your project.