Marilync1266
asked on
Access VBA Runtime Error 3021 No Current Record
HELP! I'm about to demonstrate my application to some people and all of the sudden its not working.
In the afterUpdate event, I'm selecting item_total from a table and everytime I run this event, I get an error
Runtime Error 3021 no current record. But I check my table and the data is there and the item no/quote no in the where statement match what is in my table. What could I be doing wrong?
Dim DB As DAO.Database
Dim rstSum As DAO.Recordset
Dim strSQL As String
Dim intTotal As Double
Dim strPriceDescription As String
Dim intOldItemTotal As Currency
Dim intNewItemTotal As Currency
Dim intGrandTotal As Currency
Dim intTradeIn As Double
Dim intDiscounts As Double
Dim intTraining As Double
Dim intOther As Double
Dim strQuoteNo As String
If Nz(Me.Item_No) = "" Then
GoTo Exit_Price_AfterUpdate
End If
intOldItemTotal = 0
intNewItemTotal = 0
intGrandTotal = 0
intTradeIn = 0
intDiscounts = 0
intTraining = 0
intOther = 0
Set DB = CurrentDb()
'Get Item Total Before Price Change
strSQL = "SELECT Item_Total FROM tbl_Quote_Equipment " & _
"WHERE QuoteNo = '" & strQuoteNo & "' and Item_No = '" & Me.Item_No & "';"
Set rstSum = DB.OpenRecordset(strSQL, dbOpenDynaset)
intTradeIn = Forms!frm_Opportunity_Quot e.Trade_In _Amount
intDiscounts = Forms!frm_Opportunity_Quot e.Discount s_Amt
intTraining = Forms!frm_Opportunity_Quot e.Training _Amt
intOther = Forms!frm_Opportunity_Quot e.Other_Am t
strQuoteNo = Forms!frm_Opportunity_Quot e.txtQuote No
intOldItemTotal = rstSum!Item_Total
In the afterUpdate event, I'm selecting item_total from a table and everytime I run this event, I get an error
Runtime Error 3021 no current record. But I check my table and the data is there and the item no/quote no in the where statement match what is in my table. What could I be doing wrong?
Dim DB As DAO.Database
Dim rstSum As DAO.Recordset
Dim strSQL As String
Dim intTotal As Double
Dim strPriceDescription As String
Dim intOldItemTotal As Currency
Dim intNewItemTotal As Currency
Dim intGrandTotal As Currency
Dim intTradeIn As Double
Dim intDiscounts As Double
Dim intTraining As Double
Dim intOther As Double
Dim strQuoteNo As String
If Nz(Me.Item_No) = "" Then
GoTo Exit_Price_AfterUpdate
End If
intOldItemTotal = 0
intNewItemTotal = 0
intGrandTotal = 0
intTradeIn = 0
intDiscounts = 0
intTraining = 0
intOther = 0
Set DB = CurrentDb()
'Get Item Total Before Price Change
strSQL = "SELECT Item_Total FROM tbl_Quote_Equipment " & _
"WHERE QuoteNo = '" & strQuoteNo & "' and Item_No = '" & Me.Item_No & "';"
Set rstSum = DB.OpenRecordset(strSQL, dbOpenDynaset)
intTradeIn = Forms!frm_Opportunity_Quot
intDiscounts = Forms!frm_Opportunity_Quot
intTraining = Forms!frm_Opportunity_Quot
intOther = Forms!frm_Opportunity_Quot
strQuoteNo = Forms!frm_Opportunity_Quot
intOldItemTotal = rstSum!Item_Total
If Item_No is Numeric then ... use this. I assume quote no is a string field.
"WHERE QuoteNo = '" & strQuoteNo & "' and Item_No = " & Me.Item_No & ";"
ET
"WHERE QuoteNo = '" & strQuoteNo & "' and Item_No = " & Me.Item_No & ";"
ET
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Item No is a string field too.
This is an form event
The row highlighted is: intOldItemTotal = rstSum!Item_Total
This is an form event
The row highlighted is: intOldItemTotal = rstSum!Item_Total
ASKER
There should be data in the recordset - I double checked the table and the criteria. That is what baffles me.
Marilync1266,
add the codes to test for EOF and test. it will not lie to you
check the data thoroughly
add the codes to test for EOF and test. it will not lie to you
check the data thoroughly
Set rstSum = DB.OpenRecordset(strSQL, dbOpenDynaset)
if rs.eof and rs.bof then
msgbox "No records"
exit sub
end if
intTradeIn = Forms!frm_Opportunity_Quot e.Trade_In _Amount
intDiscounts = Forms!frm_Opportunity_Quot e.Discount s_Amt
if rs.eof and rs.bof then
msgbox "No records"
exit sub
end if
intTradeIn = Forms!frm_Opportunity_Quot
intDiscounts = Forms!frm_Opportunity_Quot
ASKER
capricorn1:
You were so right - there was an error in my code. Thanks for your help!
You were so right - there was an error in my code. Thanks for your help!
Try this just to see what it returns.
intOldItemTotal = DLookup("[Item_Total]", "tbl_Quote_Equipment", "[QuoteNo]= '" & strQuoteNo & '" AND [Item_No] = '" & Me.Item_No & '";")
ET
intOldItemTotal = DLookup("[Item_Total]", "tbl_Quote_Equipment", "[QuoteNo]= '" & strQuoteNo & '" AND [Item_No] = '" & Me.Item_No & '";")
ET
of what? the form? a control?
And which line is highlighted when you get the error?