Solved Error 3078 when deleting a record (1 Viewer)

[Marshall Brooks]

Initially posted at: https://www.access-programmers.co.u...update-of-a-field.329588/page-10#post-1909410

I'm getting this error when deleting a record using a delete button control:

Stepping through the code under the delete button, the record is deleted, the code steps through the Form_Current() event, updates the custom navigation buttons, and then returns to the delete button code and displays the error message. The record seems to have been deleted and everything is fine if I click okay.

The Error Number is 3078.

I don't know where it is calling a query from.

I think I could get rid of it by just adding:
If Err.No = 3078 Then
Err.Clear
End If

In the Error trapping routine, but I'd prefer to solve the error rather than ignoring it.

 

[Marshall Brooks]

More info. I only get the error on one particular form. I have 7 forms in the database with similar code that do not produce the error.

I tried compacting the front end and that didn't make a difference.

I was initially getting the error when I deleted a record I had just created, but I verified I get the error when I delete any record from this form. It is a bound form.

 

[gemma-the-husky]

Ignore this. I think you are getting the right help in the other thread.

 

[Marshall Brooks]

The other thread was talking about an error when saving records (that I worked around with Me.Dirty = False) and some good info on validating input and which event to use, but did not address this issue and I was told to start a new thread.

I'm okay with ignoring the error, if that is what you meant, but if I release the database and it shows an error when an admin deletes a record, they won't want to just ignore it.

I'm okay with error-trapping 3078 and moving on, but that seems amateurish. Similar to changing to "On Error Resume Next" and saying "There, I fixed it."

In the E-mail notification to me, it said:

What code is running, or are you just pressing the delete button?

There might be a dara corruption producing this error.

There could be data corruption producing the error. I was troubleshooting code that copied records into the table bound to the form that was giving me problems, so I was creating several records and then deleting them. Maybe coincidence that I am getting the error in the same form/table that I was copying the records to, but I don't put much stock in coincidences.

I did a C&R on the (test) backend and still see the error.

Actually - I swapped the (test) backend with a (test) backend from a week ago and I don't see the error with that backend, so I'm assuming something got messed up in the newer backend. I'll update it again and go from there, but that resolves this problem.

I was just pressing the delete button, which ran the delete code, which ran the Form_OnCurrent() code, which completed and went back to the delete button code and displayed the error message.

 

[Pat Hartman]

Corruption in the BE is more rare than in the FE but it happens. Use the same techniques. Create a new empty database and import all objects. If that doesn't resolve the issue and you need the new data, export each table to a csv file. Then just import the structure from the old db and not the data. Then import the .csv files. You need to import them in logical order if there are relationships involved so that the parent records get imported first and then the child records.

 

[Marshall Brooks]

@Pat Hartman - Thank you again for the assistance.

In this case, I don't really care. This was just dummy data that I was using to test Data Macros and code upgrades before we release the production database.

I just started from the original backup copy and ran you DDL Conversion program (took about a second) and ran JDraw's Load from Tables routine (took a bit longer - maybe 2-3 minutes b/c I had to add a reference and copy the modules from the non-working database) and I was good-to-go.

Had this been an actual emergency ... (i.e. data I cared about preserving), I have routines in the BE that @bastanu provided previously to export all tables and code to text and import into a blank database, so I would have run those.

Everyone has done a great job helping me out and keeping everything progressing!!!

 

[gemma-the-husky]

I actually meant that I thought your query was resolved in the other thread, not that you could ignore the error.

I thought in the other thread your code was somehow reaching the same block and that was the error.

However, if you are getting an error that just doesn't make sense, then I thought it might be caused by corruption, as that's the sort of error you get with a corruption. You certainly want to find the cause and solution, I think.

 

[The_Doc_Man]

In the past when I got a reference to "|" in a query it was because I had an SQL syntax error involving improper concatenation that led to a faulty field or table reference. I'm going to guess that you have a faulty object reference behind that delete button.

 

[Marshall Brooks]

@gemma-the-husky - Yes, typically, I would want to find the cause and solution. In this case reverting to a past version of the BE cleared the error, so I'm not super worrried about it - as I said, it was prototype/test data anyway.

@The_Doc_Man - You are probably onto something, SQL is not my strong point, but there isn't really SQL involved here. I'll obfuscate some of the table names, but let me post some code (Also - it seems to contradict that in that I renamed an old copy of the BE and the error went away, without changing any of the FE code):

Delete Button code (on form object):

Private Sub ADD_Click()
Dim CurrentRecord As Long
On Error GoTo Err_ADD_Click
strResult = Dialog.Box(Prompt:="Select desired action:" & "", Buttons:=(1024 + 32), _
    LabelButton1:="Cancel", LabelButton2:="Delete Record", LabelButton3:="Add Record", DefaultButton:="3")
If strResult = vbBt1 Then ' Cancel
    Exit Sub
ElseIf strResult = VbBt2 Then
    Me.AllowDeletions = True
    Forms![ProblemForm].[REFERENCE].SetFocus ' move focus off a subform
    CurrentRecord = Me.PrimaryKey
    ' There is an automatic confirmation prompt
    DoCmd.RunCommand acCmdDeleteRecord
    Me.AllowDeletions = False
    If Me.PrimaryKey = CurrentRecord Then
    '    Screen.MousePointer = 1
        strResult = Dialog.Box(Prompt:="An error occurred deleting the current record record. Please retry." & "", Buttons:=(0 + 16))
        Exit Sub
    End If
Else ' Add Record
    Me.AllowAdditions = True
    Forms![ProblemForm].[REFERENCE].SetFocus ' move focus off a subform
    CurrentRecord = Me.PrimaryKey
    'Pause (2.5)
    'DoCmd.GoToRecord , , acNewRec
    DoCmd.GoToRecord acDataForm, "ProblemForm", acNewRec
    'Pause (3)
    If Me.PrimaryKey = CurrentRecord Then
        strResult = Dialog.Box(Prompt:="An error occurred adding the new record. Please retry." & "", Buttons:=(0 + 16))
        Exit Sub
    End If
    ' Me.AllowAdditions = False is executed in the Form_AfterInsert() Event.
End If
Exit_ADD_Click:
    Exit Sub
Err_ADD_Click:
    Box Err.Number & " - " & Err.DESCRIPTION
    Resume Exit_ADD_Click
End Sub

Seems to run fine - I'm not sure it ever got to the Me.AllowDeletions = False line. acCmdDeleteRecord goes to:

Public Function GetRealUserName() As String
GetRealUserName = Nz(ELookup("[RealName]", "[tblUsers]", "[UserName] = '" & GetUserName & "'"), GetUserName)
End Function

In Module Users, and then to (in the same module):

Public Function GetUserName() As String
    Static WinUserName As String
    If Len(WinUserName) = 0 Then
         WinUserName = GetWindowsUserName()
    End If
   GetUserName = WinUserName
End Function

And then to (same module):

Private Function GetWindowsUserName() As String
' https://www.devhut.net/vba-recognize-user-get-username/
' https://www.access-programmers.co.uk/forums/threads/audit-update-of-a-field.329588/page-3 - LCase is not technically required, it should work the same way regardless.
GetWindowsUserName = LCase(CreateObject("WScript.Network").UserName)
End Function

Through ELookup, then to (back on the form):

Private Sub Form_Current()
Call Me.zz_frmNavButtons.Form.EnableDisableButtons
End Sub

to

Public Sub EnableDisableButtons()
' Check and see which Buttons should be enabled/disabled
' based on the current row of the recordset.

On Error Resume Next

'Me.zz_frmNavButtons.Width = Me.InsideWidth

If Me.Parent.FilterOn = True Then
    Me.cmdFilter.caption = "Filterered"
    Me.cmdFilter.BackColor = RGB(252, 194, 196)
    Me.cmdFilter.ControlTipText = "Click to remove filter from the records."
    Me.Repaint
Else
    Me.cmdFilter.caption = "Unfiltered"
    Me.cmdFilter.BackColor = RGB(225, 225, 225)
    Me.cmdFilter.ControlTipText = "Click to filter the records by using the last saved filter."
    Me.Repaint
End If
If Me.Parent.Filter = "" Then
    Me.cmdFilter.caption = "No Filter"
    Me.cmdFilter.BackColor = RGB(225, 225, 225)
    Me.cmdFilter.ControlTipText = ""
    Me.Repaint
End If


' Update our Nav info
'Me.txtPos.value = Me.Parent.CurrentRecord

' Me.Parent has no recordset (yet)
If Err Then Exit Sub

' If lblNumRecords is blank then this is the
' first time through and the recordset pointers
' may not be updated yet. We need to allow time
' for them to get updated.
'If Me.lblNumRecords.caption = "" Then
'    Me.Parent.RecordsetClone.MoveLast
'    DoEvents
'End If
'Me.lblNumRecords.caption = " of  " & Me.Parent.RecordsetClone.RecordCount
DoEvents
'Updated 18-Jan-2024 to add new record counter by MB.
If Me.TXTPos.caption = "" Then
    Me.Parent.RecordsetClone.MoveLast
    DoEvents
End If
Me.TXTPos.caption = Me.Parent.CurrentRecord & " of " & Me.Parent.RecordsetClone.RecordCount
'Me.lblNumRecords.caption = " of  " & Me.Parent.RecordsetClone.RecordCount


' Are we on a NEW record
If Me.Parent.NewRecord = True Then
    ' Update our nav info.
    'Me.txtPos.Value = "New Record"
'    Me.txtPos.value = Me.Parent.RecordsetClone.RecordCount + 1
'    Me.lblNumRecords.caption = " of  " & Me.Parent.RecordsetClone.RecordCount + 1
'    Me.txtHidden.SetFocus
    Me.TXTPos.caption = "New Record"
    Me.TXTPos.caption = Me.Parent.RecordsetClone.RecordCount + 1 & " of  " & Me.Parent.RecordsetClone.RecordCount + 1
    Me.cmdLast.Enabled = False
    Me.cmdNext.Enabled = False
    Me.cmdPrevious.Enabled = True
'    Me.cmdNew.Enabled = False
    Exit Sub
End If

'If we reach here, we know we are not in a new record
'so we can enable the <New> button
'Me.cmdNew.Enabled = True
Me.cmdPrevious.Enabled = True
Me.cmdNext.Enabled = True  '

'But we need to check if there are no records. If so,
'we disable all buttons except for the <New> button.

If Me.Parent.RecordsetClone.RecordCount = 0 Then
    Me.cmdFirst.Enabled = False
    Me.cmdNext.Enabled = False
    Me.cmdPrevious.Enabled = False
    Me.cmdLast.Enabled = False
Else

    'Synchronise the current pointer in the two recordsets
'    Me.Parent.RecordsetClone.Bookmark = Me.Parent.Bookmark
'
'
'    'If there are records, see if we are on the first record
'    'If so, we should disable the <First> and <Prev> buttons
'    With Me.Parent.RecordsetClone
'        .MovePrevious
'        If .BOF Then
''            Me.txtHidden.SetFocus
'
'            Me.cmdPrevious.Enabled = False 'Not (.BOF)
'            Me.cmdFirst.Enabled = False
'            ' Update our Nav info
'            'Me.txtPos.Value = Me.Parent.CurrentRecord
'            Exit Sub
'        Else ' Added by MB 2023-Aug-24
'            Me.cmdPrevious.Enabled = True
'            Me.cmdFirst.Enabled = True
'            Me.cmdNext.Enabled = True
'            Me.cmdLast.Enabled = True
'        End If
'    End With
'    ' Added by MB 2023-Aug-24 - See if we are on the last record, if so, disable the next and last buttons
'    With Me.Parent.RecordsetClone
'        .MoveNext
'        If .EOF Then
'            Me.cmdNext.Enabled = False 'Not (.EOF)
'            Me.cmdLast.Enabled = False
'            Exit Sub
'        Else ' Added by MB 2023-Aug-24
'            Me.cmdPrevious.Enabled = True
'            Me.cmdFirst.Enabled = True
'            Me.cmdNext.Enabled = True
'            Me.cmdLast.Enabled = True
'        End If
'    End With
    If Me.Parent.CurrentRecord = Me.Parent.Recordset.RecordCount Then
          Me.cmdPrevious.Enabled = True
          Me.cmdFirst.Enabled = True
          Me.cmdNext.Enabled = False
          Me.cmdLast.Enabled = False
    ElseIf Me.Parent.CurrentRecord = 1 Then
           Me.cmdPrevious.Enabled = False
          Me.cmdFirst.Enabled = False
          Me.cmdNext.Enabled = True
          Me.cmdLast.Enabled = True
    Else
          Me.cmdPrevious.Enabled = True
          Me.cmdFirst.Enabled = True
          Me.cmdNext.Enabled = True
          Me.cmdLast.Enabled = True
    End If
 
End If
End Sub

And then would return to the Add Button code and give me the error message.

RecordSource for the form, if it matters is: SELECT * FROM [ProblemTable] ORDER BY SortKey, TITLE

Lots to go through, but hopefully you will see something that I'm missing!!!

 

[jdraw]

The thread is marked SOLVED?? But seems problem persists.
Have you tried stepping through the code?
You have Option Explicit turned on?
Do you have MZ Tools to assign line numbers and use that with your error handler?

 

[Marshall Brooks]

Solved in terms of I used an older backup of the test database and the problem went away. Unsolved in that I never determined what the error was with the BE file that was giving me the error.

Stepping through code - yes, see above, although it didn't seem to be on a specific line. If got through the navigation panel settings and returned to the ADD button click code and gave the error message. It would have been just before the "Allow Deletions = False" line, but I don't think it got to that line before displaying the error.

MZ Tools - no. Hadn't heard of MZ Tools, but I'm sure the company won't pay for a license for me.

 

[jdraw]

Mztools ~ here's a recent video on some features