stLinkCriteria error in Access 2007

[Randy]

Does anyone have any idea why this code will work in Access 2003 and NOT
Access 2007?
The database is split (if that matters); and the error I get is "The
Microsoft Office Access database engine could not find the object '0'. Make
sure the object exists and that you spell its name and the path name
correctly."


Private Sub Ok_Click()
On Error GoTo Ok_Click_Err
'using the customer sub form for customer state report to filter the
report, clicking ok will open report for selected state
Dim stDocName As String
Dim stLinkCriteria As String

If Not IsNull(Me.Search_Results) Then
stLinkCriteria = "[StateOrProvince] = """ & Me![Search Results] & """"
End If

DoCmd.Close acForm, "Customers Sub Form For Customer State Report"

DoCmd.OpenReport "Customers by State", acPreview, acNormal,
WhereCondition = stLinkCriteria

Ok_Click_Exit:
Exit Sub

Ok_Click_Err:
MsgBox Err.Description
Resume Ok_Click_Exit

End Sub

 

[fredg]

Does anyone have any idea why this code will work in Access 2003 and NOT
Access 2007?
The database is split (if that matters); and the error I get is "The
Microsoft Office Access database engine could not find the object '0'. Make
sure the object exists and that you spell its name and the path name
correctly."

Private Sub Ok_Click()
On Error GoTo Ok_Click_Err
'using the customer sub form for customer state report to filter the
report, clicking ok will open report for selected state
Dim stDocName As String
Dim stLinkCriteria As String

If Not IsNull(Me.Search_Results) Then
stLinkCriteria = "[StateOrProvince] = """ & Me![Search Results] & """"
End If

DoCmd.Close acForm, "Customers Sub Form For Customer State Report"

DoCmd.OpenReport "Customers by State", acPreview, acNormal,
WhereCondition = stLinkCriteria

Ok_Click_Exit:
Exit Sub

Ok_Click_Err:
MsgBox Err.Description
Resume Ok_Click_Exit

End Sub

Your arguments are not correct.
You placed acNormal in the Filter argument position. Why?
acNormal is an Access constant whose value is 0.
You syntax has Access looking for a query filter named 0.
You don't need a Filter argument.
Your stLinkCritera belongs in the where clause argument position.

Try it this way.

DoCmd.OpenReport "Customers by State", acPreview, , stLinkCriteria
DoCmd.Close acForm, "Customers Sub Form For Customer State Report"

Note: If the name of the form this code is on is "Customers Sub Form
For Customer State Report", all you need, to close this form is:

DoCmd.OpenReport "Customers by State", acPreview, , stLinkCriteria
DoCmd.Close acForm, Me.Name

 

[Douglas J. Steele]

Looks like you're trying to use a named parameter in your OpenForm method.
To do that, you need to use :=, not just =.

DoCmd.OpenReport "Customers by State", acPreview, acNormal, _
WhereCondition := stLinkCriteria

 

[Randy]

Does anyone have any idea why this code will work in Access 2003 and NOT
Access 2007?
The database is split (if that matters); and the error I get is "The
Microsoft Office Access database engine could not find the object '0'.
Make
sure the object exists and that you spell its name and the path name
correctly."

Private Sub Ok_Click()
On Error GoTo Ok_Click_Err
'using the customer sub form for customer state report to filter the
report, clicking ok will open report for selected state
Dim stDocName As String
Dim stLinkCriteria As String

If Not IsNull(Me.Search_Results) Then
stLinkCriteria = "[StateOrProvince] = """ & Me![Search Results] &
""""
End If

DoCmd.Close acForm, "Customers Sub Form For Customer State Report"

DoCmd.OpenReport "Customers by State", acPreview, acNormal,
WhereCondition = stLinkCriteria

Ok_Click_Exit:
Exit Sub

Ok_Click_Err:
MsgBox Err.Description
Resume Ok_Click_Exit

End Sub

Your arguments are not correct.
You placed acNormal in the Filter argument position. Why?
acNormal is an Access constant whose value is 0.
You syntax has Access looking for a query filter named 0.
You don't need a Filter argument.
Your stLinkCritera belongs in the where clause argument position.

Try it this way.

DoCmd.OpenReport "Customers by State", acPreview, , stLinkCriteria
DoCmd.Close acForm, "Customers Sub Form For Customer State Report"

Note: If the name of the form this code is on is "Customers Sub Form
For Customer State Report", all you need, to close this form is:

DoCmd.OpenReport "Customers by State", acPreview, , stLinkCriteria
DoCmd.Close acForm, Me.Name

Sweet! Thanks Fred, this is what I needed!!!

As to why the code was like it was, got me; I just threw it together from
reading posts. Like I said, it works fine in the older Access version, so
there was no need to modify it in the past.

 

[Randy]

Looks like you're trying to use a named parameter in your OpenForm method.
To do that, you need to use :=, not just =.

DoCmd.OpenReport "Customers by State", acPreview, acNormal, _
WhereCondition := stLinkCriteria


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Private Sub Ok_Click()
On Error GoTo Ok_Click_Err
'using the customer sub form for customer state report to filter the
report, clicking ok will open report for selected state
Dim stDocName As String
Dim stLinkCriteria As String

If Not IsNull(Me.Search_Results) Then
stLinkCriteria = "[StateOrProvince] = """ & Me![Search Results] & """"
End If

DoCmd.Close acForm, "Customers Sub Form For Customer State Report"

DoCmd.OpenReport "Customers by State", acPreview, acNormal,
WhereCondition = stLinkCriteria

Ok_Click_Exit:
Exit Sub

Ok_Click_Err:
MsgBox Err.Description
Resume Ok_Click_Exit

End Sub

Thanks for the reply Doug, I actually did try your recommended change based
off of another button I let the Access Wizard create; but it didn't work
either. Fred's suggestion in the above post did work for me. I believed I
had the issue narrowed down to the "WhereCondition" statement was but just
didn't know how to fix it. I guess I'm still wondering why it didn't work...