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
Set rs = Nothing
Set rs2 = CurrentDb.OpenRecordset(strSQL2, dbOpenDynaset)
txtTotal2 = rs2.Fields(1)
Me!txtTotalBilled = txtTotal2
Set rs2 = Nothing
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.
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