How do I check for the number of records?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is the problem:

I have a form, with a button that opens another form, that decides which
records to show based on it's foreign key matching the primary key of the
first form. If there is at at least one record, my application works fine
and displays it.

However, since the user cannot directly create these records in the table, I
do not allow them "Add Record" privilages on the form that will be open.
Because of this, if there are no records to show, the screen appears totally
blank.

I don't want my users faced with a blank screen. I would greatly prefer if
I could write some code that, as the form is opened, checks to see if there
are any records to display. If there are not, then a messagebox appears and
says: "Please do such-and-such to create one of these records." No blank
screen.

So...essentially what I need is a couple lines of code that checks a table
to the number of records that have a certain foreign key. Thoughts?
Comments? Suggestions?

Thanks!

Dustin Ventin
 
Hi Dustin,

One quick and easy way would be to check if there will be any records for
the second form before you open it, and display a message - rather than
opening an empty form, and forcing users to add a dummy record - if there
are none. Something like:

if dcount("*","formrecordsource","foreignkey = " & me.primarykey) = 0
then
msgbox "There are no records to display" 'and anything else you
want
else
docmd.openform ....
end if

HTH,

Rob
 
Hi Dustin

You could put some code such as this in your second form's Open event
procedure:

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records to display: blah blah"
Cancel = True
End If
 
This causes a second messagebox to appear that informs the user that the
OpenForm method was cancelled. Any idea how to prevent this?

I tried DoCmd.SetWarnings = False and calling the code of the close button
in the form, but neither seemed to work. Any ideas?

Thanks!

Dustin

Graham Mandeno said:
Hi Dustin

You could put some code such as this in your second form's Open event
procedure:

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records to display: blah blah"
Cancel = True
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Dustin Ventin said:
Here is the problem:

I have a form, with a button that opens another form, that decides which
records to show based on it's foreign key matching the primary key of the
first form. If there is at at least one record, my application works fine
and displays it.

However, since the user cannot directly create these records in the table,
I
do not allow them "Add Record" privilages on the form that will be open.
Because of this, if there are no records to show, the screen appears
totally
blank.

I don't want my users faced with a blank screen. I would greatly prefer
if
I could write some code that, as the form is opened, checks to see if
there
are any records to display. If there are not, then a messagebox appears
and
says: "Please do such-and-such to create one of these records." No blank
screen.

So...essentially what I need is a couple lines of code that checks a table
to the number of records that have a certain foreign key. Thoughts?
Comments? Suggestions?

Thanks!

Dustin Ventin
Click to expand...
Click to expand...
 
Hi Dustin

Yes, this is the mechanism for the form to report back to the code that
tried to open it that all did not go as expected.

You can either:

1. Ignore all errors:

On Error Resume Next
DoCmd.OpenForm ...

or, better...

2. Use an error handler that traps all errors and ignores that particular
one:

On Error Goto Errhandler
...
DoCmd.OpenForm ...
...
ExitProc:
Exit Sub
ErrHandler:
if Err <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error opening form"
End If
resume ExitProc

Dustin Ventin said:
This causes a second messagebox to appear that informs the user that the
OpenForm method was cancelled. Any idea how to prevent this?

I tried DoCmd.SetWarnings = False and calling the code of the close button
in the form, but neither seemed to work. Any ideas?

Thanks!

Dustin

Graham Mandeno said:
Hi Dustin

You could put some code such as this in your second form's Open event
procedure:

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records to display: blah blah"
Cancel = True
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Dustin Ventin said:
Here is the problem:

I have a form, with a button that opens another form, that decides
which
records to show based on it's foreign key matching the primary key of
the
first form. If there is at at least one record, my application works
fine
and displays it.

However, since the user cannot directly create these records in the
table,
I
do not allow them "Add Record" privilages on the form that will be
open.
Because of this, if there are no records to show, the screen appears
totally
blank.

I don't want my users faced with a blank screen. I would greatly
prefer
if
I could write some code that, as the form is opened, checks to see if
there
are any records to display. If there are not, then a messagebox
appears
and
says: "Please do such-and-such to create one of these records." No
blank
screen.

So...essentially what I need is a couple lines of code that checks a
table
to the number of records that have a certain foreign key. Thoughts?
Comments? Suggestions?

Thanks!

Dustin Ventin
Click to expand...
Click to expand...
Click to expand...
 
That's it, thanks!

Dustin

Graham Mandeno said:
Hi Dustin

Yes, this is the mechanism for the form to report back to the code that
tried to open it that all did not go as expected.

You can either:

1. Ignore all errors:

On Error Resume Next
DoCmd.OpenForm ...

or, better...

2. Use an error handler that traps all errors and ignores that particular
one:

On Error Goto Errhandler
...
DoCmd.OpenForm ...
...
ExitProc:
Exit Sub
ErrHandler:
if Err <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error opening form"
End If
resume ExitProc

Dustin Ventin said:
This causes a second messagebox to appear that informs the user that the
OpenForm method was cancelled. Any idea how to prevent this?

I tried DoCmd.SetWarnings = False and calling the code of the close button
in the form, but neither seemed to work. Any ideas?

Thanks!

Dustin

Graham Mandeno said:
Hi Dustin

You could put some code such as this in your second form's Open event
procedure:

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records to display: blah blah"
Cancel = True
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Here is the problem:

I have a form, with a button that opens another form, that decides
which
records to show based on it's foreign key matching the primary key of
the
first form. If there is at at least one record, my application works
fine
and displays it.

However, since the user cannot directly create these records in the
table,
I
do not allow them "Add Record" privilages on the form that will be
open.
Because of this, if there are no records to show, the screen appears
totally
blank.

I don't want my users faced with a blank screen. I would greatly
prefer
if
I could write some code that, as the form is opened, checks to see if
there
are any records to display. If there are not, then a messagebox
appears
and
says: "Please do such-and-such to create one of these records." No
blank
screen.

So...essentially what I need is a couple lines of code that checks a
table
to the number of records that have a certain foreign key. Thoughts?
Comments? Suggestions?

Thanks!

Dustin Ventin
Click to expand...
Click to expand...
Click to expand...
 
Back
Top