Close/Hide Form when No Records

[CT Witter]

I have a form that is based on a recordset. The issue that I'm having is closing/hiding the form when no records are present.
I was going to check the recordset before opening this form but with a lot of records this will get slow to do a preliminary check. I'd rather go with that there will be records and if not just exit the form.

Private Sub Form_Load()
On Error GoTo Err_Form_Load
    Set mrst = New ADODB.Recordset
    mrst.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\X\x.mdb"
    mrst.Source = strSQLView
    mrst.CursorType = adOpenStatic
    mrst.LockType = adLockBatchOptimistic
    mrst.CursorLocation = adUseClient
    mrst.Open Options:=adAsyncFetch
Exit_Form_Load:
    Exit Sub
Err_Form_Load:
    If Err.Number = "-2147217908" Then
    MsgBox "No Records Found!" & vbCrLf & vbCrLf & "Please Try Again", vbInformation + vbOKOnly, "No Records Found!"
    Me.Visible = False
    DoCmd.OpenForm "frmSearchDialog"
    End If
   MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Form_Load
End Sub

Private Sub Form_Unload(Cancel As Integer)

   On Error GoTo Form_Unload_Error
    If MsgBox("Save all changes back to the database?", vbQuestion + vbYesNo) = vbYes Then
    Dim wksp As DAO.Workspace
    Set wksp = DBEngine.Workspaces(0)
        'This will commit only changed records to the database
        wksp.BeginTrans
        'Update the Updated Options
        mrst!Updated = Now()
        mrst!UpdatedBy = CurrentUser()
        mrst.MarshalOptions = adMarshalModifiedOnly
        mrst.UpdateBatch
        wksp.CommitTrans
    Else
        mrst.CancelBatch
    End If

Exit_Form_Unload:
    Exit Sub
Err_Form_Unload:
    'wksp.Rollback
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Form_Unload
End Sub

I keep getting an undefined error (Error 0 at line ) from the Unload Event Any help?

Thanks,
CT

 

[Marbles]

Try

I tried this on a form I have, but with one field.


Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord , , acPrevious
DoCmd.GoToControl "EndTime"
If "EndTime" = "" Then
DoCmd.Close
End If

End Sub

Would it work with a field that would have something in it, if the record wasn't empty?

 

[Marbles]

Try

I should have said; a field that normally always has some data.

 

[CT Witter]

Thanks for the suggestion but won't work in this case as the recordset for the form isn't initalized during the form open.