Here is the code for that specific function:
Private Sub cmdReport_Click()
On Error GoTo cmdReport_Click_Err
Dim strFields As String
Dim strWhere As String
Dim strFrom As String
Dim strOrderBy As String
Dim strGroupBy As String
Dim booTotals As Boolean
Dim strQueryName As String
Dim qdUserQuery As DAO.QueryDef
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim I As Integer
Dim strSQL As String
Me![sfrmQBFFields].Form.Requery
Set rs = Me![sfrmQBFFields].Form.RecordsetClone
rs.MoveLast
I = rs.RecordCount
rs.MoveFirst
Dim strDelim As String
Set db = DBEngine.Workspaces(0).Databases(0)
strFrom = " From [" & Me![cboSource] & "]"
booTotals = Me.chkTotals = True
For I = 1 To I
If booTotals And rs("qbfTotal") & "" = "" Then
MsgBox "You must select an option in the Total " & _
"column for each field when using Totals.", _
vbInformation + vbOKOnly, "Select All Totals"
Exit Sub
End If
If Not IsNull(rs("QBFField")) Then
If booTotals = True Then
Select Case rs("qbfTotal")
Case "Sum", "Count", "Avg", "Min", "Max"
'rs("qbfTotal") should be one of the above aggregates
strFields = strFields & rs("qbfTotal") & "([" &
rs!QBFField & _
"]) as [" & rs("qbfTotal") & "_" & rs!QBFField &
"] , "
Case "Group By"
strGroupBy = strGroupBy & "[" & rs!QBFField & "], "
strFields = strFields & "[" & rs!QBFField & "], "
End Select
Else
strFields = strFields & "[" & rs!QBFField & "], "
End If
End If
If Not IsNull(rs!Operator) Then
strDelim = rs!Delimiter
strWhere = strWhere & "[" & rs!QBFField & "] " & rs!Operator
Select Case rs!Operator
Case "IN"
strWhere = strWhere & "( " & strDelim & rs!Value1 &
strDelim & ")" & " AND "
Case "Is Null"
strWhere = strWhere & " AND "
Case Else
strWhere = strWhere & strDelim & rs!Value1 & strDelim & "
AND "
End Select
If rs!Operator = "Between" Then
strWhere = strWhere & strDelim & rs!Value2 & strDelim & "
AND "
End If
End If
If Len(rs!SortOrder & "") > 0 Then
If rs!SortOrder <> "N" Then
strOrderBy = strOrderBy & "[" & rs!QBFField & "], "
End If
End If
rs.MoveNext
Next
If Len(strFields) > 2 Then
strFields = left$(strFields, Len(strFields) - 2)
End If
If Len(strWhere) > 5 Then
strWhere = " Where " & left$(strWhere, Len(strWhere) - 4)
End If
If Len(strOrderBy) > 0 Then
strOrderBy = " Order By " & left$(strOrderBy, Len(strOrderBy) - 2)
End If
If Len(strGroupBy) > 0 Then
strGroupBy = " Group By " & left$(strGroupBy, Len(strGroupBy) - 2)
strOrderBy = ""
End If
strSQL = "Select " & strFields & strFrom & strWhere & strGroupBy &
strOrderBy & " ;"
rs.Close
'MsgBox strSQL
strQueryName = "MyQuery"
Set qdUserQuery = db.CreateQueryDef(strQueryName, strSQL)
db.QueryDefs.Refresh
DoCmd.OpenForm "frmMyQueryResults", , , , , , strQueryName
cmdReport_Click_Exit:
On Error Resume Next
Exit Sub
cmdReport_Click_Err:
If Err = 3012 Then 'MyQuery wasn't delete from previous usage
db.QueryDefs.Delete "MyQuery"
Resume
Else
MsgBox Err & ": " & Error$, 16, "Error in cmdReport_Click"
Resume cmdReport_Click_Exit:
End If
'Place this just before Exit Sub (or Function)
On Error Resume Next
CurrentDb.QueryDefs("MyQuery").Delete
'Exit Sub, Exit Function, etc.
End Sub
Ron Hinds said:
message Why would it all of a sudden do that? Is there something wrong with
Access?
As I explained below, it was probably inadvertently not deleted due to a
system crash or an error of some sort in the routine that creates/deletes
the query. That is why I use the code snippet I gave you in all of my
routines where I create QueryDefs on the fly.
I
added the code but it didn't work. Now I'm getting this error message:
Method or data member not found:
*The expression may not result in the name of a macro, the name of a user
defined function, or [Event Procedure]
*There may have been an error evaluating the function, event, or macro
That's odd because the statement
On Error Resume Next
should cause no error messages! Please post the *entire* routine you are
using this in and I'll see if I can't figure out what is wrong.
:
It sounds like that is a query that is created on the fly in code and
deleted when no longer needed. Access probably crashed or was stopped
for
debugging, etc., before the query was deleted. You can delete it
manually
from the query tab of the database window and it should work again. In
order
to keep this problem from recurring, I would suggest modifying the code
so
that when the routine that uses this query exits it is automatically
deleted, like so:
'Place this just before Exit Sub (or Function)
On Error Resume Next
CurrentDb.QueryDefs("MyQuery").Delete
'Exit Sub, Exit Function, etc.
message I'm getting an error message all of a sudden using the DH QBF. It has
worked
all along but now for some reason it's erroring out. Can anyone help
with
this?
Run Time Error: 3012
Object 'MyQuery' already exists.
Nothing has been changed so I don't know why it's not working. I also
downloaded the orginal QBF to test it and the same thing happens with
that
one. I wonder if there is something wrong internally with Access.
