Error 3021 No Current Record upon failed data validation when trying to save (1 Viewer)

[Kryst51]

Hi Folks,

I haven't been on very much recently due to being moved to a new position at work, I hope ya'll are well.

To the point.

I have a form where in the before update event I have the following code (ByPass is declared public to the form at the top of the vba window as an Integer):

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim msgResponse As VbMsgBoxResult

10       On Error GoTo Form_BeforeUpdate_Error

20    If Me.NewRecord = False Then
30      Resume Next
40    Else
50      If Len(DLookup("InvoiceNumber", _
    "tblInvoices", "InvoiceNumber =" & Chr(34) & _
    Me.InvoiceNumber & Chr(34))) > 0 Then
60      msgResponse = MsgBox( _
    "This Invoice has already been entered." & vbCrLf _
    & _
    "Click 'OK' to go to the invoice (Check if you are duplicating)" _
    & vbCrLf & _
    "Click 'Cancel' to return to this record to change the invoice number if you mistyped it.", _
    vbOKCancel)
70          Select Case msgResponse
            Case 1
            
80    ByPass = 2
90    Cancel = True
110         Case 2
120   ByPass = 1
130   Cancel = True
140         End Select
150     Else
160         Cancel = True
170     End If
180   End If
 
Form_BeforeUpdate_Exit:
190   Exit Sub
Form_BeforeUpdate_Error:
200       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate of VBA Document Form_frmEnterInvoice on line " & Erl
210       GoTo Form_BeforeUpdate_Exit
End Sub

The form updates when a save button is clicked. My goal is that it will check to see if this invoice has already been entered then give the user two options, one - go to that invoice to see if it's the one they are entering, two- cancel the save, to check and make sure they didn't mistype their invoice number.

Here is the code behind my save button:

Private Sub btnSaveInvoice_Click()
10    On Error GoTo btnSaveInvoice_Click_Error
    Dim ctl As Control
    Dim cnt As Integer
    Dim IntgotoRecord As Integer
20        If Me.Dirty = False Then GoTo btnSaveInvoice_Click_Exit
30    cnt = 0
40    For Each ctl In Me.Controls
50      If ctl.Tag = "Highlight" Then
60          If Not Len(Nz(ctl.Value, "")) > 0 Then
70
80              ctl.BackColor = fRed()
90              ctl.ForeColor = fWhite()
100             cnt = cnt + 1
110         End If
120     End If
130   Next ctl
140   If cnt > 0 Then
150     MsgBox _
                "The Highlighted fields must be filled in"
160   Else
[COLOR=red][B]170     If Me.Dirty = True Then DoCmd.RunCommand acCmdSaveRecord[/B][/COLOR]
[COLOR=red][B]180     If ByPass = 1 Then
190         GoTo btnSaveInvoice_Click_Exit
200     ElseIf ByPass = 2 Then
210         IntgotoRecord = _
            DLookup("pkInvoiceID", "tblInvoices", _
                    "InvoiceNumber =" & Chr(34) & Me.InvoiceNumber & _
                    Chr(34))
220         Me.Undo
230         DoCmd.gotoRecord , , , _
                             IntgotoRecord
[/B][/COLOR]240         Call ResetEditControls
250     End If
260     ByPass = 0
270   End If
btnSaveInvoice_Click_Exit:
280   Exit Sub
btnSaveInvoice_Click_Error:
290   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSaveInvoice_Click of VBA Document Form_frmEnterInvoice on line " & Erl

300   GoTo btnSaveInvoice_Click_Exit
End Sub

The red portion is the portion that I am trying to get to work. I get the "No Current record error just after the vba has gone through the before update event and is moved back to the save button code. The error is on line 170.

I have done some reading and the only solution I found is to use the error handler to ignore the error, if I do that and move to the next line of code I get a different error on line 230 that is number 2105 "You can't go to the specified record".

Any help with either error would be appreciated.

 

[Kryst51]

So first I decided to use my error handling to ignore the first error (3021) and just deal with the second error(2105).

I could not for the life of me figure out why this error (Error 2105) was being caused, so I found a different approach and used it instead.

I found this code snippet:

Me.RecordsetClone.Findfirst "[ProductID] = " & Me![ComboNN]
   Me.Bookmark = Me.RecordSetClone.Bookmark

and modified it to my use.

I had tried using a wizard to create something that did this and then look at the code, but access 2010 creates an embedded macro. I think I remember that access 2003 used similar code to what ended up working for me.

Also, while moving stuff around I somehow fixed the "No Record Error" (3021) too.

For posterity's sake here is my working code I have moved everything from the before update event into the save button click event (red, bolded portion is the relevant portion):

Private Sub btnSaveInvoice_Click()
10    On Error GoTo btnSaveInvoice_Click_Error
    Dim ctl As Control
    Dim cnt As Integer
    Dim msgResponse As VbMsgBoxResult
    Dim intgotoRecord As Integer
20    If Me.Dirty = False Then GoTo _
       btnSaveInvoice_Click_Exit
30    cnt = 0
40    For Each ctl In Me.Controls
50      If ctl.Tag = "Highlight" Then
60          If Not Len(Nz(ctl.Value, "")) > 0 Then
70
80              ctl.BackColor = fRed()
90              ctl.ForeColor = fWhite()
100             cnt = cnt + 1
110         End If
120     End If
130   Next ctl
140   If cnt > 0 Then
150     MsgBox _
                "The Highlighted fields must be filled in"
160   Else
170     If Len(DLookup("InvoiceNumber", _
                       "tblInvoices", "InvoiceNumber =" & Chr(34) & _
                                      Me.InvoiceNumber & Chr(34))) > 0 Then
180         msgResponse = MsgBox( _
                          "This Invoice has already been entered." & vbCrLf _
                          & _
                          "Click 'OK' to go to the invoice (Check if you are duplicating)" _
                          & vbCrLf & _
                          "Click 'Cancel' to return to this record to change the invoice number if you mistyped it.", _
                          vbOKCancel)
190         Select Case msgResponse
 [COLOR=red][B]           Case 1[/B][/COLOR]
[COLOR=red][B]200             intgotoRecord = DLookup("pkInvoiceID", _
                                        "tblInvoices", "InvoiceNumber =" & Chr(34) & _
                                                       Me.InvoiceNumber & Chr(34))[/B][/COLOR]
[COLOR=red][B]210             DoCmd.RunCommand acCmdUndo[/B][/COLOR]
[COLOR=red][B]220      Me.RecordsetClone.FindFirst "[pkInvoiceID] = " & intgotoRecord
230      Me.Bookmark = Me.RecordsetClone.Bookmark[/B][/COLOR]

240         Case 2
250         Me.InvoiceNumber.SetFocus
260         End Select
270     Else
280         If Me.Dirty = True Then DoCmd.RunCommand _
               acCmdSaveRecord
290            DoCmd.gotoRecord , , acNewRec
300     End If
310   End If
btnSaveInvoice_Click_Exit:
320     Exit Sub
btnSaveInvoice_Click_Error:
330     MsgBox "Error " & Err.Number & " (" & _
               Err.Description & _
               ") in procedure btnSaveInvoice_Click of VBA Document Form_frmEnterInvoice on line " _
               & Erl

340     GoTo btnSaveInvoice_Click_Exit
    End Sub