Results 1 to 3 of 3

Thread: Loop Problem

  1. #1
    Join Date
    Feb 2006
    Posts
    59

    Arrow Unanswered: Loop Problem

    i have two tables . table A has the data and a column name tradeno which is blank. Second table B is the sequence no table it only has one number field starting from 0 . now table A has almost 100 records and tradeno field is left blank intentionally i want to update this tradeno with the value of table B sequence no i have this query which adds +1 to the sequence no . i have this loop but this is not updating

    Function trd()
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset

    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("close_trade_import_data_1") 'THIS IS TABLE A

    rs1.MoveFirst
    Do Until rs1.EOF
    DoCmd.OpenQuery "upt_trd_seq" ' THIS IS QUERY WHICH ADDS +1 TO SEQ NO

    'DoCmd.OpenQuery "update_trade_seq_to_import_tbl" THIS QUERY WILL ADD THE SEQUENCE NO TO TABLEA.TRADENO .
    rs1.MoveNext
    Loop

    rs1.Close
    Set rs = Nothing
    Set db = Nothing

    End Function


    Instead of putting table name is rs1 i also tried to put "update_trade_seq_to_import_tbl" this query but it says invalid operation
    Hope you understand
    regards

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I don't understand why you don't have tradeno update automatically. All you need to do is to identify the field (in table design mode) as an AutoNumber, which will then automatically add 1 to the sequence with each new record, and it starts with 1.

    Besides that, though, would you mind copying and pasting the SQL of the two queries?

    Sam

  3. #3
    Join Date
    Feb 2006
    Posts
    59
    Basically i am just gathering data imported from text file , re-arranging it and then its final destiny will be moving in another table. now regarding assinging the field as autonumber it MAY not work because of possibility of the original table might have that number already and it will violate the unique key constraint . I am posting you with the 2 queries ;
    Query-1
    UPDATE close_trade_import_data_1 SET tradeno = dlookup("trade_seq","trade_seq");
    -- this one will update the tradeno field with the sequence from the other table

    Query-2
    UPDATE trade_seq SET trade_seq = trade_seq+1;
    -- this just adds +1 to the existing number

    Regards

Posting Permissions

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