Opening a report from a form

G

Guest

OK, my brain must be fried because I can't seem to get this to work properly.
I have an unbound form with a combo box and a command button that I wish to
use to open several reports.

I have tried unsuccessfully to make the report open to show all records if
nothing is selected in the combo box. The only way I can get the form to open
a report is by putting this code in the On Click event for the command
button. But, this is not what I want. I want to be able to leave the combo
box blank and return all records.


Private Sub Preview_Click()
If IsNull([Account]) Then
MsgBox "You must select an account."
DoCmd.GoToControl "CHSAcctNo"

Else
Me.Visible = False
End If

End Sub


I have even tried putting this code in the event of the form and placing
Like [Forms]![frmMemberName]![Company] & "*" in the criteria of the query but
this will not produce the report.


Option Compare Database
Option Explicit

Private Sub Report_Close()
DoCmd.Minimize
DoCmd.Close acForm, "frmMemberName"
DoCmd.Maximize
End Sub




Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
DoCmd.OpenForm "frmMemberName", , , , , acDialog, "Bulk Plant Query"



End Sub


Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

Any suggestions would be great.

Thanks
 
G

Guest

Instead of creating the filter in the Query you can send the filter using the
WhereCondition of the OpenReport command line.
The report should be based on the Full data with no filter.

Use this code

Dim MyWhereCondition As String
If IsNull(Me.Account) Or Me.Account = "" Then
Docmd.OpenReport "ReportName"
Else
MyWhereCondition = "[FieldNameInTable] = " & Me.Account
Docmd.OpenReport "ReportName",,,MyWhereCondition
End If
==================
If the Account is text field type change the where condition to
MyWhereCondition = "[FieldNameInTable] = '" & Me.Account & "'"
 
G

Guest

Well, I was hoping to only have to create one form for multiple reports. If
I do it this way, I have to create the same form multiple times for all the
reports that I want filtered this way. Isn't there a way to only create one
form to use for all the reports that I have?

Ofer Cohen said:
Instead of creating the filter in the Query you can send the filter using the
WhereCondition of the OpenReport command line.
The report should be based on the Full data with no filter.

Use this code

Dim MyWhereCondition As String
If IsNull(Me.Account) Or Me.Account = "" Then
Docmd.OpenReport "ReportName"
Else
MyWhereCondition = "[FieldNameInTable] = " & Me.Account
Docmd.OpenReport "ReportName",,,MyWhereCondition
End If
==================
If the Account is text field type change the where condition to
MyWhereCondition = "[FieldNameInTable] = '" & Me.Account & "'"

--
HTH, Good Luck
BS"D


jderrig said:
OK, my brain must be fried because I can't seem to get this to work properly.
I have an unbound form with a combo box and a command button that I wish to
use to open several reports.

I have tried unsuccessfully to make the report open to show all records if
nothing is selected in the combo box. The only way I can get the form to open
a report is by putting this code in the On Click event for the command
button. But, this is not what I want. I want to be able to leave the combo
box blank and return all records.


Private Sub Preview_Click()
If IsNull([Account]) Then
MsgBox "You must select an account."
DoCmd.GoToControl "CHSAcctNo"

Else
Me.Visible = False
End If

End Sub


I have even tried putting this code in the event of the form and placing
Like [Forms]![frmMemberName]![Company] & "*" in the criteria of the query but
this will not produce the report.


Option Compare Database
Option Explicit

Private Sub Report_Close()
DoCmd.Minimize
DoCmd.Close acForm, "frmMemberName"
DoCmd.Maximize
End Sub




Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
DoCmd.OpenForm "frmMemberName", , , , , acDialog, "Bulk Plant Query"



End Sub


Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

Any suggestions would be great.

Thanks
Click to expand...
Click to expand...
 
G

Guest

In filter of the query try

[FieldName] Like IIf([Forms]![frmMemberName]![Company] Is Null Or
[Forms]![frmMemberName]![Company] = "", "*" ,
[Forms]![frmMemberName]![Company])


--
HTH, Good Luck
BS"D


jderrig said:
Well, I was hoping to only have to create one form for multiple reports. If
I do it this way, I have to create the same form multiple times for all the
reports that I want filtered this way. Isn't there a way to only create one
form to use for all the reports that I have?

Ofer Cohen said:
Instead of creating the filter in the Query you can send the filter using the
WhereCondition of the OpenReport command line.
The report should be based on the Full data with no filter.

Use this code

Dim MyWhereCondition As String
If IsNull(Me.Account) Or Me.Account = "" Then
Docmd.OpenReport "ReportName"
Else
MyWhereCondition = "[FieldNameInTable] = " & Me.Account
Docmd.OpenReport "ReportName",,,MyWhereCondition
End If
==================
If the Account is text field type change the where condition to
MyWhereCondition = "[FieldNameInTable] = '" & Me.Account & "'"

--
HTH, Good Luck
BS"D


jderrig said:
OK, my brain must be fried because I can't seem to get this to work properly.
I have an unbound form with a combo box and a command button that I wish to
use to open several reports.

I have tried unsuccessfully to make the report open to show all records if
nothing is selected in the combo box. The only way I can get the form to open
a report is by putting this code in the On Click event for the command
button. But, this is not what I want. I want to be able to leave the combo
box blank and return all records.


Private Sub Preview_Click()
If IsNull([Account]) Then
MsgBox "You must select an account."
DoCmd.GoToControl "CHSAcctNo"

Else
Me.Visible = False
End If

End Sub


I have even tried putting this code in the event of the form and placing
Like [Forms]![frmMemberName]![Company] & "*" in the criteria of the query but
this will not produce the report.


Option Compare Database
Option Explicit

Private Sub Report_Close()
DoCmd.Minimize
DoCmd.Close acForm, "frmMemberName"
DoCmd.Maximize
End Sub




Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
DoCmd.OpenForm "frmMemberName", , , , , acDialog, "Bulk Plant Query"



End Sub


Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

Any suggestions would be great.

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Open a form based on an unbound form 3
HELP 6
Re-hide my combo boxes when I go to the next record 5
Access Form - Lock Fields from Editing 0
Report Group Visibility 1
Opening a filterable report from a form 5
Form won't open report from preview button 5
Using a filter to show results on a report 5

Top