DoEvent() Returns 0 BUT Run-time Error 2585 This a

问题:

This code was running without a hitch, but now getting Error 2585.

I have looked at Gustav's answer and Gord Thompson's answer but unless I am missing something (quite possible!) the first does not work and the second seems inapplicable. I saw on another site a suggestion that there might be a duplicate record ID, but I check for that possibility.

I put a call to DoEvent() in response to this error but it returns zero. I also wait for 10 seconds to let other processes run. Still receive the error.

Private Sub SaveData_Click()
  Dim myForm As Form
  Dim myTextBox As TextBox
  Dim myDate As Date
  Dim myResponse As Integer

If IsNull(Forms!Ecoli_Data!DateCollected.Value) Then

  myReponse = myResponse = MsgBox("You have not entered all the required data. You may quit data entry by hitting 'Cancel'", vbOKOnly, "No Sample Date")
  Forms!Ecoli_Data.SetFocus
  Forms!Ecoli_Data!Collected_By.SetFocus
  GoTo endOfSub
End If

If Me.Dirty Then Me.Dirty = False

myDate = Me.DateCollected.Value

Dim yearAsString As String, monthAsString As String, dayAsString As String, clientInitial As String

Dim reportNumberText As String

reportNumberText = Me!SampleNumber.Value
Debug.Print "reportNumberText = " & reportNumberText

Debug.Print "CollectedBy Index: " & Me!Collected_By & " Employee Name: " & DLookup("CollectedBy", "Data_Lookup", Me.Collected_By)


Dim whereString As String
whereString = "SampleNumber=" & "'" & reportNumberText & "'"
Debug.Print whereString
On Error GoTo errorHandling
DoCmd.OpenReport "ECOLI_Laboratory_Report", acViewPreview, , whereString
DoCmd.PrintOut
DoCmd.Close acReport, "ECOLI_Laboratory_Report", acSaveNo

Dim eventsOpen As Integer
eventsOpen = DoEvents()
Debug.Print "Number of Open Events = " & DoEvents()

    Dim PauseTime, Start, Finish, TotalTime

        PauseTime = 10    ' Set duration.
        Start = Timer    ' Set start time.
        Do While Timer < Start + PauseTime
            DoEvents    ' Yield to other processes.
        Loop
        Finish = Timer    ' Set end time.
        TotalTime = Finish - Start  ' Calculate total time.
    myResponse = MsgBox("Processing Report Took " & TotalTime & " seconds.", vbOKOnly)


myResponse = MsgBox("Do you want to add more data?", vbYesNo, "What Next?")


If myResponse = vbYes Then

    DoCmd.Close acForm, "ECOLI_Data", acSaveYes 

Error Generated By Line Above and occurs whether response Y or N to MsgBox.

    DoCmd.OpenForm "ECOLI_Data", acNormal, , , acFormAdd
    DoCmd.GoToRecord , , acNewRec
Else
    DoCmd.Close acForm, "ECOLI_Data", acSaveYes

End If
Exit Sub

 errorHandling:
 If Err.Number = 2501 Then
   myResponse = MsgBox("Printing Job Cancelled", vbOkayOnly, "Report Not Printed")
ElseIf Err.Number = 0 Then
   'Do nothing
Else
   Debug.Print "Error Number: " & Err.Number & ": " & Err.Description

   myResponse = MsgBox("An Error occurred: " & Err.Description, vbOKOnly, "Error #" & Err.Number)
End If


If Application.CurrentProject.AllForms("ECOLI_Data").IsLoaded Then DoCmd.Close acForm, "ECOLI_Data", acSaveNo

If Application.CurrentProject.AllReports("ECOLI_Laboratory_Report").IsLoaded     Then DoCmd.Close acReport, "ECOLI_Laboratory_Report", acSaveNo

endOfSub:
End Sub

Any idea on what am I missing here? Thanks.

回答1:

I can't replicate the problem, but the following might help:

I assume you run into troubles because you're closing and opening the form in the same operation. To avoid doing this, you can open up a second copy of the form, and close the form once the second copy is open. This avoids that issue.

To open a second copy of the form:

Public Myself As Form

Public Sub CopyMe()
    Dim myCopy As New Form_CopyForm
    myCopy.Visible = True
    Set myCopy.Myself = myCopy
End Sub

(CopyForm is the form name)

To close a form that may or may not be a form created by this function

Public Sub CloseMe()
    If Myself Is Nothing Then
        DoCmd.Close acForm, Me.Name
    Else
        Set Myself = Nothing
    End If
End Sub

More information on having multiple variants of the same form open can be found here, but my approach differs from the approach suggested here, and doesn't require a second object to hold references and manage copies.

回答2:

This line of code

`DoCmd.Close acForm, "ECOLI_Data", acSaveYes`  

doesn't save the record you are on, it just saves any changes to the form design.

You should probably use

If Me.Dirty Then Me.dirty = False 

to force a save of the current record if any data has changed.