Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AddNew -- not! (Access 2003, XP SP 3)

    Getting really fancy here - i have a list of values and another table with the data needed to parse the list and another table where, for each row in the first table, each list of values found in the second one writes a single line for all values. the loop structure is

    For row each in Table 1
    -->goto table 2 and fetch all value names and parser info to locate the value in the long string
    ---->for each value, goto table 3 and write the found value in the correct column
    LOOP thru table 2 and write to table 3
    ON EOF of table 2, pop back to Table 1 and go to the next record

    There's a couple of things that aren't working as expected, but the primary one is, I cannot figure out why AddNew on completion of the innermost loop doesn't appear to do anything. I get the proper values in the proper column and, I'm betting, the system just re-writes to the same row.

    here's my code:

    I'm fairly optimistic there's something I am missing but can't seem to find it - entering flail mode...

    TIA
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AddNew -- not! (Access 2003, XP SP 3)

    The sequence

    WriteRUG.Edit
    WriteRUG.AddNew
    WriteRUG.MoveNext

    makes no sense. AddNew cancels Edit, and MoveNext cancels AddNew, so the net result is that AddNew is never effected.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AddNew -- not! (Access 2003, XP SP 3)

    That is my "flail" code. I believe I've tried many permutations. But AddNew on it's own doesn't add a new record to the target table RUG-III_ItemsTable, so ???

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AddNew -- not! (Access 2003, XP SP 3)

    If you want to add a record to WriteRUG, use

    WriteRUG.AddNew
    WriteRUG.Fields(0) = ...
    WriteRUG.Fields(1) = ...
    ...
    WriteRug.Update

    You MUST have a line with .Update after .AddNew and there should be NO lines with .Edit, .MoveNext etc. in between.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AddNew -- not! (Access 2003, XP SP 3)

    Ok - after some code cleanup, this also doesn't work:

    For j = 1 To 10
    sMdsRecord = rst.Fields(0)

    Set ExtractRUG = dbs.OpenRecordset("RUGIII_ParserData")
    ExtractRUG.MoveFirst

    i = 0

    Do While Not ExtractRUG.EOF
    RUGField = ExtractRUG.Fields(0)
    RUGLen = ExtractRUG.Fields(1)
    RUGStart = ExtractRUG.Fields(2)

    FieldValue = Mid(sMdsRecord, RUGStart, RUGLen)

    Set WriteRUG = dbs.OpenRecordset("RUG-III_ItemsTable")
    WriteRUG.Edit
    WriteRUG.Fields(i) = RUGField & FieldValue
    WriteRUG.Update
    i = i + 1
    ExtractRUG.MoveNext
    Loop

    WriteRUG.AddNew <-- This looks like it should drop to a new record in the WriteRUG recordset but no go.
    rst.MoveNext

    Next j

    Need to keep trying but this has a lot less "noise" than the previous code, FWIW. Some errors I was noticing previously aren't occurring, so no need to trap the value of 'i' now.

    I think there's something about the scope of the recordset that is getting in my way. AddNew works if I keep it in the inner loop, but that gives a new row for each column value, which is not correct.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AddNew -- not! (Access 2003, XP SP 3)

    If you want to create a new record, you should start with AddNew, then set some fields, and finally Update. Your AddNew at the end of the loop doesn't do anything since there is no corresponding Update.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AddNew -- not! (Access 2003, XP SP 3)

    thanks - and it looks like I may have gotten it nailed.

    Set dbs = CurrentDb
    Set WriteRUG = dbs.OpenRecordset("RUG-III_ItemsTable")
    Set rst = dbs.OpenRecordset("Test520a_max")

    rst.MoveFirst
    rst.MoveNext
    rst.MoveNext

    Do While Not rst.EOF
    sMdsRecord = rst.Fields(0)

    Set ExtractRUG = dbs.OpenRecordset("RUGIII_ParserData")
    ExtractRUG.MoveFirst

    i = 0

    Do While Not ExtractRUG.EOF
    RUGField = ExtractRUG.Fields(0)
    RUGLen = ExtractRUG.Fields(1)
    RUGStart = ExtractRUG.Fields(2)

    FieldValue = Mid(sMdsRecord, RUGStart, RUGLen)
    WriteRUG.Edit
    WriteRUG.Fields(i) = RUGField & FieldValue
    WriteRUG.Update
    i = i + 1
    ExtractRUG.MoveNext
    Loop

    WriteRUG.AddNew
    WriteRUG.Update
    WriteRUG.MoveLast
    rst.MoveNext

    Loop

    Not perfect - there's a blank row at the end but by and large it seems to work.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AddNew -- not! (Access 2003, XP SP 3)

    The lines

    WriteRUG.AddNew
    WriteRUG.Update

    create a new record without setting the value of any field, so you end up with a blank new record. If I understand your code correctly, you want this:

    Set dbs = CurrentDb
    Set WriteRUG = dbs.OpenRecordset("RUG-III_ItemsTable")
    Set rst = dbs.OpenRecordset("Test520a_max")

    rst.MoveFirst
    rst.MoveNext
    rst.MoveNext

    Do While Not rst.EOF
    sMdsRecord = rst.Fields(0)

    Set ExtractRUG = dbs.OpenRecordset("RUGIII_ParserData")
    ExtractRUG.MoveFirst

    WriteRUG.AddNew
    i = 0
    Do While Not ExtractRUG.EOF
    RUGField = ExtractRUG.Fields(0)
    RUGLen = ExtractRUG.Fields(1)
    RUGStart = ExtractRUG.Fields(2)

    FieldValue = Mid(sMdsRecord, RUGStart, RUGLen)
    WriteRUG.Fields(i) = RUGField & FieldValue
    i = i + 1
    ExtractRUG.MoveNext
    Loop
    WriteRUG.Update
    rst.MoveNext
    Loop

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AddNew -- not! (Access 2003, XP SP 3)

    I tried your version and got some errors but I may have missed something. I can live with a predictable blank row for now.

    Eventually I want to create the table and data in automation as the code extraction is a selection from a great many possible values in the source string, but I'm just plugging together a demo for the application.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AddNew -- not! (Access 2003, XP SP 3)

    one other thing about this project - running the code causes Access to grow very large, so a compact and repair is required after each run. weird!

Posting Permissions

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