DBEngine

[charlee63]

I'm have an Object not found.
In tools I have checks on "Visual Basic For Applications", "Microsoft Access 15.0 Object Library", "OLE Automation", "Microsoft ActiveX Data Objects 6.1 Library", "Microsoft ActiveX Data Objects Recordset 6.0 Library" and "Microsoft Office 15.0 Access database engine Object Library"

I do have the query name correct I copied and paste the name.

Any Idea where I should look.

Function Junk()
    Dim Rep, Email_Bcc, Email_Body As String
    Dim Mail_Object, Mail_Single As Variant
'   Get Email Info
    Dim o As DAO.Recordset
    Dim MyVendor, MyEmail, MyAp1, MyVendor1, MyGL1, MyAp2, MyVendor2, MyGL2, MyAp3, MyVendor3, MyGL3, qrySQLItems
    Set o = CurrentDb.OpenRecordset("Vendor")
    MyVendor = o![Vendor Name]
    MyEmail = o!
    MyAp1 = o![Ap Numbers 1]
    MyVendor1 = o![Vendor Numbers 1]
    MyGL1 = o![GL 1]
    MyAp2 = o![Ap Numbers 2]
    MyVendor2 = o![Vendor Numbers 2]
    MyGL2 = o![GL 2]
    MyAp3 = o![Ap Numbers 3]
    MyVendor3 = o![Vendor Numbers 3]
    MyGL3 = o![GL 3]
    qrySQLItems = "SELECT TblTeradata.AP_Vendor, TblTeradata.Vendor, TblTeradata.GL, TblTeradata.AP_Vendor_Name, TblTeradata.Code, " _
    & "TblTeradata.[CA Crosscode], TblTeradata.[DU Crosscode], TblTeradata.[HG Crosscode], TblTeradata.[NE Crosscode], TblTeradata.[PW Crosscode], " _
    & "TblTeradata.[OL Crosscode], TblTeradata.Description, TblTeradata.Pack, TblTeradata.Size, TblTeradata.Buyer, TblTeradata.Dest, " _
    & "TblTeradata.Warehouse, TblTeradata.Dlt, TblTeradata.UPC_VNDR, TblTeradata.UPC_CASE, TblTeradata.UPC_ITM, TblTeradata.Vendor_Name, " _
    & "TblTeradata.Chain_Name FROM TblTeradata WHERE "
   
    If Not IsEmpty(AP1) Then
        qrySQLItems = qrySQLItems & "(((TblTeradata.AP_Vendor) In (" & MyAp1 & ")))"
        If Not IsEmpty(GL1) Then
            If Not IsEmpty(AP1) Then
                qrySQLItems = qrySQLItems & " OR "
            End If
            qrySQLItems = qrySQLItems & "(((TblTeradata.Vendor) In (" & MyGL1 & "))"
        End If
    End If
    If Not IsEmpty(MyVendor1) Then
        If Not IsEmpty(AP1) Then
            qrySQLItems = qrySQLItems & " OR "
        End If
        qrySQLItems = qrySQLItems & "(((TblTeradata.Vendor) In (" & MyVendor1 & "))"
        If Not IsEmpty(GL1) Then
            If Not IsEmpty(Vendor1) Then
                qrySQLItems = qrySQLItems & " OR "
            End If
            qrySQLItems = qrySQLItems & "(((TblTeradata.Vendor) In (" & MyGL1 & "))"
        End If
    End If
    qrySQLItems = qrySQLItems & " ORDER BY TblTeradata.AP_Vendor;"
   
    DBEngine(0)(0).QueryDefs("QryItem List").SQL = qrySQLItems
    DoCmd.OpenQuery "QryItem List"

End Function

 

[stumac]

is there a line highlighted when it errors? I would set a breakpoint and step through the code to see where there error is happening.

The line below is missing a field name but don't think that would give you an object not found error.

MyEmail = o!

the line below refere to a variable that you haven't declared:

If Not IsEmpty(Ap1) Then

 

[charlee63]

Don't know why that didn't copy but I do have this.
MyEmail = o!
The second error, I did catch that error after I sent the post.

Highlights This DBEngine(0)(0).QueryDefs("QryItem List").SQL = qrySQLItems

 

[stumac]

I assume the query is in the same database? try:

CurrentDb.QueryDefs("QryItem List").SQL = qrySQLItems

Edit - also check that you have a query called QryItem List (with the space)

 

[Micron]

DBEngine is how JET maintains a pointer in memory to a database - not necessarily the one you are working in. I suppose that can be said for ACE now, but I've not researched that. Point is, you are modifying a stored query sql property then expecting the static reference to reflect the changes. It cannot do so without you refreshing the queries collection and since you are not, anything that you're trying to make part of the sql property might not be resolved including a query itself (when it was just added to the collection). I do believe that if using CurrentDb you don't have to refresh the collection unless you add an item to it, but either will raise the error if mis-spelled. However you've indicated that is not the case. IF you happen to have 2 db's open at the same time, then maybe (0)(0) is not what you want.

You also have a ton of variant variables, some of which I suspect you are not aware of (such as Rep). Not that I think it has anything to do with your current issue (even though qrySQLItems is a variant) but it can cause issues that are hard to debug, plus 100 or so lines of code down you have to remember what each type is because of your lack of a naming convention.