Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2004
    Posts
    277

    Unanswered: I need help with looping

    I need to through every record in a query and change the money information to a negative.

    Each record's reference# has no set amount of numbers between them as they are based on shipments that occur every now and then.

    I am in the process of converting access tables into IIF files to be converted into Quickbooks.

    If anyone can point me in the right direction of a quick Loop tutorial that would be great help. Thank you in advance....

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You can use the following update query

    UPDATE MyTableName
    SET MyMoneyColumn = (0 - MyMoneyColumn)

    This will update the column to negatives in one go
    Justin

  3. #3
    Join Date
    Dec 2004
    Posts
    277
    ok next part though it now has to pull the info from one table into the next table putting each field in the right place record by record

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Ok instead you use an INSERT INTO statement along the lines of:

    INSERT INTO MyNewTable(Field_1, Field_2, MyNewMoneyColumn)
    SELECT Field_1,Field_2,0 - MyMoneyColumn FROM MyTableName

    Does the same thing as the update but on the new table instead

  5. #5
    Join Date
    Dec 2004
    Posts
    277
    are you familiar with IIF files?

    it's a tab delimited file(?) that QuickBooks understands to do the money.

    QuickBooks does not offer an easy solution to importing information, so I am kind of forcing access tables to be manipulated into the format the IIF file needs

    So for each record I need it to
    Me.txtA = SPL
    Me.txtB = INVOICE
    Me.txtC = Date()
    Me.txtD = AccountsReceivable
    Me.txtE = [AccountName]
    ....... and so on

    i'd show you a screen shot but I do not have one available

    I just really need to know the basics of how a Loop works and I should be able to figure it out from there

  6. #6
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    I have used quick books and the iif format is not easy to work with. To loop through a recordset you should use ADO. The basic syntax would be:

    ' Open the recordset here - see Help for the full ADO recordset syntax

    Do Until rst.EOF
    ' Do something with the current record
    rst.MoveNext
    Loop

    You can also use the Scripting.FileSystemObject (you will need a reference to Windows Scripting to get this to work) to write to a text file record by record in the exact format you need. Or you could just INSERT INTO a new table each record. Any action carried out on a record by record basis will be slower then using an update/insert command
    Justin

  7. #7
    Join Date
    Dec 2004
    Posts
    277
    what do I set rst to equal?
    it is saying Object Required

  8. #8
    Join Date
    Dec 2004
    Posts
    277
    bump::HELP!!!!!

  9. #9
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You need to have code at the start along these lines:

    Dim rst As ADODB.Recordset
    Dim strSQL as String, strTextFile as String
    ' Register the recordset object
    Set rst = New ADODB.Recordset
    strSQl = "SELECT * FROM MyTable"
    ' You will need to read the Help file to find out what all these properties are so that you can see if they are relevant to your situation or not
    With rst
    .ActiveConnection = CurrentProject.Connection.ConnectionString
    .CursorLocation = adUseClient
    .CursorType = adoCursorType
    .LockType = adoLockType
    .MaxRecords = 0
    .Open strSQL
    End With
    ' loop through the recordset one record at a time
    Do Until rst.EOF
    Me.txtA = rst.Fields("MyFieldName").Value
    ' or you can use this if you are trying to write to a text file
    strTextFile = rst.Fields("MyFieldName_1").Value & vbCrLf & rst.Fields("MyFieldName_2").Value & vbCrLf ... etc
    rst.MoveNext
    Loop
    ' Destroy the object when you have finished with it
    Set rst = Nothing

Posting Permissions

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