Query SQL keeps getting erased

  • Thread starter Thread starter Jeremy Ames
  • Start date Start date
J

Jeremy Ames

I have several queries that I run multiple times from some VBA code. I run
the same query multiple times. On the majority of my queries, they will run
the first time and every time after that, I receive a message that says
"Query must have at least one destination field." When I look at the SQL in
the query, it is all gone. I have searched all over Technet and I haven't
been able to find anything on this problem. Can someone please help?

p.s. I am sorry for posting this twice. I figured the queries forum would
get this answered faster, but I still have not received any response. I need
an answer to this pretty quickly as I have a client that is waiting on it.
 
That does appear to be the problem.

Here is the code ( it is the entire function so it is little bit to look at)
:

***************************************************
Begining of Code
***************************************************
Public Sub AdminExport(sPath As String, sQueryName As String, bEmail As
Boolean)
Dim cnConn As ADODB.Connection
Dim rsUsers As ADODB.Recordset
Dim sSql As String, sFile As String
Dim lPrgCnt As Long

Set cnConn = CurrentProject.Connection
Set rsUsers = New ADODB.Recordset

sSql = "SELECT AgentId FROM tblUser ORDER BY AgentId"

rsUsers.Open sSql, cnConn, adOpenStatic, adLockOptimistic

DoCmd.OpenForm "frmExporting"
Form_frmExporting.prgExport.Max = rsUsers.RecordCount

On Error GoTo ErrorHandler

Do Until rsUsers.EOF
' handle exporting functions
msAgentId = rsUsers(0)
Form_frmAdmin.txtAgentId = msAgentId
sFile = msAgentId & " - " & sQueryName & ".xls"

ExportFile sPath, sFile, sQueryName

' handle email functions
If bEmail Then
If Not IsNull(DLookup("EmailAddress", "tblUser", "AgentId = '" &
msAgentId & "'")) Then
EmailReport DLookup("EmailAddress", "tblUser", "AgentId = '"
& msAgentId & "'"), _
sPath & sFile, _
Form_frmAdmin.lstReports.Column(1)
Else
InsertExportError "User does not have email.", msAgentId, _
Form_frmAdmin.lstReports.Column(1)
mlErrCnt = mlErrCnt + 1
End If
End If
ResumePoint:
lPrgCnt = lPrgCnt + 1
Form_frmExporting.prgExport = lPrgCnt

rsUsers.MoveNext
Loop

rsUsers.Close
cnConn.Close

Set rsUsers = Nothing
Set cnConn = Nothing

If mlErrCnt > 0 Then Form_frmAdmin.cmdViewErrors.Visible = True

DoCmd.Close

Exit Sub

ErrorHandler:
mlErrCnt = mlErrCnt + 1
' report error to error table
InsertExportError Err.Description, msAgentId,
Form_frmAdmin.lstReports.Column(1)
GoTo ResumePoint
End Sub
**************************************************
End of Code
**************************************************

You are presumably zapping the query's SQL property.

TC
(off for the day)
 
Back
Top