Camand Button on a Seach Form

[Jkittle]

I have a form that opens up and asked you to select the dates you want to run a report for. After that I run the code below in my command button, but the form stays open on top of the report. How can I get my date select form to close after the report opens up?

Private Sub cmdCalFrom_Click()
PopupCalendar Me.txtDateFrom
Me.txtDateFrom = Format(Me.txtDateFrom, "mm/dd/yyyy")
End Sub

Private Sub cmdCalTo_Click()
PopupCalendar Me.txtDateTo
Me.txtDateTo = Format(Me.txtDateTo, "mm/dd/yyyy")
End Sub

Private Sub cmdOpenReport_Click()
On Error GoTo err_handler
DoCmd.OpenReport "R-Sort Results", acViewPreview
Exit Sub
err_handler:
If Err.Number <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error number: " & Err.Number
End If
End Sub

 

[pbaldy]

Look at this in VBA Help:

DoCmd.Close

 

[Jkittle]

Where in the code do I place it? I tried and I get an error.

 

[CraigDolphin]

I think the idea is that you place the code after your line

DoCmd.OpenReport "R-Sort Results", acViewPreview

However, I have a question. Is your report bound to a parameter query which references the controls on your date selector form?

If so, when you close the form, the query will not be able to find the controls and your report will cause an error to occur. You may be able to avoid this error by changing the line

DoCmd.OpenReport "R-Sort Results", acViewPreview

to

DoCmd.OpenReport "R-Sort Results", acViewPreview, , ,acDialog

the acDialog option will force the code to halt while the report is open. Then, when the report closes the next line will be read

Docmd.Close

which should close your date selection form.

If you want to hide the parameter form while the report is open you could try the following:

Me.Visible = False
DoCmd.OpenReport "R-Sort Results", acViewPreview, , ,acDialog
Docmd.Close acForm, "YourDateSelectorFormNameHere", acSaveNo

(the longer version of the docmd.close command makes sure that the form you want closed is the one closed by the command even if another form has the focus when the comman runs).

 

[Jkittle]

Yes in my query I'm using Between [Forms]![F-Date Selector NCM Chart].[txtDateFrom] And [Forms]![F-Date Selector NCM Chart].[txtDateTo].

When I close the date selection form it minimizes my report as well. I fooling around with the code but I'm not getting any where. I'm a newbie as I'm sure you've figured out by now.

 

[CraigDolphin]

Have you tried the code I gave you in the cmdOpenReport_Click event? Make sure to replace the "YourDateSelectorFormNameHere" with the actual name of the date selector form.

Otherwise, can you post a stripped down, zipped version of your db here?

 

[Jkittle]

Craig,

The code you gave me is working fine (thank you!). The only question I have now is the reports open up smaller.

 

[CraigDolphin]

I don't know why the report would open smaller but you could put DoCmd.Maximize in the reports OnOpen event to maximize the report window.

 

[Jkittle]

Thanks Craig

 

[CraigDolphin]

Glad I could help