Results 1 to 4 of 4

Thread: Loop Without Do

  1. #1
    Join Date
    Jun 2004
    Posts
    26

    Unanswered: Loop Without Do

    I'm getting the following message when I try to run one of my routines (it imports names and addresssed from a table into my program)

    I definitely have the DO Statement, so cannot see where I am going wrong.

    Does anyone have any ideas ?

    Thanks.

    J.

    My code is here :

    Option Compare Database

    Private Sub cmdCreateNewCustomers_Click()

    ' Error Handler
    On Error GoTo Error_Handler

    ' Declare Objects
    Dim oSDO As SageDataObject110.SDOEngine
    Dim oWS As SageDataObject110.WorkSpace
    Dim oSalesRecord As SageDataObject110.SalesRecord

    ' Declare Variables
    Dim szDataPath As String

    ' Create the SDOEngine Object
    Set oSDO = New SageDataObject110.SDOEngine

    ' Create the Workspace
    Set oWS = oSDO.Workspaces.Add("Example")

    ' Select Company. The SelectCompany method takes the program install
    ' folder as a parameter
    szDataPath = oSDO.SelectCompany("C:\Program Files\Sage\V1101")

    ' A U.I. for company selection is presented to the user. If a company is selected,
    ' the path will be passed to the szDataPath variable.
    ' If not, or the Cancel button is selected, the variable will be left empty.
    If szDataPath <> "" Then

    ' Try to Connect - Will Throw an Exception if it Fails
    If oWS.Connect(szDataPath, "MANAGER", "", "Example") Then

    ' Create an Instance of the SalesRecord Object
    Set oSalesRecord = oWS.CreateObject("SalesRecord")

    'Declare Database and Recordset
    Dim dbs As Database
    Dim rst As Recordset

    'Set Database to Current Database
    Set dbs = CurrentDb()

    'Set Recordset to look at the Customers Table
    Set rst = dbs.OpenRecordset("Select * from tblcustomers")

    'Move to the First Record in the Customers Table
    rst.MoveFirst

    Do

    ' Try to Add a New Record based on First Record
    If oSalesRecord.AddNew Then

    oSalesRecord.Fields.Item("ACCOUNT_REF").Value = rst!Account
    oSalesRecord.Fields.Item("NAME").Value = rst!Name
    oSalesRecord.Fields.Item("ADDRESS_1").Value = rst!Address1
    oSalesRecord.Fields.Item("ADDRESS_2").Value = rst!Address2
    oSalesRecord.Fields.Item("ADDRESS_3").Value = rst!Address3
    oSalesRecord.Fields.Item("ADDRESS_4").Value = rst!Address4
    oSalesRecord.Fields.Item("ADDRESS_5").Value = rst!Address5
    oSalesRecord.Fields.Item("CONTACT_NAME").Value = rst!Contact
    oSalesRecord.Fields.Item("TELEPHONE") = rst!Telephone
    oSalesRecord.Fields.Item("FAX").Value = rst!Fax
    oSalesRecord.Fields.Item("TERMS").Value = rst!Terms
    oSalesRecord.Fields.Item("DEF_NOM_CODE").Value = rst!DefaultNominalCode
    oSalesRecord.Fields.Item("VAT_REG_NUMBER").Value = rst!VatRegNumber
    oSalesRecord.Fields.Item("COUNTRY_CODE").Value = rst!CountryCode
    oSalesRecord.Fields.Item("TERMS_AGREED_FLAG").Valu e = rst!TermsAgreed

    ' Update the Record
    If oSalesRecord.Update Then

    ' The Update was Successful
    MsgBox "Account " & oSalesRecord.Fields.Item("ACCOUNT_REF").Value & " was created successfully.", vbInformation

    'Move onto the next Record in the Customers Table
    rst.MoveNext

    'Repeat for each record in the Customers Table until the last one

    Loop Until rst.EOF

    'Close Customers Table
    rst.Close

    'Set Customers Table to Nothing
    Set rst = Nothing

    Else

    ' The Update was Unsuccessful
    MsgBox "The account could not be created.", vbCritical

    End If

    End If

    ' Disconnect
    oWS.Disconnect

    End If

    End If

    ' Destroy Objects
    Set oSalesRecord = Nothing
    Set oWS = Nothing
    Set oSDO = Nothing

    Exit Sub

    ' Error Handling Code
    Error_Handler:

    MsgBox "The SDO generated the following error: " & oSDO.LastError.Text


    End Sub

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You've got your Loop enclosed in a If..Then.. Else

    That trick never works!
    Inspiration Through Fermentation

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here is your flow control stuff without the guff

    Code:
    If szDataPath <> "" Then
      If oWS.Connect(szDataPath, "MANAGER", "", "Example") Then
        Do
          If oSalesRecord.AddNew Then
            If oSalesRecord.Update Then
            missing endif
          missing endif
        Loop Until rst.EOF
          Else
          End If 'unwanted endif
        End If 'unwanted endif
      End If
    End If
    ?? izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jun 2004
    Posts
    26
    Thanks very much, I can see where I was going wrong now.

    Cheers.

    J.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •