Form no longer maximizing

[LaurieW]

I have buttons on a form that run a query to filter the records and then open another form to display the records.

I just added the following code on the second form to show a "No Records Found" message when the query returns no records:

Private Sub Form_Load()
If Me.Recordset.RecordCount = 0 Then
DoCmd.Close
DoCmd.Close acQuery, "qryFrmQuickDE"
DoCmd.Maximize
MsgBox "No records found"
Else
DoCmd.Maximize
End If
End Sub


After adding the above code to the second form's Load event, the first form will no longer maximize. I have DoCmd.Maximize on the first form in the On Load, On Got Focus, On Open, On Resize and On Activate form events. I've tried adding DoCmd.Maximize to many of the other events with no luck.

Can anyone tell me how to get the first form to maximize after the "No Records Found" message box is closed?

 

[GinaWhipp]

Is the first Form a Pop-Up?

 

[LaurieW]

I don't know what you mean by pop up.
The forms work like this in my database:
There is a main menu form, with buttons that open other forms. One of the buttons on the main menu opens my form that will not maximize.

 

[LaurieW]

I figured out what you mean by pop up -- Pop Up is set to NO in the properties of the form that will not maximize.

 

[GinaWhipp]

I just READ your code and not sure I understand...

Private Sub Form_Load()
 
DoCmd.Maximize
 
If Me.Recordset.RecordCount = 0 Then
DoCmd.Close <<<< Why is that there? You are already closing the query so what are you closing here?
DoCmd.Close acQuery, "qryFrmQuickDE"
MsgBox "No records found"
End If
 
End Sub

I also moved the DoCmd.Maximize because you want that to happen when the Form Loads no matter what.

 

[LaurieW]

I got that code from another post.

When I change the code to what you suggested, it is worse. The second form opens and stays open with the message box on top.

I added the extra DoCmd.Close back in and the second form now closes, but the first form still does not maximize.

 

[LaurieW]

My goal was to tell the user there were no records and not have the user see the empty second form when there were no records.

This opens the second (empty) form when there are no records and tells them there are no records PLUS it maximizes the first form when they exit. I guess this is a viable option that I will settle for.

Thank you for your help!

 

[GinaWhipp]

Hmm, wonder if you should move that message to On_Open and, of course, leave the DoCmd.Maximize in the On_Load event.

Side note... I would check for Records with a DLookup() and use that present the massage thereby never opening the second Form.

 

[LaurieW]

Might you have an example of how to use dlookup? I don't know that I've used it before. Would that code go on the first form's button or still on the second form's OnOpen or OnLoad event?

 

[GinaWhipp]

Feeling stupid , meant DCount()

So, in the first Form on the clicking of the button...

If DCount("FieldFromTableOrQuery", "YourTableOrQuery", "[FieldFromTableOrQuery]=" & Me![FieldFromForm]) = 0 Then
     MsgBox "No records!"
Else
     DoCmd.OpenForm...
End if

Sample...

If ID field is numeric...

DCount("FieldFromTableOrQuery", "YourTableOrQuery", "[FieldFromTableOrQuery]=" & Me![FieldFromForm])

If ID field is text…

DCount("FieldFromTableOrQuery", "YourTable", "[FieldFromTableOrQuery]='" & Me![FieldFromForm] & "'")

If no ID you can just count the Records...

DCount("FieldFromTableOrQuery", "YourTableOrQuery") = 0

 

[LaurieW]

I've tried all of these with no success. Is it because the code is on the form button but is referencing the query before it has run? I keep getting an error that it doesn't understand/can't find my fields.

 

[GinaWhipp]

What error? Also, now that you have typed it up can you post what you have done here...

 

[LaurieW]

When I try to just count the records, the second form opens (no VB error), the msgbox appears and the second form stays open.

Here is the code:

Private Sub cmdMtAiry_Click()
If DCount("RezName", "qryFrmQuickDE") = 0 Then
MsgBox "No Records Found"
Else: DoCmd.OpenForm "frmQuickDE", , , "QuickDEGroup = 'Mt. Airy'"
End If
End Sub

I tried adding a DoCmd.Close and the second form still stays open.

When I try to use dcount for text/string I get a Run-Time Error # 2465: Can't find the field "RezName" referred to in your expression.

Here is the code:

Private Sub cmdMtAiry_Click()
If DCount("RezName", "qryFrmQuickDE", "[RezName]=" & Me![RezName] & "'") = 0 Then
MsgBox "No records!"
Else
DoCmd.OpenForm "frmQuickDE", , , "QuickDEGroup = 'Mt. Airy'"
End If

End Sub

 

[GinaWhipp]

Let's try...

Private Sub cmdMtAiry_Click()
 
If DCount("RezName", "qryFrmQuickDE") > 0 Then
   DoCmd.OpenForm "frmQuickDE", , , "QuickDEGroup = 'Mt. Airy'"
Else
   MsgBox "No Records Found"
End If
 
End Sub

 

[LaurieW]

That code no longer gives me an error, but it still opens the form and leaves the form open.

Again, this is not what I was hoping to achieve, but it works. So I guess I'll stick with this. Thank you again.

 

[GinaWhipp]

Okay, let's try putting a...

DoCmd.CancelEvent

..in the second part because the second form should not open.

 

[LaurieW]

My apologies. I forgot I still had this code on the second form's On Open event:
DoCmd.Maximize
If Me.Recordset.RecordCount = 0 Then
DoCmd.Close acQuery, "qryFrmQuickDE"
MsgBox "No records found"
End If

When I take out all but the DoCmd.Maximize on that form AND have this on the first form's button Click event:

If DCount("RezName", "qryFrmQuickDE") > 0 Then
DoCmd.OpenForm "frmQuickDE", , , "QuickDEGroup = 'Mt. Airy'"
Else
MsgBox "No Records Found"
DoCmd.CancelEvent
End If

The second form opens, I get no VB error, and I also do not get the message box. And the second form remains open. Do I have the CancelEvent in the wrong place?

 

[GinaWhipp]

Are there any other Events on the second Form and are there any events on the first that *call* the second form in another event procedure perhaps?

 

[LaurieW]

On the second form, I have DoCmd.Maximize on the On Load, On Got Focus, On Close and On Activate events.

On the first form, I have a total of seven buttons that call the second form, filling in the query "qryFrmQuickDE" with the location "QuickDEGroup = '[location]'. For example, the Mt. Airy button code says "QuickDEGroup = 'Mt. Airy'"; the Dunedin button code says "QuickDEGroup = 'Dunedin'", etc. When one of the buttons is pressed, the appropriate data for that button's location is selected for the second form.

I was trying to get one button to work first before I copied the code to the other buttons.

 

[LaurieW]

No it is not. None of my forms are pop ups.