Error 7874 MS Access can't find object (1 Viewer)

bconner

Registered User.
Local time
Today, 03:39
Joined
Dec 22, 2008
Messages
183
Hi All,
I am trying to run the below code but I am getting the MS Access can't find object on this line
Code:
DoCmd.OpenQuery "SELECT * FROM PROV_CL WHERE [Address 1] LIKE '*" & strInputBox & "*'", acViewNormal
from the code below. I just can't seem to figure out what I am doing wrong. Any help is appreciated..... thanks


Code:
Private Sub cmdPreview_Click()
Dim strInputBox As String
 
Select Case cmbReports
  Case "Darin Bad Address"
  DoCmd.OpenQuery "Rpt_Darin_Bad_Address", acViewNormal
 
  Case "MMDM Bad Address"
  DoCmd.OpenQuery "Rpt_MMDM_Bad_Address", acViewNormal
 
  Case "Lookup Darin Address"
  strInputBox = InputBox("Please Enter an Address or Partial Address for lookup", "Address Lookup")
  DoCmd.OpenQuery "SELECT * FROM PROV_CL WHERE [Address 1] LIKE '*" & strInputBox & "*'", acViewNormal
 
 
 
 
 End Select
 
End Sub
 

pr2-eugin

Super Moderator
Local time
Today, 08:39
Joined
Nov 30, 2011
Messages
8,494
DoCmd.OpenQuery method takes in the Name of the pre saved/compiled query, you cannot pass the SQL into that.
 

bconner

Registered User.
Local time
Today, 03:39
Joined
Dec 22, 2008
Messages
183
Thanks for the tip... I have changed the syntax to the below

This time it goes thru the code without error but nothing happens (Query doesn't open or anything). Any idea what I am missing or doing wrong?


Code:
Private Sub cmdPreview_Click()
Dim strInputBox As String

Select Case cmbReports
  Case "Darin Bad Address"
  DoCmd.OpenQuery "Rpt_Darin_Bad_Address", acViewNormal
  
  Case "MMDM Bad Address"
  DoCmd.OpenQuery "Rpt_MMDM_Bad_Address", acViewNormal
   
  Case "Lookup Darin Address"
  strInputBox = InputBox("Please Enter an Address or Partial Address for lookup", "Address Lookup")
  'DoCmd.OpenQuery "SELECT * FROM PROV_CL WHERE [Address 1] LIKE '*" & strInputBox & "*'""", acViewNormal
  strSQL = "SELECT * FROM PROV_CL WHERE [Address 1] LIKE '*" & strInputBox & "*'"
  CurrentDb.OpenRecordset strSQL
  
    
 End Select
 
End Sub
 

bconner

Registered User.
Local time
Today, 03:39
Joined
Dec 22, 2008
Messages
183
Ok, I figured it out (thanks to your tip)
The below worked....

I just created a query object named "Qry_Adhoc" and changed the definition and it worked perfecctly.....

Thanks again...


Code:
Private Sub cmdPreview_Click()
Dim strInputBox As String

Select Case cmbReports
  Case "Darin Bad Address"
  DoCmd.OpenQuery "Rpt_Darin_Bad_Address", acViewNormal
  
  Case "MMDM Bad Address"
  DoCmd.OpenQuery "Rpt_MMDM_Bad_Address", acViewNormal
   
  Case "Lookup Darin Address"
  strInputBox = InputBox("Please Enter an Address or Partial Address for lookup", "Address Lookup")
  strSQL = "SELECT * FROM PROV_CL WHERE [Address 1] LIKE '*" & strInputBox & "*'"
  CurrentDb.QueryDefs("Qry_Adhoc").SQL = strSQL
  DoCmd.OpenQuery "Qry_Adhoc", acViewNormal
    
 End Select
 
End Sub
 

Users who are viewing this thread

Top Bottom