Help- Problems with rst.Edit

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • David
    #1

    Help- Problems with rst.Edit

    Dear All

    I'm trying to use a multi-select listbox as criteria to either add new
    record or edit the current record of the destiantation table with each
    row selected in the box. This works fine for adding a new record but
    once records exist in the destination table, the routine seems to
    overwrite the first highlighted record in the destination table with
    the last highlighted record record, therefore giving two duplicate
    records. I've listed the code below, any advice is greatfully
    received as I seem to have drawn a complete blank and have been trying
    various methods all day.

    David

    Private Sub cmdAutoReport_C lick()

    'Sets all group members reports to the default comments

    For i = 0 To lstGroupMem.Lis tCount
    lstGroupMem.Sel ected(i) = True

    Next i

    If MsgBox("Set the final report comments for all highlighted group
    members?", vbYesNo + vbQuestion, "Auto Set Reports") = vbNo Then

    'Clear highlighted records


    For i = 0 To lstGroupMem.Lis tCount
    lstGroupMem.Sel ected(i) = False
    Next i

    Exit Sub

    Else

    DoCmd.SetWarnin gs False


    'Declare variables

    Dim ATID As Variant 'variable for attaiment indicators ID
    Dim Gender As String 'variable for gender
    Dim attainment As Variant 'variable for attainment level
    Dim attainsub As Variant 'variable for attainment sub-level
    Dim skills As String 'variable for skills comment
    Dim performance As Variant 'variable for performance comment
    Dim Target As Variant 'variable for target comment
    Dim effort As String 'variable for effort comment
    Dim lbl3ID As Variant
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    'Set recordset focus on tblReportCommen ts
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecords et("tblReportCo mments")

    'Introduce variant
    For Each lbl3ID In lstGroupMem.Ite msSelected 'the listbox with the
    groupmember values

    Gender = lstGroupMem.Col umn(10, lbl3ID) 'set the gender value
    from the groupmembers listbox
    attainment = lstGroupMem.Col umn(5, lbl3ID) 'set the attainment
    value from the groupmembers listbox
    attainsub = lstGroupMem.Col umn(6, lbl3ID) 'set the attainment
    sub-level value from the groupmembers listbox

    'set the attainment ID value using the attainment level and
    gender from tblAttainmentIn dicators
    ATID = DLookup("[AttainmentID]", "tblAttainmentI ndicators",
    "[Attainment Level] = " & "'" & attainment & "'" & "And [Attainment
    Gender]=" & "'" & Gender & "'")

    'sets the comment values using the ATID value from
    tblAttainmentSu bLevel
    skills = DLookup("[Basic Skills and Tactics]",
    "tblAttainmentS ubLevel", "[AttainmentID] =" & ATID & "And
    [AttainmentSubLe vel] =" & "'" & attainsub & "'")
    performance = DLookup("[Performance and Physiology]",
    "tblAttainmentS ubLevel", "[AttainmentID]=" & ATID & "And
    [AttainmentSubLe vel] =" & "'" & attainsub & "'")
    Target = DLookup("[Target]", "tblAttainmentS ubLevel",
    "[AttainmentID]=" & ATID & "And [AttainmentSubLe vel] =" & "'" &
    attainsub & "'")

    'sets the effort comment value
    effort = DLookup("[Effort Description]", "tblEffort" , "[Effort
    Grade]= 'b' ")


    'Check to see if the groupmember selected already has a report
    comments record
    If DCount("[Comment ID]", "tblReportComme nts", "[Group Member ID]=
    " & lstGroupMem.Col umn(0, lbl3ID)) = 0 Then


    'add a new record in tblReportCommen ts
    rst.AddNew
    rst![Group Member ID] = lstGroupMem.Col umn(0, lbl3ID)
    'groupmember ID from the groupmember listbox
    rst![Targets Comments] = Target 'set the target
    comment
    rst![Basic Skills and Tactics] = skills 'set the basic
    skills comment
    rst![Performance and Physiology] = performance 'set
    the performance comment
    rst![Effort Comments] = effort 'set the effort comment

    'update the recordset
    rst.Update

    'else edit the current report comment record with the new values
    Else

    MsgBox lstGroupMem.Col umn(0, lbl3ID)

    'edit current record in tblReportCommen ts
    rst.Edit
    rst![Group Member ID] = lstGroupMem.Col umn(0, lbl3ID)
    'groupmember ID from the groupmember listbox
    rst![Targets Comments] = Target 'set the target
    comment
    rst![Basic Skills and Tactics] = skills 'set the basic
    skills comment
    rst![Performance and Physiology] = performance 'set
    the performance comment
    rst![Effort Comments] = effort 'set the effort comment


    'update the recordset
    rst.Update

    End If

    Next lbl3ID


    Set dbs = Nothing
    Set rst = Nothing

    For i = 0 To lstGroupMem.Lis tCount
    lstGroupMem.Sel ected(i) = False
    Next i

    DoCmd.SetWarnin gs True

    End If

    End Sub
    Tags: None
    • Salad
      #2
      Re: Help- Problems with rst.Edit

      David wrote:
      Dear All
      >
      I'm trying to use a multi-select listbox as criteria to either add new
      record or edit the current record of the destiantation table with each
      row selected in the box. This works fine for adding a new record but
      once records exist in the destination table, the routine seems to
      overwrite the first highlighted record in the destination table with
      the last highlighted record record, therefore giving two duplicate
      records. I've listed the code below, any advice is greatfully
      received as I seem to have drawn a complete blank and have been trying
      various methods all day.
      >
      David
      >
      Private Sub cmdAutoReport_C lick()
      >
      'Sets all group members reports to the default comments
      >
      For i = 0 To lstGroupMem.Lis tCount
      lstGroupMem.Sel ected(i) = True
      >
      Next i
      The above line works but if you were attempting to get info from it it
      wouldn't. For example, if the listbox name was List0, the following
      would blow up.
      For intFor = 0 To Me.List0.ListCo unt
      MsgBox Me.List0.Column (0, intFor)
      Next

      The reason? The first row is 0 in the list count. If you have 3 rows,
      the rows would be 0,1,2. So normally one would make it ListCount-1.
      >
      If MsgBox("Set the final report comments for all highlighted group
      members?", vbYesNo + vbQuestion, "Auto Set Reports") = vbNo Then
      >
      'Clear highlighted records
      >
      >
      For i = 0 To lstGroupMem.Lis tCount
      lstGroupMem.Sel ected(i) = False
      Next i
      >
      Exit Sub
      >
      Else
      >
      DoCmd.SetWarnin gs False
      >
      >
      'Declare variables
      >
      Dim ATID As Variant 'variable for attaiment indicators ID
      Dim Gender As String 'variable for gender
      Dim attainment As Variant 'variable for attainment level
      Dim attainsub As Variant 'variable for attainment sub-level
      Dim skills As String 'variable for skills comment
      Dim performance As Variant 'variable for performance comment
      Dim Target As Variant 'variable for target comment
      Dim effort As String 'variable for effort comment
      Dim lbl3ID As Variant
      Dim dbs As DAO.Database
      Dim rst As DAO.Recordset
      >
      'Set recordset focus on tblReportCommen ts
      Set dbs = CurrentDb
      Set rst = dbs.OpenRecords et("tblReportCo mments")
      >
      'Introduce variant
      For Each lbl3ID In lstGroupMem.Ite msSelected 'the listbox with the
      groupmember values
      >
      Gender = lstGroupMem.Col umn(10, lbl3ID) 'set the gender value
      from the groupmembers listbox
      attainment = lstGroupMem.Col umn(5, lbl3ID) 'set the attainment
      value from the groupmembers listbox
      attainsub = lstGroupMem.Col umn(6, lbl3ID) 'set the attainment
      sub-level value from the groupmembers listbox
      >
      'set the attainment ID value using the attainment level and
      gender from tblAttainmentIn dicators
      ATID = DLookup("[AttainmentID]", "tblAttainmentI ndicators",
      "[Attainment Level] = " & "'" & attainment & "'" & "And [Attainment
      Gender]=" & "'" & Gender & "'")
      >
      'sets the comment values using the ATID value from
      tblAttainmentSu bLevel
      skills = DLookup("[Basic Skills and Tactics]",
      "tblAttainmentS ubLevel", "[AttainmentID] =" & ATID & "And
      [AttainmentSubLe vel] =" & "'" & attainsub & "'")
      performance = DLookup("[Performance and Physiology]",
      "tblAttainmentS ubLevel", "[AttainmentID]=" & ATID & "And
      [AttainmentSubLe vel] =" & "'" & attainsub & "'")
      Target = DLookup("[Target]", "tblAttainmentS ubLevel",
      "[AttainmentID]=" & ATID & "And [AttainmentSubLe vel] =" & "'" &
      attainsub & "'")
      >
      'sets the effort comment value
      effort = DLookup("[Effort Description]", "tblEffort" , "[Effort
      Grade]= 'b' ")
      >
      >
      'Check to see if the groupmember selected already has a report
      comments record
      If DCount("[Comment ID]", "tblReportComme nts", "[Group Member ID]=
      " & lstGroupMem.Col umn(0, lbl3ID)) = 0 Then
      >
      >
      'add a new record in tblReportCommen ts
      rst.AddNew
      rst![Group Member ID] = lstGroupMem.Col umn(0, lbl3ID)
      'groupmember ID from the groupmember listbox
      rst![Targets Comments] = Target 'set the target
      comment
      rst![Basic Skills and Tactics] = skills 'set the basic
      skills comment
      rst![Performance and Physiology] = performance 'set
      the performance comment
      rst![Effort Comments] = effort 'set the effort comment
      >
      'update the recordset
      rst.Update
      >
      'else edit the current report comment record with the new values
      Else
      >
      MsgBox lstGroupMem.Col umn(0, lbl3ID)
      >
      'edit current record in tblReportCommen ts
      rst.Edit
      rst![Group Member ID] = lstGroupMem.Col umn(0, lbl3ID)
      'groupmember ID from the groupmember listbox
      rst![Targets Comments] = Target 'set the target
      comment
      rst![Basic Skills and Tactics] = skills 'set the basic
      skills comment
      rst![Performance and Physiology] = performance 'set
      the performance comment
      rst![Effort Comments] = effort 'set the effort comment
      >
      >
      'update the recordset
      rst.Update
      >
      End If
      >
      Next lbl3ID
      >
      >
      Set dbs = Nothing
      Set rst = Nothing
      >
      For i = 0 To lstGroupMem.Lis tCount
      lstGroupMem.Sel ected(i) = False
      Next i
      >
      DoCmd.SetWarnin gs True
      >
      End If
      >
      End Sub
      Well, you got the count to determine if it existed. And so you edited
      whatever record you were sitting on. I think you might find it crucial
      to finding the record you are editing. In fact, the Dcount isn't needed
      as far as I can see. For example
      rst.FindFirst "[Group Member ID]= " & lstGroupMem.Col umn(0,lbl3ID))
      If rst.NoMatch then'
      rst.AddNew
      Else
      rst.Edit
      Endif
      would make more sense.

      Boots
      http://www.youtube.com/watch?v=nw7Qg_2JjWk




        Comment

        • David
          #3
          Re: Help- Problems with rst.Edit

          On Feb 15, 4:07 pm, Salad <o...@vinegar.c omwrote:
          David wrote:
          Dear All
          >
          I'm trying to use a multi-select listbox as criteria to either add new
          record or edit the current record of the destiantation table with each
          row selected in the box.  This works fine for adding a new record but
          once records exist in the destination table, the routine seems to
          overwrite the first highlighted record in the destination table with
          the last highlighted record record, therefore giving two duplicate
          records.  I've listed the code below, any advice is greatfully
          received as I seem to have drawn a complete blank and have been trying
          various methods all day.
          >
          David
          >
          Private Sub cmdAutoReport_C lick()
          >
          'Sets all group members reports to the default comments
          >
          For i = 0 To lstGroupMem.Lis tCount
                   lstGroupMem.Sel ected(i) = True
          >
              Next i
          >
          The above line works but if you were attempting to get info from it it
          wouldn't.  For example, if the listbox name was List0, the following
          would blow up.
               For intFor = 0 To Me.List0.ListCo unt
                   MsgBox Me.List0.Column (0, intFor)
               Next
          >
          The reason?  The first row is 0 in the list count.  If you have 3 rows,
          the rows would be 0,1,2.  So normally one would make it ListCount-1.
          >
          >
          >
          >
          >
          >
          >
          If MsgBox("Set the final report comments for all highlighted group
          members?", vbYesNo + vbQuestion, "Auto Set Reports") = vbNo Then
          >
              'Clear highlighted records
          >
              For i = 0 To lstGroupMem.Lis tCount
                  lstGroupMem.Sel ected(i) = False
              Next i
          >
              Exit Sub
          >
          Else
          >
              DoCmd.SetWarnin gs False
          >
              'Declare variables
          >
              Dim ATID As Variant 'variable for attaiment indicators ID
              Dim Gender As String 'variable for gender
              Dim attainment As Variant 'variable for attainment level
              Dim attainsub As Variant 'variable for attainment sub-level
              Dim skills As String 'variable for skills comment
              Dim performance As Variant 'variable for performance comment
              Dim Target As Variant 'variable for target comment
              Dim effort As String 'variable for effort comment
              Dim lbl3ID As Variant
              Dim dbs As DAO.Database
              Dim rst As DAO.Recordset
          >
                'Set recordset focus on tblReportCommen ts
                  Set dbs = CurrentDb
                  Set rst = dbs.OpenRecords et("tblReportCo mments")
          >
              'Introduce variant
              For Each lbl3ID In lstGroupMem.Ite msSelected 'the listbox with the
          groupmember values
          >
                Gender = lstGroupMem.Col umn(10, lbl3ID) 'set the gender value
          from the groupmembers listbox
                attainment = lstGroupMem.Col umn(5, lbl3ID) 'set the attainment
          value from the groupmembers listbox
                attainsub = lstGroupMem.Col umn(6, lbl3ID) 'set the attainment
          sub-level value from the groupmembers listbox
          >
                'set the attainment ID value using the attainment level and
          gender from tblAttainmentIn dicators
                ATID = DLookup("[AttainmentID]", "tblAttainmentI ndicators",
          "[Attainment Level] = " & "'" & attainment & "'" & "And [Attainment
          Gender]=" & "'" & Gender & "'")
          >
                'sets the comment values using the ATID value from
          tblAttainmentSu bLevel
                skills = DLookup("[Basic Skills and Tactics]",
          "tblAttainmentS ubLevel", "[AttainmentID] =" & ATID & "And
          [AttainmentSubLe vel] =" & "'" & attainsub & "'")
                performance = DLookup("[Performance and Physiology]",
          "tblAttainmentS ubLevel", "[AttainmentID]=" & ATID & "And
          [AttainmentSubLe vel] =" & "'" & attainsub & "'")
                Target = DLookup("[Target]", "tblAttainmentS ubLevel",
          "[AttainmentID]=" & ATID & "And [AttainmentSubLe vel] =" & "'" &
          attainsub & "'")
          >
                'sets the effort comment value
                effort = DLookup("[Effort Description]", "tblEffort" , "[Effort
          Grade]= 'b' ")
          >
                 'Check to see if the groupmember selected already has a report
          comments record
              If DCount("[Comment ID]", "tblReportComme nts", "[Group Member ID]=
          " & lstGroupMem.Col umn(0, lbl3ID)) = 0 Then
          >
                'add a new record in tblReportCommen ts
                      rst.AddNew
                          rst![Group Member ID] = lstGroupMem.Col umn(0, lbl3ID)
          'groupmember ID from the groupmember listbox
                          rst![Targets Comments] = Target 'set the target
          comment
                          rst![Basic Skills and Tactics] = skills 'set the basic
          skills comment
                          rst![Performance and Physiology] = performance 'set
          the performance comment
                          rst![Effort Comments] = effort 'set the effort comment
          >
                      'update the recordset
                      rst.Update
          >
                'else edit the current report comment record with the new values
                Else
          >
                      MsgBox lstGroupMem.Col umn(0, lbl3ID)
          >
                      'edit current record in tblReportCommen ts
                      rst.Edit
                          rst![Group Member ID] = lstGroupMem.Col umn(0, lbl3ID)
          'groupmember ID from the groupmember listbox
                          rst![Targets Comments] = Target 'set the target
          comment
                          rst![Basic Skills and Tactics] = skills 'set the basic
          skills comment
                          rst![Performance and Physiology] = performance 'set
          the performance comment
                          rst![Effort Comments] = effort 'set the effort comment
          >
                      'update the recordset
                      rst.Update
          >
              End If
          >
              Next lbl3ID
          >
                  Set dbs = Nothing
                  Set rst = Nothing
          >
                  For i = 0 To lstGroupMem.Lis tCount
                  lstGroupMem.Sel ected(i) = False
                      Next i
          >
                  DoCmd.SetWarnin gs True
          >
          End If
          >
          End Sub
          >
          Well, you got the count to determine if it existed.  And so you edited
          whatever record you were sitting on.  I think you might find it crucial
          to finding the record you are editing.  In fact, the Dcount isn't needed
          as far as I can see.  For example
             rst.FindFirst "[Group Member ID]= " & lstGroupMem.Col umn(0,lbl3ID))
             If rst.NoMatch then'
                  rst.AddNew
             Else
                  rst.Edit
             Endif
          would make more sense.
          >
          Bootshttp://www.youtube.com/watch?v=nw7Qg_2 JjWk- Hide quoted text -
          >
          - Show quoted text -- Hide quoted text -
          >
          - Show quoted text -
          That's great thanks, the rst.FindFirst bit sorted it out for me!

            Comment

            Previous template Next
            Working...