Trapping error '3021'

[Yepp]

I have a form that fills in certain fields on the form automatically once a
number is typed in. The information is pulled from a query. The problem I
am having is trying to trap a certain error: if the number that is typed in
isn't in the tables from which the query pulls, I get the run-time error
'3021' (No current record). I tried using a generic sub to trap the error,
but I am having no success with it. The code is below. Is there something I
am not putting in the code, or am I not placing this code in the right place?

Sub Trapping()
On Error GoTo Err_Trapping
MsgBox "Type in this information"
Exit_Trapping:
Exit Sub
Err_Trapping:
MsgBox Err.Number & Err.Description
Resume Exit_Trapping
End Sub

 

[J_Goddard via AccessMonster.com]

Hi -

The error trapping and associated code must be in the same procedure as the
code which causes the error. The On Error statement must be before any line
which might cause the run-time error, preferably near the top of the
procedure.

The error handling code is (almost) always at the bottom of the procedure,
just as you have it.

John

 

[Yepp]

Thanks for the help; however, now that I have moved the code near the
beginning of my procedure, the error message comes up regardless of the
number that is typed in. How can I prevent that?

 

[mcescher]

Thanks for the help; however, now that I have moved the code near the
beginning of my procedure, the error message comes up regardless of the
number that is typed in.  How can I prevent that?








- Show quoted text -

Is it WITHIN your procedure?

For example:

Private Sub btnYourButton_OnClick()
on error goto errhndlr
'do some stuff
'do some more stuff - error occurs here
'do even more stuff
errexit:
exit sub
errhndlr:
msgbox err.number & "-" & err.message
return errexit
end sub


The error has to be handled within the procedure that generated the
error.

Hope this helps,
Chris M.

 

[J_Goddard via AccessMonster.com]

If your error handling code is near the beginning of the procedure, the
chances are that it is being executed whether an error occurs or not. Of
course, it could also be that there is something else wrong, and that the
errors are genuine, particularly if the error number is non-zero.

Can you post your code?

John

Thanks for the help; however, now that I have moved the code near the
beginning of my procedure, the error message comes up regardless of the
number that is typed in. How can I prevent that?

Hi -

[quoted text clipped - 25 lines]

 

[Yepp]

Here is my code:

Private Sub txtProvider_Number_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].COUNTY, [prov1].NAME,
[prov1].IPA_ID, [prov1].FIRST_NAME, [prov1].ADDRESS_LINE_1,
[prov1].ADDRESS_LINE_2, [prov1].CITY, [prov1].STATE, [prov1].ZIP_CODE,
[prov1].PHONE_NUMBER FROM [prov1] WHERE " & _
"[prov1].SEQ_PROV_ID=" & Me.Provider_Number
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
rs.MoveFirst
If rs.RecordCount > 0 Then
Me.txtProvider_Last_Name = rs.Fields![LAST_NAME]
Me.txtProvider_First_Name = rs.Fields![FIRST_NAME]
Me.txtProvider_Address = rs.Fields![ADDRESS_LINE_1]
Me.txtProvider_Address2 = rs.Fields![ADDRESS_LINE_2]
Me.txtProvider_City = rs.Fields![CITY]
Me.txtProvider_State = rs.Fields![STATE]
Me.txtProvider_Zip = rs.Fields![ZIP_CODE]
Me.txtProvider_Phone = rs.Fields![PHONE_NUMBER]
Me.txtCounty = rs.Fields![COUNTY]
Me.txtIPA_Name = rs.Fields![Name]
Me.txtIPA_Number = rs.Fields![IPA_ID]
End If
rs.Close
Set rs = Nothing
End Sub

If your error handling code is near the beginning of the procedure, the
chances are that it is being executed whether an error occurs or not. Of
course, it could also be that there is something else wrong, and that the
errors are genuine, particularly if the error number is non-zero.

Can you post your code?

John

Thanks for the help; however, now that I have moved the code near the
beginning of my procedure, the error message comes up regardless of the
number that is typed in. How can I prevent that?

Hi -

[quoted text clipped - 25 lines]

Resume Exit_Trapping
End Sub

 

[mcescher]

Here is my code:

Private Sub txtProvider_Number_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].COUNTY, [prov1].NAME,
[prov1].IPA_ID, [prov1].FIRST_NAME, [prov1].ADDRESS_LINE_1,
[prov1].ADDRESS_LINE_2, [prov1].CITY, [prov1].STATE, [prov1].ZIP_CODE,
[prov1].PHONE_NUMBER FROM [prov1] WHERE " & _
         "[prov1].SEQ_PROV_ID=" & Me.Provider_Number
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
rs.MoveFirst
If rs.RecordCount > 0 Then
    Me.txtProvider_Last_Name = rs.Fields![LAST_NAME]
    Me.txtProvider_First_Name = rs.Fields![FIRST_NAME]
    Me.txtProvider_Address = rs.Fields![ADDRESS_LINE_1]
    Me.txtProvider_Address2 = rs.Fields![ADDRESS_LINE_2]
    Me.txtProvider_City = rs.Fields![CITY]
    Me.txtProvider_State = rs.Fields![STATE]
    Me.txtProvider_Zip = rs.Fields![ZIP_CODE]
    Me.txtProvider_Phone = rs.Fields![PHONE_NUMBER]
    Me.txtCounty = rs.Fields![COUNTY]
    Me.txtIPA_Name = rs.Fields![Name]
    Me.txtIPA_Number = rs.Fields![IPA_ID]
End If
rs.Close
Set rs = Nothing
End Sub

If your error handling code is near the beginning of the procedure, the
chances are that it is being executed whether an error occurs or not.  Of
course, it could also be that there is something else wrong, and that the
errors are genuine, particularly if the error number is non-zero.

Can you post your code?

John

Thanks for the help; however, now that I have moved the code near the
beginning of my procedure, the error message comes up regardless of the
number that is typed in.  How can I prevent that?
Hi -
[quoted text clipped - 25 lines]
    Resume Exit_Trapping
End Sub

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -

Try something like this

Private Sub txtProvider_Number_AfterUpdate()
On Error GoTo txtProvider_Number_Error
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].COUNTY, [prov1].NAME,
[prov1].IPA_ID, [prov1].FIRST_NAME, [prov1].ADDRESS_LINE_1,
[prov1].ADDRESS_LINE_2, [prov1].CITY, [prov1].STATE,
[prov1].ZIP_CODE,
[prov1].PHONE_NUMBER FROM [prov1] WHERE " & _
"[prov1].SEQ_PROV_ID=" & Me.Provider_Number
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
.MoveFirst
If .RecordCount > 0 Then
txtProvider_Last_Name = ![LAST_NAME]
txtProvider_First_Name = ![FIRST_NAME]
txtProvider_Address = ![ADDRESS_LINE_1]
txtProvider_Address2 = ![ADDRESS_LINE_2]
txtProvider_City = ![CITY]
txtProvider_State = ![STATE]
txtProvider_Zip = ![ZIP_CODE]
txtProvider_Phone = ![PHONE_NUMBER]
txtCounty = ![COUNTY]
txtIPA_Name = ![Name]
txtIPA_Number = ![IPA_ID]
End If
End With
txtProvider_Number_End:
rs.Close
Set rs = Nothing
Exit Sub
txtProvider_Number_Error:
If Err.Number = 3021 Then 'No current record
MsgBox Err.Number & Err.Description
Resume txtProvider_Number_End
Else 'Some other error
MsgBox Err.Number & Err.Description
Resume Next
End Sub

Hope this helps,
Chris M.

 

[Yepp]

Chris,

Yes, that seems to work. Thank you and everyone else who responded for your
help.

Here is my code:

Private Sub txtProvider_Number_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].COUNTY, [prov1].NAME,
[prov1].IPA_ID, [prov1].FIRST_NAME, [prov1].ADDRESS_LINE_1,
[prov1].ADDRESS_LINE_2, [prov1].CITY, [prov1].STATE, [prov1].ZIP_CODE,
[prov1].PHONE_NUMBER FROM [prov1] WHERE " & _
"[prov1].SEQ_PROV_ID=" & Me.Provider_Number
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
rs.MoveFirst
If rs.RecordCount > 0 Then
Me.txtProvider_Last_Name = rs.Fields![LAST_NAME]
Me.txtProvider_First_Name = rs.Fields![FIRST_NAME]
Me.txtProvider_Address = rs.Fields![ADDRESS_LINE_1]
Me.txtProvider_Address2 = rs.Fields![ADDRESS_LINE_2]
Me.txtProvider_City = rs.Fields![CITY]
Me.txtProvider_State = rs.Fields![STATE]
Me.txtProvider_Zip = rs.Fields![ZIP_CODE]
Me.txtProvider_Phone = rs.Fields![PHONE_NUMBER]
Me.txtCounty = rs.Fields![COUNTY]
Me.txtIPA_Name = rs.Fields![Name]
Me.txtIPA_Number = rs.Fields![IPA_ID]
End If
rs.Close
Set rs = Nothing
End Sub

If your error handling code is near the beginning of the procedure, the
chances are that it is being executed whether an error occurs or not. Of
course, it could also be that there is something else wrong, and that the
errors are genuine, particularly if the error number is non-zero.

Can you post your code?

Yepp wrote:
Thanks for the help; however, now that I have moved the code near the
beginning of my procedure, the error message comes up regardless of the
number that is typed in. How can I prevent that?

Hi -

[quoted text clipped - 25 lines]
Resume Exit_Trapping
End Sub

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -

Try something like this

Private Sub txtProvider_Number_AfterUpdate()
On Error GoTo txtProvider_Number_Error
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].COUNTY, [prov1].NAME,
[prov1].IPA_ID, [prov1].FIRST_NAME, [prov1].ADDRESS_LINE_1,
[prov1].ADDRESS_LINE_2, [prov1].CITY, [prov1].STATE,
[prov1].ZIP_CODE,
[prov1].PHONE_NUMBER FROM [prov1] WHERE " & _
"[prov1].SEQ_PROV_ID=" & Me.Provider_Number
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
.MoveFirst
If .RecordCount > 0 Then
txtProvider_Last_Name = ![LAST_NAME]
txtProvider_First_Name = ![FIRST_NAME]
txtProvider_Address = ![ADDRESS_LINE_1]
txtProvider_Address2 = ![ADDRESS_LINE_2]
txtProvider_City = ![CITY]
txtProvider_State = ![STATE]
txtProvider_Zip = ![ZIP_CODE]
txtProvider_Phone = ![PHONE_NUMBER]
txtCounty = ![COUNTY]
txtIPA_Name = ![Name]
txtIPA_Number = ![IPA_ID]
End If
End With
txtProvider_Number_End:
rs.Close
Set rs = Nothing
Exit Sub
txtProvider_Number_Error:
If Err.Number = 3021 Then 'No current record
MsgBox Err.Number & Err.Description
Resume txtProvider_Number_End
Else 'Some other error
MsgBox Err.Number & Err.Description
Resume Next
End Sub

Hope this helps,
Chris M.