[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

Has Data

Can I find out if a form HasData before I try to open it ?

I have tried this

If Me.Forms!AppointmentsAll.HasData = -1 Then
    DoCmd.OpenForm "AppointmentsAll"
End If

It doesn't work

Derek
0
DatabaseDek
Asked:
DatabaseDek
  • 2
1 Solution
 
Dale FyeCommented:
No, but you can find out whether the table or query that the form is based upon has data.

If fnHasData(TableName) Then Docmd.OpenForm "AppointmentsAll"

Change 'TableName' in the above to the name of the table or query that the form is based upon.  Then add this function to your code.
Public Function fnHasData(TableName as string) as boolean

    Dim strSQL as string
    Dim rs as DAO.Recordset

    strSQL = "SELECT Top1 * FROM [" & TableName & "]"
    set rs = currentdb.openrecordset(strsql,, dbfailonerror)

    fnHasData = iif(rs.eof, false, true)

ProcExit:
    OnError Resume next
    rs.close
    set rs = nothing
    Exit Function

ProcError:
    fnHasData = false
    Debug.print "fnHasData", err.number, err.description
    msgbox err.number & vbcrlf & err.description, , "fnHasData error"
    Resume ProcExit

End function

Open in new window

Or, you could simply use DLOOKUP() to see whether the forms recordsource has any records:
If Isnull(DLOOKUP("FieldName", "TableName")) = False Then 
    docmd.openform "AppointmentsAll"
endif

Open in new window

0
 
DatabaseDekAuthor Commented:
Thank you!!

Derek
0
 
Dale FyeCommented:
You could also use the Form_Open event to determine whether there are any records in the form.

Private Sub Form_Open(Cancel As Integer)

    Cancel = (Me.RecordsetClone.RecordCount = 0)
   
End Sub

However, if you use this technique, you will need to handle the error that occurs when you cancel the form_Open event.  This would have to be done in the routine that calls the form, so it is better to simply test before hand.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now