message instead of blank screen when no records? 4

[hlkelly]

I have a form which after selecting a customer it will show the orders that customer has placed. Unfortunately, if there are no orders, a blank gray screen opens. I'd like a message to indicate that there aren't any records or something like that.

Can anyone help?

Thanks. Heather

 

[missinglinq]

Use DCount to check for records. If records <1 (no records returned) then have a popup form that says &quot;There Are No Orders&quot; with an OK buttom come up.

The Missinglinq

&quot;It's got to be the going,
not the getting there that's good!&quot;
-Harry Chapin

 

[hlkelly]

OK. This is good. Now, seeing as how I'm new at this...

AFter the customer selection from the cbo, user would click cmdbutton to search (if there are orders a new form opens). Is DCount in the OnClick event for the cmdbutton?

 

[CajunCenturion]

In addition to the DCount and Popup technique, you can also do the following in the Form_Load event

Private Sub Form_Load()
   If (Me.Recordset.RecordCount = 0) Then
      MsgBox ("Say Whatever You Want")
      <Do what you need to do>
   End If
End Sub

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[hlkelly]

Cajun...

This worked great. I got the pop up that indicates there are no records...but...the blank gray box still pops up in the background. Can it keep it from doing that?

 

[RickSpr]

Testing RecordCount can give wrong results in certain circumstances. Here's a test that will always work:

Public Function RecordsetIsEmpty(Form As Form) As Boolean
' Return True if the form's recordset is empty.
' REQUIRES ACCESS 2000 OR LATER
    RecordsetIsEmpty = Form.Recordset.BOF And Form.Recordset.EOF
End Function

Call it from a form like this:

    If RecordsetIsEmpty(Me) Then MsgBox "No records found"

The trick is, at what point do you execute this test? You'll surely want to do it in the Open event, but possibly also in the Filter event. If you use VBA code to modify the form's RecordSource, Filter, or FilterOn property, you may need to do it then as well.

If the recordset is empty and either the recordset is not updatable or the form doesn't allow record additions, the blank background will happen. You can't prevent it, unless you can make the recordset updatable and make the form allow additions.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[hlkelly]

rickspr

I think I'm too much of a novice to try that approach. I put your suggested code in the on open even just to try it out and i get a bunch of error messages.

Do I put
RecordsetIsEmpty = Form.Recordset.BOF And Form.Recordset.EOF
End Function

in the on open event? I know nothing about public function

 

[CajunCenturion]

If you choose to use Rick's approach, which is the better approach, then you should create a module, and then add the function, exactly as Rick has written it into that module. Then in your Form_Load event would look like the following:

Private Sub Form_Load()
   If (RecordsetIsEmpty(Me)) Then
      MsgBox ("Say Whatever You Want")
      <Do what you need to do>
   End If
End Sub

Now, to answer your question about the black gray box, it all depends on what you want to do when you discover that the recordset is empty. So what do you want to have happen in that case?

Thanks Rick, I'm going to use that, although in my case, it will be mostly
If Not (RecordsetIsEmpty(Me)) Then


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[RickSpr]

I think you did the right thing. You should also have copied the code for the Public Function to either the bottom of the form module, or to a standard module. Did you do that?

If you have Access 97, you can fix the function to work by changing it to 'Form.RecordsetClone.BOF' and 'Form.RecordsetClone.EOF'.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[hlkelly]

I got it. Thanks to all that helped! I'll get those stars posted!

Thanks so much!

Heather

 

[hlkelly]

Ok...actually, I still have the gray box. If there aren't any records, I'd like to return to my form where the customer cbo is. Or, if it's easier, click 'ok' and return to the original form where the customer choice is.

sheesh...complicated.

 

[CajunCenturion]

You could then try the following:

Private Sub Form_Load()
If (RecordsetIsEmpty(Me)) Then
MsgBox (&quot;Say Whatever You Want&quot
DoCmd.Close
End If
End Sub


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[RickSpr]

Cajun,

I have almost never used Form_Load. I'm just used to Form_Open and use it without thinking. Besides, Form_Open has a Cancel parameter that Form_Load doesn't.

Do you know of anything that can not be done in Form_Open but will work in Form_Load? I'm sure there must be something, or Microsoft wouldn't have gone to the trouble of creating a Load event.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[CajunCenturion]

That's a good question Rick. I haven't used the Form_Open event, but that's primarily because mostly I program in VB6 which does not have a Form_Open event, so it's new to me. It wasn't until the last two projects that I've been working on that are pure Access by customer dictate, that I've become more involved with VBA, and although the differences are subtle, they do matter, and I'm still getting used to them. VB6 has the Form_Load and Form_Activate events, but the Form_Activate event is intercepted and not handed off to the application so it's not something you can count on. Consequently, I've gotten into the habit of using the Form_Load event for start-up checks. The downsides that I've experienced to the Form_Load event is that since you're really inside the Object Constructor (I am more sure of this in VB6 than VBA), you can't always guarantee that all the necessary children have been properly instantiated (ie controls on forms and such), and so even in VB6, I'm very hesitant to make references inside the _Load event. I have seen on more than one occasion a reference to a control that inside the _Load event causes an Invalid Object Reference error, not because object does not exist, but because it's not ready yet. Therefore, I don't do anything in the _Load event (other than variable initializations) that I don't absolutely have to, and have run sufficient test to insure that the timing in adequate.

If the Form_Open event fires after the Form, and all it's children, have been completely intantiated, and it only fires once on start up, then I would think it would be a far better event to use than Form_Load, for start-up checks. If that were the case, and I will investigate this, then it's most likely that the only tasks that I would perform in the Form_Load would be application defined variable intializations. All other checks would be in the Form_Open event.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein