Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33

    Unanswered: error code 3021 confusion

    I have a text box on a form that I fill with a value from an sql select query. The query runs in the on current event, so each time I scroll to a new record it recalculates this value. Recently however I've been receiving error code 3021 which I believe to be the EOF or no record error. I've commented out the error case in the code and now its back to working again.... Any idea what could have caused this. Here's my code:

    Private Sub Form_Current()

    On Error GoTo Err_Form_Current

    Me!txtTotalPayments = "0"
    Me!txtTotalBilled = "0"

    Dim txtTotal As Currency
    Dim strSQL As String
    Dim rs As DAO.Recordset

    Dim txtTotal2 As Currency
    Dim strSQL2 As String
    Dim rs2 As DAO.Recordset

    strSQL = "SELECT Payments.JobID, Sum(Payments.Amount) AS TotalPayments " & _
    "FROM Payments GROUP BY Payments.JobID " & _
    "HAVING ((([Payments].[JobID])=" & Me!JobID & "));"

    strSQL2 = "SELECT Billing.JobID, Sum(Billing.Amount) AS TotalBilled " & _
    "FROM Billing GROUP BY Billing.JobID " & _
    "HAVING ((([Billing].[JobID])=" & Me!JobID & "));"

    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    txtTotal = rs.Fields(1)
    Me!txtTotalPayments = txtTotal
    rs.Clone
    Set rs = Nothing

    Set rs2 = CurrentDb.OpenRecordset(strSQL2, dbOpenDynaset)
    txtTotal2 = rs2.Fields(1)
    Me!txtTotalBilled = txtTotal2
    rs2.Clone
    Set rs2 = Nothing

    Exit_Form_Current:
    Exit Sub

    Err_Form_Current:
    Select Case Err
    Case 3021
    'txtTotal = 0
    'Me!txtTotalPayments = txtTotal
    'txtTotal2 = 0
    'Me!txtTotalBilled = txtTotal2
    Case 3075
    'txtTotal = 0
    'Me!txtTotalPayments = txtTotal
    'txtTotal2 = 0
    'Me!txtTotalBilled = txtTotal2
    Case Else
    MsgBox "Error " & Err & ": " & Error, vbOKOnly, "Stone Error"
    End Select
    Resume Exit_Form_Current

    End Sub


    You'll see that I added two lines at the beginning to reset these text boxes to '0' and I commented out the error cases. This appears to fix the problem but I'm not entirely sure what caused this error in the first place. Any help would be appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you remove your error handling what is the message that access tells you?
    Or instead of removing it why not
    Code:
    Err_Form_Current:
    Select Case Err
    Case 3021
    MsgBox "Error:" & Chr(9) & Err.Number & Chr(13) & "Desc:" & Chr(9) & Err.Description
    'txtTotal = 0
    'Me!txtTotalPayments = txtTotal
    'txtTotal2 = 0
    'Me!txtTotalBilled = txtTotal2
    Case 3075
    'txtTotal = 0
    'Me!txtTotalPayments = txtTotal
    'txtTotal2 = 0
    'Me!txtTotalBilled = txtTotal2
    Case Else
    MsgBox "Error " & Err & ": " & Error, vbOKOnly, "Stone Error"
    End Select
    Resume Exit_Form_Current
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33
    Without the error handling and with the added message box I get a message similar to the one I got before. Error 3021 No record found. I have a sinking feeling I might know why this is happening all of a sudden... I checked the relationships between some of my tables and they appear to have changed (maybe from a compact and repair?), I corrected this but there must be something I'm missing because the original code still doesn't not work properly. It will actually place the value in the text box, then the error will occur and reset the value to '0'. Oh well... It looks like I'll have to keep fooling with it. I appreciate the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •