Link to home
Start Free TrialLog in
Avatar of Marilync1266
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_Quote.Trade_In_Amount
intDiscounts = Forms!frm_Opportunity_Quote.Discounts_Amt
intTraining = Forms!frm_Opportunity_Quote.Training_Amt
intOther = Forms!frm_Opportunity_Quote.Other_Amt
strQuoteNo = Forms!frm_Opportunity_Quote.txtQuoteNo
intOldItemTotal = rstSum!Item_Total
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

'In the afterUpdate event'
of what? the form? a control?

And which line is highlighted when you get the error?
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

ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Marilync1266
Marilync1266

ASKER

Item No is a string field too.
This is an form event
The row highlighted is:  intOldItemTotal = rstSum!Item_Total
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
Set rstSum = DB.OpenRecordset(strSQL, dbOpenDynaset)
if rs.eof and rs.bof then
msgbox "No records"
exit sub
end if

intTradeIn = Forms!frm_Opportunity_Quote.Trade_In_Amount
intDiscounts = Forms!frm_Opportunity_Quote.Discounts_Amt
capricorn1:
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