Loop Without Do

[jonnymenthol]

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").Value = 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

 

[fuzzygeek]

You didn't list the message. Where are the end if's inside the loop...I didn't see them.

 

[WayneRyan]

jonny,

FuzzyGeek is right, basically, your code is doing this:

Do
   If Something
      Loop Until rst.EOF
   End If

Your Do/Loop statement should not include an incomplete If - Then statement.

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 <-- Level #1

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

      ' 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 <-- Level #3

         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").Value = rst!TermsAgreed

         ' Update the Record
         If oSalesRecord.Update Then <-- Level #4

            ' 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  <-- This statement should not be within an
                                    If - Then block, since the Do - Loop
                                    statement is outside of it.

           '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 <-- Level #4

     End If <-- Level #3

     ' Disconnect
     oWS.Disconnect

   End If <-- Level #2

End If <-- Level #1

' 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

Wayne

 

[modest]

Syntax for Do Loop

Do
  ..do stuff here..
Loop While (expression is true)

or if you want to evaluate the expression before you do the code

Do While (expression is true)
    ..do stuff here..
Loop

 

[jonnymenthol]

Thanks very much, I can see where I was going wrong now.

Cheers.

J.