Type mismatch error (1 Viewer)

tucker61

Registered User.
Local time
Today, 09:15
Joined
Jan 13, 2008
Messages
321
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.


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
 

tucker61

Registered User.
Local time
Today, 09:15
Joined
Jan 13, 2008
Messages
321
Code:
Set Index = CurrentDb.OpenRecordset("SELECT tblPoDetail.Cost, tblPoDetail.Requisition_Number " & _
                                        "FROM tblPoDetail WHERE tblPoDetail.Requisition_Number = " & Forms!frmmain.tbRequisition_Number & ";", dbOpenSnapshot, dbReadOnly)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:15
Joined
Oct 29, 2018
Messages
21,358
Hi. Try using this.
Code:
Dim Index As DAO.Recordset
 

strive4peace

AWF VIP
Local time
Today, 11:15
Joined
Apr 3, 2020
Messages
1,003
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
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 09:15
Joined
Mar 14, 2017
Messages
8,738
Is Requisition_Number a Number field?
 

Users who are viewing this thread

Top Bottom