Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Read query, build another table based on content (2k)

    Want to read field1 in a raw data query (rst) and create another table/query (tsr) based on field1 content. I get the LINEID field written to the new table to start the record but the Update_prod: sub fails with a "NO CURRENT RECORD" message leaving the PROD1 field blank. Three fields in new table: LINEID, PROD1, SOFT1. Each time field1 ="LineID", create new record and fill in 3 fields.


    'open query-cnf-raw, read field1, build query-cnf-scrub records/fields based on field1 content
    Dim rst As DAO.Recordset
    Dim tsr As DAO.Recordset
    Dim dbs As DAO.database
    Set dbs = CurrentDb
    Set rst = dbs.openrecordset("query-cnf-raw", dbopendynaset)
    Set tsr = dbs.openrecordset("query-cnf-scrub", dbopendynaset)
    Do While Not rst.EOF
    rst.Edit
    id_update:
    If Left(rst!field1, 4) = "Line" Then GoTo update_id
    If Left(rst!field1, 7) = "prodnam" Then GoTo Update_prod
    If Left(rst!field1, 7) = "softdld" Then GoTo Update_soft
    'End If
    Continue:
    rst.MoveNext
    Loop
    Exit Sub
    update_id:
    tsr.AddNew
    tsr!LineID = Mid(rst!field1, 10, 8)
    tsr.Update
    GoTo Continue
    Update_prod:
    tsr.Edit
    tsr!prod1 = Mid(rst!field1, 11, 22)
    tsr.Update
    GoTo Continue
    Update_soft:
    tsr.Edit
    tsr!soft1 = Mid(rst!field1, 14, 22)
    tsr.Update
    GoTo Continue
    End Sub

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Read query, build another table based on content (2k)

    You appear to have a logic error, as you need to set all three fields before you actually do the tsr.Update. The way you code is structured, you save the record as soon as you process the LineID, and Prod1 and Soft1 will have Null values. Are you trying to create a three field table from a single field table where every three records represent one record in your new database? If so, you might try an If ElseIf construct and put the code all in one procedure. The only issue you need to worry about is if there are errors in the input table such that things are out of sequence.
    Wendell

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Read query, build another table based on content (2k)

    <P ID="edit" class=small>(Edited by patt on 14-Jan-03 07:17. Add comment about input file order.)</P>You have another problem, and that is that you have not read the tsr record before trying to update it in Update_prod: and Update_soft:, I believe this is the reason for the "NO CURRENT RECORD" error message.

    Does each input record always have the 3 fields? If so, change your code to read 3 records at a time before doing the AddNew...Update.
    Eg.
    tsr.AddNew
    rst.MoveNext
    tsr!LineID = Mid(rst!field1, 10, 8)
    rst.MoveNext
    tsr!prod1 = Mid(rst!field1, 11, 22)
    rst.MoveNext
    tsr!soft1 = Mid(rst!field1, 14, 22)
    tsr.Update

    This presumes that the order of the input file is:
    "Line"
    "prodnam"
    "softdld"

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read query, build another table based on content (2k)

    The problem was the tsr.update. I was unclear on it's function and thought it was needed after each assignment of
    tsr! instead of just once per record. I fixed the logic and it works as needed. Thanks for the help.

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read query, build another table based on content (2k)

    The problem was the tsr.update. I was unclear on it's function and thought it was needed after each assignment of
    tsr! instead of just once per record. I fixed the logic and it works as needed. A typical raw table has multiple Prod and Soft per Line so the number of records to read is variable. I'm having to code in a counter to allocate data to multiple Prod fields (prod1, prod2,etc.) until the next record with LINE comes up and reset to zero. That appears to be working as needed. Thanks for the help.

Posting Permissions

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