query by form gives error OBJECT MISSING

Misiek

Registered User.
Local time
Today, 04:05
Joined
Sep 10, 2014
Messages
248
Hello,
The following code supposed to let me print all records OR only those where dAreaFK = myCBO

currently I get an error message "Object missing"
if i remove this:

Or Me!cboStatsArea Is Null

from the last line the it works but only if i make selection in combo.

Code:
Private Sub cmdPrintOpen_Click()
'Print open defects using R_Open_details

Dim i As Integer
i = DCount("*", "Q_Open_details", "dAreaFK=cboStatsArea OR cboStatsArea IS Null")
'MsgBox "The count of rows is " & i
If i = 0 Then
MsgBox "No Records available for print", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "R_Open_details", acPreview, , _
"dAreaFK=" & Me!cboStatsArea Or Me!cboStatsArea Is Null
End Sub


Can you advice please
Thank you
 
Well, it's mainly because that text is outside the string, but I'd go about it differently. I'd test for the combo containing something, and only include the wherecondition argument if it does. I'd also use the No Data event of the report to handle the empty report, but that's me.
 
Try the below, (the Is Null is used in a query, not in VBA, there is IsNull(...) used):
Code:
  If IsNull(Me!cboStatsArea) Then
    DoCmd.OpenReport "R_Open_details", acPreview
  Else
    DoCmd.OpenReport "R_Open_details", acPreview, , _
    "dAreaFK=" & Me!cboStatsArea 
  End If
 
used this:
Code:
Private Sub cmdPrintOpen_Click()
Dim i As Integer
i = DCount("*", "Q_Open_defects", "dAreaFK=cboStatsArea OR cboStatsArea IS Null")
'MsgBox "The count of rows is " & i
If i = 0 Then
MsgBox "No Records available for print", _
vbOKOnly, "Error"
Exit Sub
End If
If cboStatsArea <> 0 Then
DoCmd.OpenReport "R_Open_defects", acPreview, , _
"dAreaFK=" & Me!cboStatsArea
Else
DoCmd.OpenReport "R_Open_defects", acPreview
End If
End Sub
thank you works lovely.

But I have another one

I have records with completion date and records still need to be completed.
and this code:
I want it to automatically filter and only show one or the other.
Code:
If IsNull(Me!dPK) Then
MsgBox "No Records available for print", _
vbOKOnly, "Error"
Exit Sub
End If
If Len(Me.[dDateClosed] & "") > 0 Then
DoCmd.OpenReport "R_Open_defects", acPreview, "Q_Closed_defects"
Else
DoCmd.OpenReport "R_Open_defects", acPreview
End If
 
as below, had to add this:
me.filter and add all PK fields to the query.

Code:
If IsNull(Me!dPK) Then
MsgBox "No Records available for print", _
vbOKOnly, "Error"
Exit Sub
End If
If Len(Me.[dDateClosed] & "") > 0 Then
DoCmd.OpenReport "R_Open_defects", acPreview, , Me.Filter
Else
DoCmd.OpenReport "R_Open_defects", acPreview, , Me.Filter
End If
 
What is the purpose of the If/Then block, since you appear to do the same thing either way?
 
remainings of the old code (try if that works). removed now,
Thanks Paul
 

Users who are viewing this thread

Back
Top Bottom