Re: Query SQL keeps getting erased

From: Jeremy Ames (yourname_at_here.com)
Date: 04/28/04 

Date: Wed, 28 Apr 2004 08:12:49 -0500

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
**************************************************

"TC" <no@email.here> wrote in message
news:408f45cc$1_1@news.chariot.net.au...
You are presumably zapping the query's SQL property.

TC
(off for the day)

"TC" <no@email.here> wrote in message
news:408f4072$1_3@news.chariot.net.au...
> Uh: show us the code?
>
> TC
>
>
> "Jeremy Ames" <yourname@here.com> wrote in message
> news:uHGGtiILEHA.3324@TK2MSFTNGP10.phx.gbl...
> > 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.
> >
> >
>
>

Relevant Pages

  • RE: Find And Replace Text String VBA
    ... > text string? ... > 123 SAINT JOSEPH CT. ... but I have to run it multiple times to replace all the ... > could I run the query multiple times, please give an example of some code? ...
    (microsoft.public.access.modulesdaovba)
  • RE: Delete Rows based on # of rows (no variable?)
    ... Dim a As String ... End Sub ... run this same sequence of steps multiple times, but I can figure that out as ...
    (microsoft.public.excel.programming)
  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)
  • Auto Write Name and Merge across
    ... Dim Sheetname01 As String ... Dim WeekName01 As String ...
    (microsoft.public.excel.misc)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
Quantcast