Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    vb Syntax rst.Field (2003 SP 3)

    This is so easy that I cannot figure it out. I know the info is here, but I am not using the correct search criteria. If someone can point me to the correct thread I would really appreciate it!

    I have a table where I have two records that are "nearly" identical. I want to edit one record and when saving the record, update about 30 fields in the "nearly matching" record.

    It works, but it is REALLY a lot of code and I know there is a simpler way to accomplish the same goal. Below are snippets of code, where the first one works and the second is what I am trying to streamline. For the sake of brevity, I left out several lines inside the "With rstL" loop the first example, and didn't include the full strSQL in either example.

    ' ========================
    ' This one works but looks ugly
    ' ========================
    Dim rst As DAO.Recordset
    Dim rstL As DAO.Recordset
    Dim strSQL as String

    strSQL = _
    "SELECT . . . " & _
    "FROM . . . "& _
    "WHERE . . . ;"

    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot) ' D Case


    strSQL = _
    "SELECT . . . " & _
    "FROM . . . "& _
    "WHERE . . . ;"

    Set rstL = db.OpenRecordset(strSQL, dbOpenDynaset) ' L Case
    With rst
    With rstL
    .Edit
    rstL!PersonID = rst!PersonID
    rstL!I_SSN = rst!I_SSN
    rstL!I_FirstName = rst!I_FirstName
    rstL!I_MiddleName = rst!I_MiddleName
    . . .
    rstL!I_LastName = rst!I_LastName
    rstL!I_dtmIncident = rst!I_dtmIncident
    rstL!I_dtmTime = rst!I_dtmTime
    rstL!I_logRecord = rst!I_logRecord
    rstL!I_logOccIllness = rst!I_logOccIllness
    .Update
    End with
    End with

    ' ==================================================
    ' This is the approach I prefer, but do not know the syntax
    ' ==================================================
    Dim rst As DAO.Recordset
    Dim rstL As DAO.Recordset
    Dim strSQL as String

    strSQL = _
    "SELECT . . . " & _
    "FROM . . . "& _
    "WHERE . . . ;"

    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot) ' D Case

    strSQL = _
    "SELECT . . . " & _
    "FROM . . . "& _
    "WHERE . . . ;"

    Set rstL = db.OpenRecordset(strSQL, dbOpenDynaset) ' L Case
    With rst
    For i = 1 to .Fields - 1
    With rstL
    For L = 1 to .Fields -1
    .Edit
    !Field(L).value = !Field(i).value
    .Update
    Next L
    End with
    next i
    End with

    Any help is appreciated!

    Ken

    P.S. Is there a way to maintain the formatting when pasting code here? It looks fine when paste it, but it looses the tab/spacing when I actually post.
    thx. kwvh

  2. #2
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: vb Syntax rst.Field (2003 SP 3)

    Hans,

    I seem to have something wrong with my code. I get "Item not found in this collection" everytime it gets to the following line:
    rstL!Field(i).Value = rst!Field(i).Value

    I put a test variable in the code and there is a record in each recordset. Is there something else I should check?

    Thanks,

    Ken

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

    Re: vb Syntax rst.Field (2003 SP 3)

    When it comes up with the error hover your cursor over rstL!Field(i).Value to establish if it is the problem, else if that's ok then hover it over rst!Field(i).Value. whichever is the problem one hiver the cursor over the variable i and see what value it is.
    Then determine how many fields are in that table to see if the value of i has exceeded the number of fields.

    Did you note Hans comment re the index range.
    Note: I assume that you intentionally start the loop at i = 1, i.e. you're skipping the first field (with index = 0).

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: vb Syntax rst.Field (2003 SP 3)

    I think you need to change two things.

    The FOR statements need to add the Count.

    For i = 1 To rs.Fields.Count - 1

    and the line that gives the error needs to be:

    rstL.Fields(i).Value = rst.Fields(i).Value
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: vb Syntax rst.Field (2003 SP 3)

    Patt,

    Thanks for following up. Yes, I did note Hans' comment on the index range and made the change. Below is the code modified so I could test to see if both recordsets had a record and what field it gets the error on.

    Set rstL = db.OpenRecordset(strSQL, dbOpenDynaset) ' L Case
    With rst
    intYear = .RecordCount ' Test to ensure record exists
    With rstL
    .Edit
    intYear = .RecordCount ' Test to ensure record exists
    For i = 0 To rst.Fields.Count - 1
    test1 = rstL!Field(i).Value
    test2 = rst!Field(i).Value
    rstL!Field(i).Value = rst!Field(i).Value
    Next i
    .Update
    End With
    End With
    rst.Close
    Set rst = Nothing


    When running, the intYear(a reused variable) = 1
    and when I hover over the rstL!Field(i).Value = rst!Field(i).Value I get nothing. When I hover over the "i", it shows a "0" (zero). so I know it is on the first field.
    So I inserted the Test1 and Test2 and when running through either line of code I get the "Item not found in collection" message.

    Is there a better method to identify the field(s)?

    Thanks!

    Ken

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

    Re: vb Syntax rst.Field (2003 SP 3)

    John Hutchison has identified the mistakes in my reply, and posted the corrections needed. I have edited my previous reply.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: vb Syntax rst.Field (2003 SP 3)

    Hans

    I don't think you have completely corrected it.

    rstL!Fields(i).Value still gives me the same error.

    I need to have

    rstL.Fields(i).Value
    Regards
    John



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

    Re: vb Syntax rst.Field (2003 SP 3)

    Edited by HansV on 21-Jan-08 (twice) to correct errors - thanks to John Hutchison for pointing them out.

    You must use only one loop, and use the same loop index for both recordsets.
    And you must start edit mode before the loop, and update the record after the loop.
    You cannot use !Fieldname syntax within a nested With ... End With block to refer to two different recordsets.

    ...
    Set rstL = db.OpenRecordset(strSQL, dbOpenDynaset)
    rstL.Edit
    For i = 1 To rst.Fields.Count - 1
    rstL.Fields(i).Value = rst.Fields(i).Value
    Next i
    rstL.Update
    ...

    Note: I assume that you intentionally start the loop at i = 1, i.e. you're skipping the first field (with index = 0).

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

    Re: vb Syntax rst.Field (2003 SP 3)

    Thanks - I must be asleep! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  10. #10
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: vb Syntax rst.Field (2003 SP 3)

    Patt, John and Hans,

    THANKS! Sort of embarrassing to be so ignorant. A period vs. exclamation point and the addition of an "s" fixed it all.

    THANKS!!

    Ken

Posting Permissions

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