Query not saved

[so10070]

I have a procedure to make a query but in runtime I get error 3078 ("The Microsoft Access database engine cannot find the input table or query 'name'. Make sure it exists and that its name is spelled correctly."). The query isn't displayed left in the list of the queries (list of all the objects). But when I restart my application the query is in the list! How can I get this query in the list at runtime.

  [FONT=&quot]Private Sub Report_Open(Cancel As Integer)[/FONT]
  [FONT=&quot]    Dim sqlRecordSourceReportPD As String[/FONT]
  [FONT=&quot]    Dim sqlBronPD As String[/FONT]
  [FONT=&quot]    Dim qdfPD As Object[/FONT]
  
  [FONT=&quot]    'Delete previous query[/FONT]
  [FONT=&quot]    If ObjectBestaatNog("BronTabelPD", 2) = True Then[/FONT]
  [FONT=&quot]        DoCmd.DeleteObject acQuery, "BronTabelPD"[/FONT]
  [FONT=&quot]    End If[/FONT]
  
  [FONT=&quot]    sqlBronPD = "SELECT tblWerkgroepCGS.WerkgroepCGSID, tblWerkgroepCGS.WGAfdelingDivisie, tblProducten.*, tblAandachtspunten.* " & _[/FONT]
  [FONT=&quot]        "FROM tblWerkgroepCGS INNER JOIN (tblProducten LEFT JOIN tblAandachtspunten ON tblProducten.ProductenID = tblAandachtspunten.ProductenID) " & _[/FONT]
  [FONT=&quot]        "ON tblWerkgroepCGS.WerkgroepCGSID = tblProducten.WerkgroepCGSID " & _[/FONT]
  [FONT=&quot]        "WHERE (tblWerkgroepCGS.WerkgroepCGSID <> " & Me.Parent.txtWerkgroepCGSID & ") And " & _[/FONT]
  [FONT=&quot]        "(tblProducten." & Me.Parent.txtWGAfdelingDivisiePD & " = True) And (tblProducten.PKalenderjaar = " & TempVars.Item("PubKalenderjaar") & ");"[/FONT]
  
  [FONT=&quot]    Set qdfPD = CurrentDb.CreateQueryDef("BronTabelPD")[/FONT]
  [FONT=&quot]    qdfPD.SQL = sqlBronPD[/FONT]
  [FONT=&quot]'    DoCmd.OpenQuery "BronTabelPD"[/FONT]
  [FONT=&quot]'    DoCmd.Save acQuery, "BronTabelPD"[/FONT]
  
  [FONT=&quot]    Set qdfPD = Nothing[/FONT]
  
  [FONT=&quot]    'Count total records  >> [B][COLOR=Blue]Error 3078 (Can't find query)[/COLOR][/B][/FONT]
  [FONT=&quot]    TempVars.Add "TotaalRecordsPD", Nz(DCount("*", "[U][B][COLOR=Blue]BronTabelPD[/COLOR][/B][/U]"), 0)[/FONT]
  
  [FONT=&quot]    sqlRecordSourceReportPD = "SELECT tblWerkgroepCGS.WerkgroepCGSID, tblWerkgroepCGS.WGAfdelingDivisie, tblProducten.*, tblAandachtspunten.* " & _[/FONT]
  [FONT=&quot]        "FROM tblWerkgroepCGS INNER JOIN (tblProducten LEFT JOIN tblAandachtspunten ON tblProducten.ProductenID = tblAandachtspunten.ProductenID) " & _[/FONT]
  [FONT=&quot]        "ON tblWerkgroepCGS.WerkgroepCGSID = tblProducten.WerkgroepCGSID " & _[/FONT]
  [FONT=&quot]        "WHERE (tblWerkgroepCGS.WerkgroepCGSID <> " & Me.Parent.txtWerkgroepCGSID & ") And " & _[/FONT]
  [FONT=&quot]        "(tblProducten." & Me.Parent.txtWGAfdelingDivisiePD & " = True) And (tblProducten.PKalenderjaar = " & TempVars.Item("PubKalenderjaar") & ") " & _[/FONT]
  [FONT=&quot]        "ORDER BY tblWerkgroepCGS.WGAfdelingDivisie, tblProducten.PCodeActie, tblProducten.PNaam;"[/FONT]
  
  [FONT=&quot]    Me.RecordSource = sqlRecordSourceReportPD[/FONT]
  [FONT=&quot]End Sub[/FONT]

 

[JHB]

Try the Application.RefreshDatabaseWindow.

[FONT=&quot]Set qdfPD = CurrentDb.CreateQueryDef("BronTabelPD")[/FONT]
[FONT=&quot]qdfPD.SQL = sqlBronPD[/FONT]
Application.[COLOR=Black]RefreshDatabaseWindow[/COLOR]

 

[MarkK]

Or you might have to refresh the QueryDefs collection, like...

CurrentDb.QueryDefs.Refresh

...but I would be tempted to just use a temp QueryDef so you side-step the whole issue of saving the query in the first place, or having to delete the previously created copy, etc.... Consider code like...

Private Sub Report_Open(Cancel As Integer)
    Dim sql As String
  
    sql = "SELECT tblWerkgroepCGS.WerkgroepCGSID, tblWerkgroepCGS.WGAfdelingDivisie, tblProducten.*, tblAandachtspunten.* " & _
        "FROM tblWerkgroepCGS INNER JOIN (tblProducten LEFT JOIN tblAandachtspunten ON tblProducten.ProductenID = tblAandachtspunten.ProductenID) " & _
        "ON tblWerkgroepCGS.WerkgroepCGSID = tblProducten.WerkgroepCGSID " & _
        "WHERE (tblWerkgroepCGS.WerkgroepCGSID <> " & Me.Parent.txtWerkgroepCGSID & ") And " & _
        "(tblProducten." & Me.Parent.txtWGAfdelingDivisiePD & " = True) And (tblProducten.PKalenderjaar = " & TempVars.item("PubKalenderjaar") & ") " & _
        "ORDER BY tblWerkgroepCGS.WGAfdelingDivisie, tblProducten.PCodeActie, tblProducten.PNaam;"
  
    With CurrentDb.CreateQueryDef("", sql)
        With .OpenRecordset
            If Not .EOF Then .MoveLast                      'populate the recordset
            TempVars.Add "TotaalRecordsPD", .RecordCount    'save the recordcount
            .Close
        End With
        Me.RecordSource = .sql
        .Close
    End With
    
End Sub

See what happens there? We create a temp instance of the sorted query, which will have the same record count anyway, use it to provide the count to the TempVar, and use it again to provide the SQL to the report's RecordSource.

No need to ensure it is deleted, no need to create it or find it back.

hth
Mark

 

[MarkK]

Just to clarify: to make a temp QueryDef, we simply do not provide a name when we create it...

const SQL as string = "SELECT Count(*) FROM Table"

with currentdb.createquerydef("", sql)[COLOR="Green"]   'note here we do not provide a 'name'[/COLOR]
   with .openrecordset
      msgbox "There are " & .fields(0) & " records in 'Table'"
      .close
   end with
   .close
end with

In this case the query only ever exists in code, and is never saved.

 

[so10070]

Thanks very much! It works perfectly.
I have still one question: how can I make a sum of a particular field. I don't know where to refer to for the "domain".

TotalFieldsPD = Nz(DSum(Replace(Me.Parent.txtWGAfdelingDivisiePD, "P", "A", 1, 1),[SIZE=5] [U][B][COLOR=Red]""[/COLOR][/B][/U][/SIZE]), 0)

 

[MarkK]

DSum() sums a field value in a table or query. Syntax is...

DSum(<field name>, <table or query name>, <optional criteria>)

 

[so10070]

I didn't explain me very well I think. My question is: what is the name of the domain in this specific dsum-function (my example). It is a temporary query, what kind of name do I put in the domain. Is it "sql", the string-name of the sql, or ???? But "sql " doesn't work.
Thanks in advance!

 

[MarkK]

Do you mean you want to DSum() from the temp query we created earlier? I don't think that will work. I think you need a saved query or table to use DSum(). But you can write another temp querydef to use the SQL we already constructed, like...

Private Sub Report_Open(Cancel As Integer)
    Dim sql As String
    Dim fld As String
  
    sql = "SELECT tblWerkgroepCGS.WerkgroepCGSID, tblWerkgroepCGS.WGAfdelingDivisie, tblProducten.*, tblAandachtspunten.* " & _
        "FROM tblWerkgroepCGS INNER JOIN (tblProducten LEFT JOIN tblAandachtspunten ON tblProducten.ProductenID = tblAandachtspunten.ProductenID) " & _
        "ON tblWerkgroepCGS.WerkgroepCGSID = tblProducten.WerkgroepCGSID " & _
        "WHERE (tblWerkgroepCGS.WerkgroepCGSID <> " & Me.Parent.txtWerkgroepCGSID & ") And " & _
        "(tblProducten." & Me.Parent.txtWGAfdelingDivisiePD & " = True) And (tblProducten.PKalenderjaar = " & TempVars.item("PubKalenderjaar") & ") " & _
        "ORDER BY tblWerkgroepCGS.WGAfdelingDivisie, tblProducten.PCodeActie, tblProducten.PNaam "
  
    With CurrentDb.CreateQueryDef("", sql)
        With .OpenRecordset
            If Not .EOF Then .MoveLast                      'populate the recordset
            TempVars.Add "TotaalRecordsPD", .RecordCount    'save the recordcount
            .Close
        End With
        Me.RecordSource = .sql
        .Close
    End With
    
    fld = Replace(Me.Parent.txtWGAfdelingDivisiePD, "P", "A", 1, 1)
    sql = _
        "SELECT Sum(q." & fld & ") " & _
        "FROM ( " & sql & " ) As q "
    With CurrentDb.CreateQueryDef("", sql)
        With .OpenRecordset
            TotalFieldsPD = .Fields(0).Value
            .Close
        End With
        .Close
    End With

End Sub

See what happens there? We use the first sql as a subquery in the second sql, which sums the field using SQL.Sum() rather than Access.DSum(). Also note that we must remove the semi-colon at the end of the first sql or it will prematurely terminate our second sql. Otherwise the first part of the code is unchanged.

Is that what you mean to do?

hth
Mark

 

[Galaxiom]

DSum() sums a field value in a table or query. Syntax is...

DSum(<field name>, <table or query name>, <optional criteria>)

That is not precisely accurate. It is a common misunderstanding that sometimes leads to code being more complex than it need be.

The first argument is actually an expression. While that expression is often a fieldname, it can also incorporate multiple fieldnames and operators.

eg This might be used for an invoice total.

DSum("[price]*[quantity]","[invoices]", "[invoiceID] =" & whatever)

 

[so10070]

Mark, it works perfectly! Thanks for the help. I've learned a lot in a few days!

Bewaren​