Selecting a report range

T

Tony Williams

I have a form that I use to choose the range of a report. The form has a
combo box and if this is blank I want all the records but if there is a
value I only want the report to shown the equivalent records. Here is my
code
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Assigned Cases"
If Forms.[frmofficer].[cmbofficer] Is Null Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName, acPreview, , "[Officer] =
Forms.[frmofficer].[cmbofficer]"
End If
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

The Where condition works but I think the first part must be wrong because I
get a message that saysObject doesn't support this property or method. Where
am I going wrong? The combobox is a text box.
TRhanks
Tony
 
T

Tony Williams

I've also tried this
If IsNull(Forms.[frmofficer].[cmbofficer]) Then
and still get the same message.
Thanks
Tony
 
F

fredg

I have a form that I use to choose the range of a report. The form has a
combo box and if this is blank I want all the records but if there is a
value I only want the report to shown the equivalent records. Here is my
code
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Assigned Cases"
If Forms.[frmofficer].[cmbofficer] Is Null Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName, acPreview, , "[Officer] =
Forms.[frmofficer].[cmbofficer]"
End If
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

The Where condition works but I think the first part must be wrong because I
get a message that saysObject doesn't support this property or method. Where
am I going wrong? The combobox is a text box.
TRhanks
Tony
Click to expand...


Does this help?

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Assigned Cases"
If IsNull(Me![cmbofficer]) Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName, acPreview, , "[Officer] = " &
Me![cmbofficer]
End If
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Note: The above assumes the bound column of the combo box is a Number
datatype.
If in fact the bound column is Text datatype, then use:

DoCmd.OpenReport stDocName, acPreview, , "[Officer] = """ &
Me![cmbofficer] & """"
 
T

Tony Williams

Sorry to have posted have worked it out I've used this
If IsNull([cmbofficer]) Then
And it worked!
Cheers
Tony
 
T

Tony Williams

Thanks Fred I realised the IsNull statement may be at fault and I changed it
to

If IsNull(Forms.[frmofficer].[cmbofficer]) Then

This worked but I didn't add the Me! Is that necessary, it seemed to work ok
without it?
Thanks again
Tony
fredg said:
I have a form that I use to choose the range of a report. The form has a
combo box and if this is blank I want all the records but if there is a
value I only want the report to shown the equivalent records. Here is my
code
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Assigned Cases"
If Forms.[frmofficer].[cmbofficer] Is Null Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName, acPreview, , "[Officer] =
Forms.[frmofficer].[cmbofficer]"
End If
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

The Where condition works but I think the first part must be wrong
because I
get a message that saysObject doesn't support this property or method.
Where
am I going wrong? The combobox is a text box.
TRhanks
Tony
Click to expand...


Does this help?

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Assigned Cases"
If IsNull(Me![cmbofficer]) Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName, acPreview, , "[Officer] = " &
Me![cmbofficer]
End If
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Note: The above assumes the bound column of the combo box is a Number
datatype.
If in fact the bound column is Text datatype, then use:

DoCmd.OpenReport stDocName, acPreview, , "[Officer] = """ &
Me![cmbofficer] & """"
Click to expand...
 
F

fredg

Thanks Fred I realised the IsNull statement may be at fault and I changed it
to

If IsNull(Forms.[frmofficer].[cmbofficer]) Then

This worked but I didn't add the Me! Is that necessary, it seemed to work ok
without it?
Thanks again
Tony
fredg said:
I have a form that I use to choose the range of a report. The form has a
combo box and if this is blank I want all the records but if there is a
value I only want the report to shown the equivalent records. Here is my
code
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Assigned Cases"
If Forms.[frmofficer].[cmbofficer] Is Null Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName, acPreview, , "[Officer] =
Forms.[frmofficer].[cmbofficer]"
End If
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

The Where condition works but I think the first part must be wrong
because I
get a message that saysObject doesn't support this property or method.
Where
am I going wrong? The combobox is a text box.
TRhanks
Tony
Click to expand...


Does this help?

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Assigned Cases"
If IsNull(Me![cmbofficer]) Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName, acPreview, , "[Officer] = " &
Me![cmbofficer]
End If
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Note: The above assumes the bound column of the combo box is a Number
datatype.
If in fact the bound column is Text datatype, then use:

DoCmd.OpenReport stDocName, acPreview, , "[Officer] = """ &
Me![cmbofficer] & """"
Click to expand...
Click to expand...

The Me keyword refers to the name of the object in whose code it is
used.

If the name of the form that this code is on (and I believe it is) is
"frmofficer", then the Me! replaces forms!frmofficer
It's useful as it lessens the chance of a miss-spelled form name, and
the code is easily transportable form one sub to another.
It can not be used in a module, as the module is not associated with
any object.
 

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 Report - using a selection made by combo box 1
Adding control to report 2
stLinkcriteria no longer working (since 2007 upgrade) 1
Parameter for Report from Form 2
Datashhet View from another Form wont work 2
Maximizing preview window for report... 4
adding another control 1
Filtering a report by controls 4

Top