Code for Open Form

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

What is the Code for Open Form but Do Not open or Do Nothing actually if
there are No Records found?
Thanks
DS
 
DS said:
What is the Code for Open Form but Do Not open or Do Nothing actually
if there are No Records found?
Thanks
Click to expand...

There are several ways to do this.

1. Check if there will be records before opening the form. Note that
this actually involves querying the data twice, so I don't like it much.
But code might look like this:

If Not IsNull(DLookup("SomeField", "SomeTableOrQuery")) Then
DoCmd.OpenForm "YourForm"
End If

2. Open the form and use code in its Open event to cancel its own
opening if there are no records:

' ----- code for form module -----
Private Sub Form_Open(Cancel As Integer)

If Me.Recordset.RecordCount = 0 Then
Cancel = True
End If

End Sub

' ----- end code for form module -----

When you do this, if you open the form from code you also have to
trap the error (2501) that will be raised due to the opening being
cancelled. The simplest example would be:

On Error Resume Next

DoCmd.OpenForm "Your Form"

If Err.Number <> 0 Then
If Err.Number <> 2501 Then
' This is a real error!
MsgBox Err.Description, vbExclamation, "Error " &
Err.Number
End If
End If

3. Open the form invisibly, check to see if it has any records, and
either make it visible or close it accordingly:

DoCmd.OpenForm "YourForm", WindowMode:=acHidden
With Forms!YourForm
If .Recordset.RecordCount = 0 Then
MsgBox "No records to display."
DoCmd.Close acForm, "YourForm", acSaveNo
Else
.Visible = True
End If
End With
 
Try this, using the table name & its first field name (of the table that is
the record source of the form being opened) in the DCount.

Private Sub Form_Open(Cancel As Integer)
If DCount "[FirstFieldName]","[TableName]") = 0 Then
MsgBox "No records found."
Cancel = True
Exit Sub
End Sub

This will display the message if there are no records, then cancel opening
the form. The question is: How will anyone put anything into the table if
they cannot open its form until there are records in its underlying table?
 
Brian said:
Try this, using the table name & its first field name (of the table that is
the record source of the form being opened) in the DCount.

Private Sub Form_Open(Cancel As Integer)
If DCount "[FirstFieldName]","[TableName]") = 0 Then
MsgBox "No records found."
Cancel = True
Exit Sub
End Sub

This will display the message if there are no records, then cancel opening
the form. The question is: How will anyone put anything into the table if
they cannot open its form until there are records in its underlying table?


:

What is the Code for Open Form but Do Not open or Do Nothing actually if
there are No Records found?
Thanks
DS
Click to expand...
Click to expand...
They really aren'y putting any records in.. the form has a list box on
it that is populated from a listbox on the first form. I just don't
want that second form to show if there is nothing in the listbox on the
second form. I hope this works because the Listbox on the second form
is set to populate from the first forms after update property.
Thanks for the help.
DS
 
Dirk said:
There are several ways to do this.

1. Check if there will be records before opening the form. Note that
this actually involves querying the data twice, so I don't like it much.
But code might look like this:

If Not IsNull(DLookup("SomeField", "SomeTableOrQuery")) Then
DoCmd.OpenForm "YourForm"
End If

2. Open the form and use code in its Open event to cancel its own
opening if there are no records:

' ----- code for form module -----
Private Sub Form_Open(Cancel As Integer)

If Me.Recordset.RecordCount = 0 Then
Cancel = True
End If

End Sub

' ----- end code for form module -----

When you do this, if you open the form from code you also have to
trap the error (2501) that will be raised due to the opening being
cancelled. The simplest example would be:

On Error Resume Next

DoCmd.OpenForm "Your Form"

If Err.Number <> 0 Then
If Err.Number <> 2501 Then
' This is a real error!
MsgBox Err.Description, vbExclamation, "Error " &
Err.Number
End If
End If

3. Open the form invisibly, check to see if it has any records, and
either make it visible or close it accordingly:

DoCmd.OpenForm "YourForm", WindowMode:=acHidden
With Forms!YourForm
If .Recordset.RecordCount = 0 Then
MsgBox "No records to display."
DoCmd.Close acForm, "YourForm", acSaveNo
Else
.Visible = True
End If
End With
Click to expand...
Hi, Thank you for all of the input. I'll give this a try. It's for use
with the Listboxes that I have been posting about. The problem is when
you click on Listbox1 on form1 the second form opens up with a populated
listbox, however when there are no records to put in that second forms
listbox the form still comes up and its empty. Not even the close
button is available. I'll give these suggestions a try.
Thank You once again.
DS
 
DS said:
They really aren'y putting any records in.. the form has a list box on
it that is populated from a listbox on the first form. I just don't
want that second form to show if there is nothing in the listbox on
the second form. I hope this works because the Listbox on the second
form is set to populate from the first forms after update property.
Thanks for the help.
Click to expand...

Note that this is a significantly different question from the one you
originally asked, and I answered. It seems now that you aren't
concerned about whether the second *form* has any records to display;
you want to know whether the *list box* on that form will have any items
in its list. For this purpose you are best off using a DLookup to find
out first whether there will be any items in the list, and only then
opening the second form.
 
Dirk said:
There are several ways to do this.

1. Check if there will be records before opening the form. Note that
this actually involves querying the data twice, so I don't like it much.
But code might look like this:

If Not IsNull(DLookup("SomeField", "SomeTableOrQuery")) Then
DoCmd.OpenForm "YourForm"
End If

2. Open the form and use code in its Open event to cancel its own
opening if there are no records:

' ----- code for form module -----
Private Sub Form_Open(Cancel As Integer)

If Me.Recordset.RecordCount = 0 Then
Cancel = True
End If

End Sub

' ----- end code for form module -----

When you do this, if you open the form from code you also have to
trap the error (2501) that will be raised due to the opening being
cancelled. The simplest example would be:

On Error Resume Next

DoCmd.OpenForm "Your Form"

If Err.Number <> 0 Then
If Err.Number <> 2501 Then
' This is a real error!
MsgBox Err.Description, vbExclamation, "Error " &
Err.Number
End If
End If

3. Open the form invisibly, check to see if it has any records, and
either make it visible or close it accordingly:

DoCmd.OpenForm "YourForm", WindowMode:=acHidden
With Forms!YourForm
If .Recordset.RecordCount = 0 Then
MsgBox "No records to display."
DoCmd.Close acForm, "YourForm", acSaveNo
Else
.Visible = True
End If
End With
Click to expand...

I chose option Number 2 and it works great!...The only thing I don't
understand is where to put this error code.
Does it go after the code on the On Open Property. or does it go on the
On Error property of the form?
Thanks
DS
 
Sorry about my late post, Dirk. I started it before yours was posted, got
called away to something else, and then finished it without first checking
for other responses...
 
DS said:
Dirk said:
There are several ways to do this. [...]
2. Open the form and use code in its Open event to cancel its own
opening if there are no records:

' ----- code for form module -----
Private Sub Form_Open(Cancel As Integer)

If Me.Recordset.RecordCount = 0 Then
Cancel = True
End If

End Sub

' ----- end code for form module -----

When you do this, if you open the form from code you also have to
trap the error (2501) that will be raised due to the opening being
cancelled. The simplest example would be:

On Error Resume Next

DoCmd.OpenForm "Your Form"

If Err.Number <> 0 Then
If Err.Number <> 2501 Then
' This is a real error!
MsgBox Err.Description, vbExclamation, "Error " &
Err.Number
End If
End If
Click to expand...
[...]

I chose option Number 2 and it works great!...
Click to expand...

That's odd, considering what you have since told me about the list box.
Is your form based on the same query that the list box is based on?
That would be unnecessary -- it results in the query being called twice:
once to pull up records for the form to display and edit, and once to
povide rows for the list box. If you're just going to display records
in the list box, I would expect the form to be unbound -- have no
recordsource at all. At least one of us is confused. :-)
The only thing I don't
understand is where to put this error code.
Does it go after the code on the On Open Property. or does it go on
the On Error property of the form?
Click to expand...

Neither. That last code snippet would replace the code you currently
have in place on the first form, to open the second form. Its purpose
is to open the form, check if there was an error, and if there *was* an
error, determine if it was just that the open was cancelled. If it
wasn't just a "cancel" error, then the code displays a message.

But, as I said, now I'm not at all sure this is the best way to do what
you want.
 
Brian said:
Sorry about my late post, Dirk. I started it before yours was posted,
got called away to something else, and then finished it without first
checking for other responses...
Click to expand...

Don't worry about it. We all try not to step on each other's toes, but
this isn't a real-time medium. As far as I'm concerned, the more people
chiming in, the better.
 
Back
Top