Opening a report from a form

[jderrig]

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

 

[Ofer Cohen]

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 & "'"

 

[jderrig]

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?

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

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

 

[Ofer Cohen]

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

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?

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

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