Macro, Onclick, OpenReport, Where Condition

Question

msOffice Help says to put the following string in the "Where Condition" of an OpenReport command in a Macro:

[field] = forms![formname]![controlname]

but when I type in:

[tasking] = forms![frmcurrenttaskingstatus]![combo10]

my report filters to #Error.

What am I doing wrong?



Answer 1

You are using calculated fields (expressions) in your Report?

If so, it will result in an #Error when there are no records to show in your Report.

When you run a Macro with a Form parameter make sure you have the Form open you are referring to.

So in this case frmcurrenttaskingstatus needs be opened before running the Macro.

Then make sure when using a Combobox that the value shown is the right one.

A Combobox can use many columns, make sure the Column number which is bound is the value you need to Filter your Field [tasking] in your Report.

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 2

The column [tasking] is not calculated, its a text value.

The combobox list information is pulled out of loaded information in another table, there are only valid values in it which are loaded to records.

The form is open as it has the button on it which runs this macro.

The combobox only has one column in it.

Im just trying to filter a report (rptTaskingTrainingReport) using column [tasking] based on the value on a combobox (combo10) which is on my "frmCurrentTaskingStatus" form.

Answer 3

Is this one of those stupid situations where i am forced to use a seperate query to base the data in my form on instead of a query built into my form?

Answer 4

Ok, so in what field does the error occur?

The field that is bound is containing the values which correspond with values in Field [tasking] in your Report?

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 5

All fields report errors

Value of the Combobox is "Tasking 3C", I have 3 values in the column [tasking] in the query which are "Tasking 3C".

Answer 6

Are you sure the bound column of the combo box is not a hidden column containing a surrogate key?  This will be the case if its BoundColumn property is 1, its ColumnCount property 2, and its ColumnWidths are something like 0cm;8cm, or even just 0cm.  If this is the case then you need to reference whatever is the bound column in the WhereCondition argument rather than the Tasking column, and the relevant column needs to be included in the reports underlying recordset of course.

---

Ken Sheridan, Stafford, England

Answer 7

It would help, if possible, you could share a stripped down database on SkyDrive to have a look.

Take out sensitive data, but have at least the Table/ Query/ Form and Report related to the problem available to analyze it.

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 8

Our network security wont let me use skydrive.  Also, all of the data in the database is based on SSNs so you wont be able to actually use it.  Don't know if that matters.

Answer 9

My SQL for my combobox is

SELECT tblDeploymentTaskings.ID, tblDeploymentTaskings.Tasking
FROM tblDeploymentTaskings
ORDER BY tblDeploymentTaskings.[ID];



Answer 10

Yes, so as already suspected in earlier replies, you have an ID field and Tasking field selected, that means there are two columns in your Combobox.

When you refer to forms![frmcurrenttaskingstatus]![combo10] the value of your ID will be validated instead of field Tasking.

Then it results in an #Error as the field values won't match.

So what you can do is change the condition to [tasking]=[Forms]![frmcurrenttaskingstatus]![combo10].[Column](1)

The Column numbering start with 0, so when ID is your first column, number 0, and your field Tasking is second, so number 1.

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 11

You might not be completely out of the woods.  Daniel's reply assumes  that the report's underlying recordset contains a text column 'tasking'.  That may well be the case, but there is also the possibility that the report's recordset might only include the numeric ID column and id showing the tasking text column by means of a combo box in the same way as that on your form is.  This is not a good way to design a report, but I think the report wizard does do this.  If so then the expression for the WhereCondition argument would still reference the value of the combo box, as you currently have it, but it would be the ID column not the tasking column on which the restriction is applied, so it would be:

[ID]=[Forms]![frmcurrenttaskingstatus]![combo10]

However it is set up, my preference would be to ensure that the report's recordset does include the ID column as well as the text tasking column to which a control in the report is bound, and to use the above expression in your macro.  It's always more reliable to place the restriction on a key column in cases like this.

---

Ken Sheridan, Stafford, England

Answer 12

I did this

[tasking]=[Forms]![frmcurrenttaskingstatus]![combo10].[Column](1)

and it gave me an undefined function error

I did this

[ID]=[Forms]![frmcurrenttaskingstatus]![combo10]

and it asked me for the parameter value for [ID].

Answer 13

rptTaskingTrainingReport Record Source
SELECT tblAlphaRosterKey.FullName, tblAlphaRosterKey.Unit, tblAlphaRosterKey.OffSymbol, 
tblAlphaRosterKey.[Band], qryCurrentlyTasked.Tasked, qryCurrentlyTasked.Tasking, 
qryCurrentlyTasked.DateStart, qryCurrentlyTasked.DateEnd, qryADLS2AMasterList.HRComp, 
qryADLS2AMasterList.FPComp, qryADLS2AMasterList.IAComp, qryADLS2AMasterList.IPComp, 
qryADLS2AMasterList.SAComp, qryADLS2BMasterList.EOPOSHComp, qryADLS2BMasterList.CRComp, 
qryADLS2BMasterList.CEComp, qryADLS2BMasterList.SAPComp, qryADLS2BMasterList.PRComp, 
qryADLS2BMasterList.CGComp, qryADLS2BMasterList.CBRNComp, qryADLS2BMasterList.CIEDComp, 
qryADLS2BMasterList.EORComp, qryADLS2BMasterList.LOACComp, qryADLS2BMasterList.SABCHOComp, 
qryADLS2BMasterList.SABCComp, qryCurrentlyTasked.M4Req, qryCurrentlyTasked.M9Req, 
qryCurrentlyTasked.M4Date, qryCurrentlyTasked.M9Date, qryCurrentlyTasked.CIEDAdvReq, 
qryCurrentlyTasked.CIEDAdvDate, qryCurrentlyTasked.SERE1001Re, qryCurrentlyTasked.SERE1001Date, 
qryCurrentlyTasked.AfghanLngReq, qryCurrentlyTasked.LngAfghanDate, qryCurrentlyTasked.IraqiLngReq, 
qryCurrentlyTasked.LngIraqDate, qryCurrentlyTasked.BiometricsReq, qryCurrentlyTasked.BioDate, 
qryCurrentlyTasked.COIN1Req, qryCurrentlyTasked.Coin1Date, qryCurrentlyTasked.COIN2Req, 
qryCurrentlyTasked.Coin2Date
FROM ((tblAlphaRosterKey LEFT JOIN qryCurrentlyTasked ON tblAlphaRosterKey.AlphaPriKey = qryADLS2AMasterList.ADLSPriKey) 
LEFT JOIN qryADLS2BMasterList ON tblAlphaRosterKey.AlphaPriKey = qryADLS2BMasterList.ADLSPriKey
WHERE (((qryCurrentlyTasked.Tasked)=Yes));

Answer 14

qryCurrentlyTasked where [Tasking] Comes from
SELECT tblAlphaRoster.[Full Name], tblAlphaRoster.SSAN, tblAssignedTaskings.Tasked, 
tblBandList.[Band] AS AssAEF, tblBandList.DateStart, tblBandList.DateEnd, 
tblDeploymentTaskings.Tasking, tblDeploymentTaskings.[2AReq], tblDeploymentTaskings.[2BReq], 
tblDeploymentTaskings.M4Req, tblDeploymentTaskings.M9Req, tblTrainingTracking.M4Date, 
tblTrainingTracking.M9Date, tblDeploymentTaskings.CIEDAdvReq, tblTrainingTracking.CIEDAdvDate, 
tblDeploymentTaskings.SERE1001Re, tblTrainingTracking.SERE1001Date,
tblDeploymentTaskings.AfghanLngReq, tblTrainingTracking.LngAfghanDate, 
tblDeploymentTaskings.IraqiLngReq, tblTrainingTracking.LngIraqDate, 
tblDeploymentTaskings.BiometricsReq, tblTrainingTracking.BioDate, 
tblDeploymentTaskings.COIN1Req, tblTrainingTracking.Coin1Date, 
tblDeploymentTaskings.COIN2Req, tblTrainingTracking.Coin2Date, tblDeploymentTaskings.[Band]
FROM (((tblAlphaRoster LEFT JOIN tblAssignedTaskings ON tblAlphaRoster.SSAN = tblAssignedTaskings.SSAN) 
LEFT JOIN tblBandList ON tblAlphaRoster.[AEF Ind] = tblBandList.[Band])
 LEFT JOIN tblDeploymentTaskings ON tblAssignedTaskings.Tasking = tblDeploymentTaskings.ID)
 LEFT JOIN tblTrainingTracking ON tblAlphaRoster.SSAN = tblTrainingTracking.SSAN
WHERE (((tblAssignedTaskings.Tasked)=Yes));

Answer 15

I don't see the tblDeploymentTaskings.ID column in the query's SELECT clause.  Add it to qryCurrentlyTasked
 and then to the report's RecordSource query and the set the WhereCondition argument to:

[ID]=[Forms]![frmcurrenttaskingstatus]![combo10]

---

Ken Sheridan, Stafford, England

Answer 16

I added it to both and am still getting #Error

On the plus side, its not asking me to fill in a parameter value for [ID] any longer.

Answer 17

Are you still getting the Error in all bound controls in the report?  If so I'd check that their ControlSource properties are correct.

---

Ken Sheridan, Stafford, England

Answer 18

Do you mean the controlsource for the combo box?  That currently has nothing in it at all.  The record source has the info from above in it.

Answer 19

No, I mean the ControlSource properties of the bound controls in the report.

---

Ken Sheridan, Stafford, England

Answer 20

Sorry I didn't get back but I had a 4 day weekend.

The report has a record source and a filter... Not sure what you mean by the controlsource for the report.

Answer 21

This is not even going to work if I want to set it up based on multiple criteria is it?  If i want to do multiple criteria, am I going to need to use VBA?  I'm beginning to think it might be easier and more reliable.

Answer 22

Not sure what you mean by the controlsource for the report.

I said the ControlSource properties of the bound controls in the report, not of the report object itself, which doesn't have such a property.

If I want to do multiple criteria, am I going to need to use VBA?

Not necessarily; it depends what you mean by multiple criteria.  If you mean criteria on separate columns then you can use an expression with AND or OR operations, depending on whether want the criteria to be inclusive or not.  If you mean multiple values of the same column then it must of course be an OR operation.  However.......

I'm beginning to think it might be easier and more reliable.

I'd agree.

---

Ken Sheridan, Stafford, England

Answer 23

given general coding, how would i do a two combobox filter (combo10 and combo11) on a docmd.openreport?

Answer 24

• Proposed As Answer by Ken Sheridan<abbr class="affil"></abbr> Wednesday, March 14, 2012 11:23 AM

Just noticed you've marked this as an answer, Bruce.  I didn't realize I'd proposed it.  Must have been a slip of the mouse.

---

Ken Sheridan, Stafford, England

Answer 25

Just noticed that myself.  I was wondering what happen.

Answer 26

Would it be possible to alter the SQL for the record source using the combobox values?

variable1 = combo10
variable2 = combo11
variable3 = combo12
WHERE (((qryCurrentlyTasked.Tasked)=Yes AND tblalpharoster.unit = variable1 AND tblAlpharoster.band = variable2 AND qryCurrentlyTasked.Tasking = variable3));

Answer 27

Private Sub Report_Click()
Dim variable As String
Dim variable1 As String
Dim variable2 As String
Dim variable3 As String
Dim whereCond As String
variable = "qryCurrentlyTasked.Tasked=Yes"
If IsNull(Me.Unit) = False Then
    variable1 = " And tblAlpharosterkey.Unit=" & Me.Unit
End If
If IsNull(Me.Band) = False Then
    variable2 = " And tblAlpharosterkey.Band=" & Me.Band
End If
If IsNull(Me.Task) = False Then
    variable3 = " And qryCurrentlyTasked.ID=" & Me.Task
End If
MsgBox variable1 & variable2 & variable3
whereCond = variable & variable1 & variable2 & variable3
DoCmd.OpenReport "rptTaskingTrainingReport", acViewPreview, , whereCond
End Sub

Did this, and its prompting me for parameter value of band (which is set to "B4")

Answer 28

if i get rid of

If IsNull(Me.Band) = False Then
    variable2 = " And tblAlpharosterkey.Band=" & Me.Band
End If

It works fine... go figure.

Answer 29

Long as the data doesnt conflict, this works:

Private Sub Report_Click()
Dim variable As String
Dim variable1 As String
Dim variable2 As String
Dim variable3 As String
Dim whereCond As String
variable = "qryCurrentlyTasked.Tasked=Yes"
If IsNull(Me.Unit) = False Then
    variable1 = " And tblAlpharosterkey.Unit=" & Me.Unit
End If
If IsNull(Me.Band) = False Then
    variable2 = " And tblAlpharosterkey.Band=" & Chr$(34) & Me.Band & Chr$(34) & ""
End If
If IsNull(Me.Task) = False Then
    variable3 = " And qryCurrentlyTasked.ID=" & Me.Task
End If
MsgBox variable1 & variable2 & variable3
whereCond = variable & variable1 & variable2 & variable3
DoCmd.OpenReport "rptTaskingTrainingReport", acViewPreview, , whereCond
End Sub

Answer 30

Long as the data doesnt conflict, this works

In what way might the data conflict? 

---

Ken Sheridan, Stafford, England