rs.Edit instead of rs.AddNew

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • dixie

    rs.Edit instead of rs.AddNew

    I have some code that adds new records into a table for each ID in a list
    box when a button on a form is clicked. This works fine. My problem now is
    that I wish to be able to edit all the records for people whose ID is in the
    list box. I made minor changes to the code (mainly replacing rs.AddNew with
    rs.Edit)and it appears to be updating only the first record and then
    overwriting that record with the next, etc until it runs out of ID's in the
    list box. In other words, it is stepping through the ID's in the list box,
    but not the records. Is there a trick to this? I have spent many hours
    doing minor changes and still have the same problem.

    The code follows (I have reduced the number of fields I am updating to keep
    the size of the message down).

    Dim intIndex As Integer

    For intIndex = 0 To Me.lboBulkList. ListCount

    Me.lbo.BulkList .Selected(intIn dex) = True

    Next intIndex



    Dim db As Database

    Dim rs As Recordset

    Dim prm As Parameter

    Dim qdf As QueryDef



    Set db = CurrentDb()

    Set qdf = db.QueryDefs("q ryBulkEdit")



    For Each prm In qdf.Parameters

    prm.Value = Eval(prm.Name)

    Next prm



    Set rs = qdf.OpenRecords et(dbOpenDynase t)



    Dim frm As Form

    Dim ctl As Control

    Dim varItm As Variant



    Set frm = Forms!frmExcurs ions

    Set ctl = frm! lboBulkList

    For Each varItm In ctl.ItemsSelect ed



    rs.Edit

    rs(0) = Me. lboBulkList.Ite mData(varItm)

    rs!Date = frm!txtDate

    rs!Faculty = frm!cboFaculty

    rs!Course = frm!Course

    rs!Cost = frm!ExCost

    rs.Update

    Next varItm

    rs.Close: Set rs = Nothing


    dixie


  • Wayne Morgan

    #2
    Re: rs.Edit instead of rs.AddNew

    You are correct. The Edit function will edit the current record. You will
    need to start at the first record and do a MoveNext, FindFirst, or other
    procedure that will move to the record you want to edit then do the edit.

    --
    Wayne Morgan
    MS Access MVP


    "dixie" <dixiec@dogmail .com> wrote in message
    news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...[color=blue]
    > I have some code that adds new records into a table for each ID in a list
    > box when a button on a form is clicked. This works fine. My problem now[/color]
    is[color=blue]
    > that I wish to be able to edit all the records for people whose ID is in[/color]
    the[color=blue]
    > list box. I made minor changes to the code (mainly replacing rs.AddNew[/color]
    with[color=blue]
    > rs.Edit)and it appears to be updating only the first record and then
    > overwriting that record with the next, etc until it runs out of ID's in[/color]
    the[color=blue]
    > list box. In other words, it is stepping through the ID's in the list[/color]
    box,[color=blue]
    > but not the records. Is there a trick to this? I have spent many hours
    > doing minor changes and still have the same problem.
    >
    > The code follows (I have reduced the number of fields I am updating to[/color]
    keep[color=blue]
    > the size of the message down).
    >
    > Dim intIndex As Integer
    >
    > For intIndex = 0 To Me.lboBulkList. ListCount
    >
    > Me.lbo.BulkList .Selected(intIn dex) = True
    >
    > Next intIndex
    >
    >
    >
    > Dim db As Database
    >
    > Dim rs As Recordset
    >
    > Dim prm As Parameter
    >
    > Dim qdf As QueryDef
    >
    >
    >
    > Set db = CurrentDb()
    >
    > Set qdf = db.QueryDefs("q ryBulkEdit")
    >
    >
    >
    > For Each prm In qdf.Parameters
    >
    > prm.Value = Eval(prm.Name)
    >
    > Next prm
    >
    >
    >
    > Set rs = qdf.OpenRecords et(dbOpenDynase t)
    >
    >
    >
    > Dim frm As Form
    >
    > Dim ctl As Control
    >
    > Dim varItm As Variant
    >
    >
    >
    > Set frm = Forms!frmExcurs ions
    >
    > Set ctl = frm! lboBulkList
    >
    > For Each varItm In ctl.ItemsSelect ed
    >
    >
    >
    > rs.Edit
    >
    > rs(0) = Me. lboBulkList.Ite mData(varItm)
    >
    > rs!Date = frm!txtDate
    >
    > rs!Faculty = frm!cboFaculty
    >
    > rs!Course = frm!Course
    >
    > rs!Cost = frm!ExCost
    >
    > rs.Update
    >
    > Next varItm
    >
    > rs.Close: Set rs = Nothing
    >
    >
    > dixie
    >
    >[/color]


    Comment

    • Pat

      #3
      Re: rs.Edit instead of rs.AddNew

      Dixie,
      You are looping through your selected items, but you are not looping through
      your recordset (although looping through the recordset is only going to work
      if the records are in the _exact_ same order as your listbox). rs(0) is the
      first record in the returned recordset. As you loop through the items, you
      are editing the same record.

      Instead, you want to select each record that cooresponds to each selected
      listbox item and then update, or, pull all your records, find the one that
      matches the listbox item in the loop and then update.

      Hope that gets you started.
      Pat



      "dixie" <dixiec@dogmail .com> wrote in message
      news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...[color=blue]
      > I have some code that adds new records into a table for each ID in a list
      > box when a button on a form is clicked. This works fine. My problem now[/color]
      is[color=blue]
      > that I wish to be able to edit all the records for people whose ID is in[/color]
      the[color=blue]
      > list box. I made minor changes to the code (mainly replacing rs.AddNew[/color]
      with[color=blue]
      > rs.Edit)and it appears to be updating only the first record and then
      > overwriting that record with the next, etc until it runs out of ID's in[/color]
      the[color=blue]
      > list box. In other words, it is stepping through the ID's in the list[/color]
      box,[color=blue]
      > but not the records. Is there a trick to this? I have spent many hours
      > doing minor changes and still have the same problem.
      >
      > The code follows (I have reduced the number of fields I am updating to[/color]
      keep[color=blue]
      > the size of the message down).
      >
      > Dim intIndex As Integer
      >
      > For intIndex = 0 To Me.lboBulkList. ListCount
      >
      > Me.lbo.BulkList .Selected(intIn dex) = True
      >
      > Next intIndex
      >
      >
      >
      > Dim db As Database
      >
      > Dim rs As Recordset
      >
      > Dim prm As Parameter
      >
      > Dim qdf As QueryDef
      >
      >
      >
      > Set db = CurrentDb()
      >
      > Set qdf = db.QueryDefs("q ryBulkEdit")
      >
      >
      >
      > For Each prm In qdf.Parameters
      >
      > prm.Value = Eval(prm.Name)
      >
      > Next prm
      >
      >
      >
      > Set rs = qdf.OpenRecords et(dbOpenDynase t)
      >
      >
      >
      > Dim frm As Form
      >
      > Dim ctl As Control
      >
      > Dim varItm As Variant
      >
      >
      >
      > Set frm = Forms!frmExcurs ions
      >
      > Set ctl = frm! lboBulkList
      >
      > For Each varItm In ctl.ItemsSelect ed
      >
      >
      >
      > rs.Edit
      >
      > rs(0) = Me. lboBulkList.Ite mData(varItm)
      >
      > rs!Date = frm!txtDate
      >
      > rs!Faculty = frm!cboFaculty
      >
      > rs!Course = frm!Course
      >
      > rs!Cost = frm!ExCost
      >
      > rs.Update
      >
      > Next varItm
      >
      > rs.Close: Set rs = Nothing
      >
      >
      > dixie
      >
      >[/color]


      Comment

      • dixie

        #4
        Re: rs.Edit instead of rs.AddNew

        OK Pat, I thought as much. You have confirmed what I believed was
        happening. My problem is that I don't know how to combine the previous type
        of code that updates according to an ID in a list box and then this type of
        code

        With rs

        If rs.RecordCount > 0 Then
        .MoveFirst
        Do
        .Edit
        rs!Cost = 0
        .Update
        .MoveNext
        Loop Until .EOF
        End If
        .Close: Set rs = Nothing

        End With

        I know that I need to step through the records in the recordset and edit
        each field according to my ID in the list box, but I can't seem to combine
        the two ideas to get to the solution I want - which is for each person
        selected to have their specific record edited according to a what is in
        various controls on the form.

        dixie

        ----- Original Message -----
        From: "Pat" <noemail@ihates pam.bum>
        Newsgroups: comp.databases. ms-access
        Sent: Thursday, April 22, 2004 11:38 AM
        Subject: Re: rs.Edit instead of rs.AddNew

        [color=blue]
        > Dixie,
        > You are looping through your selected items, but you are not looping[/color]
        through[color=blue]
        > your recordset (although looping through the recordset is only going to[/color]
        work[color=blue]
        > if the records are in the _exact_ same order as your listbox). rs(0) is[/color]
        the[color=blue]
        > first record in the returned recordset. As you loop through the items,[/color]
        you[color=blue]
        > are editing the same record.
        >
        > Instead, you want to select each record that cooresponds to each selected
        > listbox item and then update, or, pull all your records, find the one that
        > matches the listbox item in the loop and then update.
        >
        > Hope that gets you started.
        > Pat
        >
        >
        >
        > "dixie" <dixiec@dogmail .com> wrote in message
        > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...[color=green]
        > > I have some code that adds new records into a table for each ID in a[/color][/color]
        list[color=blue][color=green]
        > > box when a button on a form is clicked. This works fine. My problem[/color][/color]
        now[color=blue]
        > is[color=green]
        > > that I wish to be able to edit all the records for people whose ID is in[/color]
        > the[color=green]
        > > list box. I made minor changes to the code (mainly replacing rs.AddNew[/color]
        > with[color=green]
        > > rs.Edit)and it appears to be updating only the first record and then
        > > overwriting that record with the next, etc until it runs out of ID's in[/color]
        > the[color=green]
        > > list box. In other words, it is stepping through the ID's in the list[/color]
        > box,[color=green]
        > > but not the records. Is there a trick to this? I have spent many hours
        > > doing minor changes and still have the same problem.
        > >
        > > The code follows (I have reduced the number of fields I am updating to[/color]
        > keep[color=green]
        > > the size of the message down).
        > >
        > > Dim intIndex As Integer
        > >
        > > For intIndex = 0 To Me.lboBulkList. ListCount
        > >
        > > Me.lbo.BulkList .Selected(intIn dex) = True
        > >
        > > Next intIndex
        > >
        > >
        > >
        > > Dim db As Database
        > >
        > > Dim rs As Recordset
        > >
        > > Dim prm As Parameter
        > >
        > > Dim qdf As QueryDef
        > >
        > >
        > >
        > > Set db = CurrentDb()
        > >
        > > Set qdf = db.QueryDefs("q ryBulkEdit")
        > >
        > >
        > >
        > > For Each prm In qdf.Parameters
        > >
        > > prm.Value = Eval(prm.Name)
        > >
        > > Next prm
        > >
        > >
        > >
        > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
        > >
        > >
        > >
        > > Dim frm As Form
        > >
        > > Dim ctl As Control
        > >
        > > Dim varItm As Variant
        > >
        > >
        > >
        > > Set frm = Forms!frmExcurs ions
        > >
        > > Set ctl = frm! lboBulkList
        > >
        > > For Each varItm In ctl.ItemsSelect ed
        > >
        > >
        > >
        > > rs.Edit
        > >
        > > rs(0) = Me. lboBulkList.Ite mData(varItm)
        > >
        > > rs!Date = frm!txtDate
        > >
        > > rs!Faculty = frm!cboFaculty
        > >
        > > rs!Course = frm!Course
        > >
        > > rs!Cost = frm!ExCost
        > >
        > > rs.Update
        > >
        > > Next varItm
        > >
        > > rs.Close: Set rs = Nothing
        > >
        > >
        > > dixie
        > >
        > >[/color]
        >
        >[/color]

        "Pat" <noemail@ihates pam.bum> wrote in message
        news:AmFhc.8861 $NR5.255@fe1.te xas.rr.com...[color=blue]
        > Dixie,
        > You are looping through your selected items, but you are not looping[/color]
        through[color=blue]
        > your recordset (although looping through the recordset is only going to[/color]
        work[color=blue]
        > if the records are in the _exact_ same order as your listbox). rs(0) is[/color]
        the[color=blue]
        > first record in the returned recordset. As you loop through the items,[/color]
        you[color=blue]
        > are editing the same record.
        >
        > Instead, you want to select each record that cooresponds to each selected
        > listbox item and then update, or, pull all your records, find the one that
        > matches the listbox item in the loop and then update.
        >
        > Hope that gets you started.
        > Pat
        >
        >
        >
        > "dixie" <dixiec@dogmail .com> wrote in message
        > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...[color=green]
        > > I have some code that adds new records into a table for each ID in a[/color][/color]
        list[color=blue][color=green]
        > > box when a button on a form is clicked. This works fine. My problem[/color][/color]
        now[color=blue]
        > is[color=green]
        > > that I wish to be able to edit all the records for people whose ID is in[/color]
        > the[color=green]
        > > list box. I made minor changes to the code (mainly replacing rs.AddNew[/color]
        > with[color=green]
        > > rs.Edit)and it appears to be updating only the first record and then
        > > overwriting that record with the next, etc until it runs out of ID's in[/color]
        > the[color=green]
        > > list box. In other words, it is stepping through the ID's in the list[/color]
        > box,[color=green]
        > > but not the records. Is there a trick to this? I have spent many hours
        > > doing minor changes and still have the same problem.
        > >
        > > The code follows (I have reduced the number of fields I am updating to[/color]
        > keep[color=green]
        > > the size of the message down).
        > >
        > > Dim intIndex As Integer
        > >
        > > For intIndex = 0 To Me.lboBulkList. ListCount
        > >
        > > Me.lbo.BulkList .Selected(intIn dex) = True
        > >
        > > Next intIndex
        > >
        > >
        > >
        > > Dim db As Database
        > >
        > > Dim rs As Recordset
        > >
        > > Dim prm As Parameter
        > >
        > > Dim qdf As QueryDef
        > >
        > >
        > >
        > > Set db = CurrentDb()
        > >
        > > Set qdf = db.QueryDefs("q ryBulkEdit")
        > >
        > >
        > >
        > > For Each prm In qdf.Parameters
        > >
        > > prm.Value = Eval(prm.Name)
        > >
        > > Next prm
        > >
        > >
        > >
        > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
        > >
        > >
        > >
        > > Dim frm As Form
        > >
        > > Dim ctl As Control
        > >
        > > Dim varItm As Variant
        > >
        > >
        > >
        > > Set frm = Forms!frmExcurs ions
        > >
        > > Set ctl = frm! lboBulkList
        > >
        > > For Each varItm In ctl.ItemsSelect ed
        > >
        > >
        > >
        > > rs.Edit
        > >
        > > rs(0) = Me. lboBulkList.Ite mData(varItm)
        > >
        > > rs!Date = frm!txtDate
        > >
        > > rs!Faculty = frm!cboFaculty
        > >
        > > rs!Course = frm!Course
        > >
        > > rs!Cost = frm!ExCost
        > >
        > > rs.Update
        > >
        > > Next varItm
        > >
        > > rs.Close: Set rs = Nothing
        > >
        > >
        > > dixie
        > >
        > >[/color]
        >
        >[/color]


        Comment

        • Pat

          #5
          Re: rs.Edit instead of rs.AddNew

          Dixie,
          Your original post and your latest reply contains a loop that looks to have
          originally been designed to loop through all records in a recordset and
          update fields. From your description, you want to update only records
          listed in a listbox, for which you have their ID.

          If your listbox will likely contain a few items, it could create a dynamic
          SLQ statment that selects only the record you want to edit. Then you can
          edit that record.

          Set frm = Forms!frmExcurs ions
          Set ctl = frm! lboBulkList
          For Each varItm In ctl.ItemsSelect ed
          strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
          Set rs = CurrentDb.OpenR ecordset(strSql )
          If rs.RecordCount > 0 Then
          rs.FieldNameToE dit = YourNewValue
          End if
          Next varItm

          If you have more than one record that will return in the recordset, you will
          need a loop to handle update each record in the set.

          Hope this helps,
          Pat



          "dixie" <dixiec@dogmail .com> wrote in message
          news:gXFhc.17$A h5.1849@nnrp1.o zemail.com.au.. .[color=blue]
          > OK Pat, I thought as much. You have confirmed what I believed was
          > happening. My problem is that I don't know how to combine the previous[/color]
          type[color=blue]
          > of code that updates according to an ID in a list box and then this type[/color]
          of[color=blue]
          > code
          >
          > With rs
          >
          > If rs.RecordCount > 0 Then
          > .MoveFirst
          > Do
          > .Edit
          > rs!Cost = 0
          > .Update
          > .MoveNext
          > Loop Until .EOF
          > End If
          > .Close: Set rs = Nothing
          >
          > End With
          >
          > I know that I need to step through the records in the recordset and edit
          > each field according to my ID in the list box, but I can't seem to combine
          > the two ideas to get to the solution I want - which is for each person
          > selected to have their specific record edited according to a what is in
          > various controls on the form.
          >
          > dixie
          >
          > ----- Original Message -----
          > From: "Pat" <noemail@ihates pam.bum>
          > Newsgroups: comp.databases. ms-access
          > Sent: Thursday, April 22, 2004 11:38 AM
          > Subject: Re: rs.Edit instead of rs.AddNew
          >
          >[color=green]
          > > Dixie,
          > > You are looping through your selected items, but you are not looping[/color]
          > through[color=green]
          > > your recordset (although looping through the recordset is only going to[/color]
          > work[color=green]
          > > if the records are in the _exact_ same order as your listbox). rs(0) is[/color]
          > the[color=green]
          > > first record in the returned recordset. As you loop through the items,[/color]
          > you[color=green]
          > > are editing the same record.
          > >
          > > Instead, you want to select each record that cooresponds to each[/color][/color]
          selected[color=blue][color=green]
          > > listbox item and then update, or, pull all your records, find the one[/color][/color]
          that[color=blue][color=green]
          > > matches the listbox item in the loop and then update.
          > >
          > > Hope that gets you started.
          > > Pat
          > >
          > >
          > >
          > > "dixie" <dixiec@dogmail .com> wrote in message
          > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...[color=darkred]
          > > > I have some code that adds new records into a table for each ID in a[/color][/color]
          > list[color=green][color=darkred]
          > > > box when a button on a form is clicked. This works fine. My problem[/color][/color]
          > now[color=green]
          > > is[color=darkred]
          > > > that I wish to be able to edit all the records for people whose ID is[/color][/color][/color]
          in[color=blue][color=green]
          > > the[color=darkred]
          > > > list box. I made minor changes to the code (mainly replacing[/color][/color][/color]
          rs.AddNew[color=blue][color=green]
          > > with[color=darkred]
          > > > rs.Edit)and it appears to be updating only the first record and then
          > > > overwriting that record with the next, etc until it runs out of ID's[/color][/color][/color]
          in[color=blue][color=green]
          > > the[color=darkred]
          > > > list box. In other words, it is stepping through the ID's in the list[/color]
          > > box,[color=darkred]
          > > > but not the records. Is there a trick to this? I have spent many[/color][/color][/color]
          hours[color=blue][color=green][color=darkred]
          > > > doing minor changes and still have the same problem.
          > > >
          > > > The code follows (I have reduced the number of fields I am updating to[/color]
          > > keep[color=darkred]
          > > > the size of the message down).
          > > >
          > > > Dim intIndex As Integer
          > > >
          > > > For intIndex = 0 To Me.lboBulkList. ListCount
          > > >
          > > > Me.lbo.BulkList .Selected(intIn dex) = True
          > > >
          > > > Next intIndex
          > > >
          > > >
          > > >
          > > > Dim db As Database
          > > >
          > > > Dim rs As Recordset
          > > >
          > > > Dim prm As Parameter
          > > >
          > > > Dim qdf As QueryDef
          > > >
          > > >
          > > >
          > > > Set db = CurrentDb()
          > > >
          > > > Set qdf = db.QueryDefs("q ryBulkEdit")
          > > >
          > > >
          > > >
          > > > For Each prm In qdf.Parameters
          > > >
          > > > prm.Value = Eval(prm.Name)
          > > >
          > > > Next prm
          > > >
          > > >
          > > >
          > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
          > > >
          > > >
          > > >
          > > > Dim frm As Form
          > > >
          > > > Dim ctl As Control
          > > >
          > > > Dim varItm As Variant
          > > >
          > > >
          > > >
          > > > Set frm = Forms!frmExcurs ions
          > > >
          > > > Set ctl = frm! lboBulkList
          > > >
          > > > For Each varItm In ctl.ItemsSelect ed
          > > >
          > > >
          > > >
          > > > rs.Edit
          > > >
          > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
          > > >
          > > > rs!Date = frm!txtDate
          > > >
          > > > rs!Faculty = frm!cboFaculty
          > > >
          > > > rs!Course = frm!Course
          > > >
          > > > rs!Cost = frm!ExCost
          > > >
          > > > rs.Update
          > > >
          > > > Next varItm
          > > >
          > > > rs.Close: Set rs = Nothing
          > > >
          > > >
          > > > dixie
          > > >
          > > >[/color]
          > >
          > >[/color]
          >
          > "Pat" <noemail@ihates pam.bum> wrote in message
          > news:AmFhc.8861 $NR5.255@fe1.te xas.rr.com...[color=green]
          > > Dixie,
          > > You are looping through your selected items, but you are not looping[/color]
          > through[color=green]
          > > your recordset (although looping through the recordset is only going to[/color]
          > work[color=green]
          > > if the records are in the _exact_ same order as your listbox). rs(0) is[/color]
          > the[color=green]
          > > first record in the returned recordset. As you loop through the items,[/color]
          > you[color=green]
          > > are editing the same record.
          > >
          > > Instead, you want to select each record that cooresponds to each[/color][/color]
          selected[color=blue][color=green]
          > > listbox item and then update, or, pull all your records, find the one[/color][/color]
          that[color=blue][color=green]
          > > matches the listbox item in the loop and then update.
          > >
          > > Hope that gets you started.
          > > Pat
          > >
          > >
          > >
          > > "dixie" <dixiec@dogmail .com> wrote in message
          > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...[color=darkred]
          > > > I have some code that adds new records into a table for each ID in a[/color][/color]
          > list[color=green][color=darkred]
          > > > box when a button on a form is clicked. This works fine. My problem[/color][/color]
          > now[color=green]
          > > is[color=darkred]
          > > > that I wish to be able to edit all the records for people whose ID is[/color][/color][/color]
          in[color=blue][color=green]
          > > the[color=darkred]
          > > > list box. I made minor changes to the code (mainly replacing[/color][/color][/color]
          rs.AddNew[color=blue][color=green]
          > > with[color=darkred]
          > > > rs.Edit)and it appears to be updating only the first record and then
          > > > overwriting that record with the next, etc until it runs out of ID's[/color][/color][/color]
          in[color=blue][color=green]
          > > the[color=darkred]
          > > > list box. In other words, it is stepping through the ID's in the list[/color]
          > > box,[color=darkred]
          > > > but not the records. Is there a trick to this? I have spent many[/color][/color][/color]
          hours[color=blue][color=green][color=darkred]
          > > > doing minor changes and still have the same problem.
          > > >
          > > > The code follows (I have reduced the number of fields I am updating to[/color]
          > > keep[color=darkred]
          > > > the size of the message down).
          > > >
          > > > Dim intIndex As Integer
          > > >
          > > > For intIndex = 0 To Me.lboBulkList. ListCount
          > > >
          > > > Me.lbo.BulkList .Selected(intIn dex) = True
          > > >
          > > > Next intIndex
          > > >
          > > >
          > > >
          > > > Dim db As Database
          > > >
          > > > Dim rs As Recordset
          > > >
          > > > Dim prm As Parameter
          > > >
          > > > Dim qdf As QueryDef
          > > >
          > > >
          > > >
          > > > Set db = CurrentDb()
          > > >
          > > > Set qdf = db.QueryDefs("q ryBulkEdit")
          > > >
          > > >
          > > >
          > > > For Each prm In qdf.Parameters
          > > >
          > > > prm.Value = Eval(prm.Name)
          > > >
          > > > Next prm
          > > >
          > > >
          > > >
          > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
          > > >
          > > >
          > > >
          > > > Dim frm As Form
          > > >
          > > > Dim ctl As Control
          > > >
          > > > Dim varItm As Variant
          > > >
          > > >
          > > >
          > > > Set frm = Forms!frmExcurs ions
          > > >
          > > > Set ctl = frm! lboBulkList
          > > >
          > > > For Each varItm In ctl.ItemsSelect ed
          > > >
          > > >
          > > >
          > > > rs.Edit
          > > >
          > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
          > > >
          > > > rs!Date = frm!txtDate
          > > >
          > > > rs!Faculty = frm!cboFaculty
          > > >
          > > > rs!Course = frm!Course
          > > >
          > > > rs!Cost = frm!ExCost
          > > >
          > > > rs.Update
          > > >
          > > > Next varItm
          > > >
          > > > rs.Close: Set rs = Nothing
          > > >
          > > >
          > > > dixie
          > > >
          > > >[/color]
          > >
          > >[/color]
          >
          >[/color]


          Comment

          • dixie

            #6
            Re: rs.Edit instead of rs.AddNew

            Ok, tried that and strangely, I get exactly the same problem - that is, it
            updates the first record, but not the others. I did not understand your
            statement about you will need a loop to update each record in the set - is
            this the bit I have missed.

            The general idea is that I have a list of details for an excursion that a
            group of students are going on. I print a form out for each one and I print
            a list of students attending. Now, I already have that bit working. The
            bit I am trying to get here is if there was a mistake in the details or more
            information had come to hand, I wan't to be able to do a "bulk edit" on each
            entry (one per student). The ID is a unique student ID and there would be a
            number of them (up to 100) in the list box, lboBulkList.

            Now, this is the fine detail and is currently what I interpreted your
            previous post into.

            Private Sub btnEditTest_Cli ck()
            Dim db As Database
            Dim rs As Recordset
            Dim strSQL As String

            Dim frm As Form
            Dim ctl As Control
            Dim varItm As Variant

            Set frm = Forms!frmExcurs ions
            Set ctl = frm!lboBulkList

            For Each varItm In ctl.ItemsSelect ed
            strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm

            Set rs = CurrentDb.OpenR ecordset(strSQL )
            If rs.RecordCount > 0 Then
            rs!Date = frm!txtDate
            rs!ID_TCHR = frm!Teacher
            rs!Faculty = frm!cboFaculty
            rs!Course = frm!Course
            rs!ExcursionTyp e = frm!Reason
            rs!Details = frm!Comment
            rs!Signatory = frm!TxtSignator y
            rs!SignatoryAre a = frm!TxtSignator yArea
            rs!ExcursionNam e = frm!ExName
            rs!Cost = frm!ExCost
            rs!Destination = frm!ExcursionDe stination
            rs!DeparturePla ce = frm!DepartFrom
            rs!ReturningPla ce = frm!ReturnTo
            rs!DepartureTim e = frm!DepartTime
            rs!ReturningTim e = frm!ReturnTime
            rs!Representati ve = frm!Rep
            rs!Uniform = frm!Dress
            rs!Overnight = frm!Night
            rs!Commendation Required = frm!Commendatio n
            rs!Outdoors = frm!Outdoors
            rs!Travel = frm!TravelType
            rs!EntryDate = frm!txtEntryDat e
            rs!Edited = -1
            End If
            Next varItm
            End sub

            "Pat" <noemail@ihates pam.bum> wrote in message
            news:IjHhc.1170 8$hR1.10135@fe2 .texas.rr.com.. .[color=blue]
            > Dixie,
            > Your original post and your latest reply contains a loop that looks to[/color]
            have[color=blue]
            > originally been designed to loop through all records in a recordset and
            > update fields. From your description, you want to update only records
            > listed in a listbox, for which you have their ID.
            >
            > If your listbox will likely contain a few items, it could create a dynamic
            > SLQ statment that selects only the record you want to edit. Then you can
            > edit that record.
            >
            > Set frm = Forms!frmExcurs ions
            > Set ctl = frm! lboBulkList
            > For Each varItm In ctl.ItemsSelect ed
            > strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
            > Set rs = CurrentDb.OpenR ecordset(strSql )
            > If rs.RecordCount > 0 Then
            > rs.FieldNameToE dit = YourNewValue
            > End if
            > Next varItm
            >
            > If you have more than one record that will return in the recordset, you[/color]
            will[color=blue]
            > need a loop to handle update each record in the set.
            >
            > Hope this helps,
            > Pat
            >
            >
            >
            > "dixie" <dixiec@dogmail .com> wrote in message
            > news:gXFhc.17$A h5.1849@nnrp1.o zemail.com.au.. .[color=green]
            > > OK Pat, I thought as much. You have confirmed what I believed was
            > > happening. My problem is that I don't know how to combine the previous[/color]
            > type[color=green]
            > > of code that updates according to an ID in a list box and then this type[/color]
            > of[color=green]
            > > code
            > >
            > > With rs
            > >
            > > If rs.RecordCount > 0 Then
            > > .MoveFirst
            > > Do
            > > .Edit
            > > rs!Cost = 0
            > > .Update
            > > .MoveNext
            > > Loop Until .EOF
            > > End If
            > > .Close: Set rs = Nothing
            > >
            > > End With
            > >
            > > I know that I need to step through the records in the recordset and edit
            > > each field according to my ID in the list box, but I can't seem to[/color][/color]
            combine[color=blue][color=green]
            > > the two ideas to get to the solution I want - which is for each person
            > > selected to have their specific record edited according to a what is in
            > > various controls on the form.
            > >
            > > dixie
            > >
            > > ----- Original Message -----
            > > From: "Pat" <noemail@ihates pam.bum>
            > > Newsgroups: comp.databases. ms-access
            > > Sent: Thursday, April 22, 2004 11:38 AM
            > > Subject: Re: rs.Edit instead of rs.AddNew
            > >
            > >[color=darkred]
            > > > Dixie,
            > > > You are looping through your selected items, but you are not looping[/color]
            > > through[color=darkred]
            > > > your recordset (although looping through the recordset is only going[/color][/color][/color]
            to[color=blue][color=green]
            > > work[color=darkred]
            > > > if the records are in the _exact_ same order as your listbox). rs(0)[/color][/color][/color]
            is[color=blue][color=green]
            > > the[color=darkred]
            > > > first record in the returned recordset. As you loop through the[/color][/color][/color]
            items,[color=blue][color=green]
            > > you[color=darkred]
            > > > are editing the same record.
            > > >
            > > > Instead, you want to select each record that cooresponds to each[/color][/color]
            > selected[color=green][color=darkred]
            > > > listbox item and then update, or, pull all your records, find the one[/color][/color]
            > that[color=green][color=darkred]
            > > > matches the listbox item in the loop and then update.
            > > >
            > > > Hope that gets you started.
            > > > Pat
            > > >
            > > >
            > > >
            > > > "dixie" <dixiec@dogmail .com> wrote in message
            > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
            > > > > I have some code that adds new records into a table for each ID in a[/color]
            > > list[color=darkred]
            > > > > box when a button on a form is clicked. This works fine. My[/color][/color][/color]
            problem[color=blue][color=green]
            > > now[color=darkred]
            > > > is
            > > > > that I wish to be able to edit all the records for people whose ID[/color][/color][/color]
            is[color=blue]
            > in[color=green][color=darkred]
            > > > the
            > > > > list box. I made minor changes to the code (mainly replacing[/color][/color]
            > rs.AddNew[color=green][color=darkred]
            > > > with
            > > > > rs.Edit)and it appears to be updating only the first record and then
            > > > > overwriting that record with the next, etc until it runs out of ID's[/color][/color]
            > in[color=green][color=darkred]
            > > > the
            > > > > list box. In other words, it is stepping through the ID's in the[/color][/color][/color]
            list[color=blue][color=green][color=darkred]
            > > > box,
            > > > > but not the records. Is there a trick to this? I have spent many[/color][/color]
            > hours[color=green][color=darkred]
            > > > > doing minor changes and still have the same problem.
            > > > >
            > > > > The code follows (I have reduced the number of fields I am updating[/color][/color][/color]
            to[color=blue][color=green][color=darkred]
            > > > keep
            > > > > the size of the message down).
            > > > >
            > > > > Dim intIndex As Integer
            > > > >
            > > > > For intIndex = 0 To Me.lboBulkList. ListCount
            > > > >
            > > > > Me.lbo.BulkList .Selected(intIn dex) = True
            > > > >
            > > > > Next intIndex
            > > > >
            > > > >
            > > > >
            > > > > Dim db As Database
            > > > >
            > > > > Dim rs As Recordset
            > > > >
            > > > > Dim prm As Parameter
            > > > >
            > > > > Dim qdf As QueryDef
            > > > >
            > > > >
            > > > >
            > > > > Set db = CurrentDb()
            > > > >
            > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
            > > > >
            > > > >
            > > > >
            > > > > For Each prm In qdf.Parameters
            > > > >
            > > > > prm.Value = Eval(prm.Name)
            > > > >
            > > > > Next prm
            > > > >
            > > > >
            > > > >
            > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
            > > > >
            > > > >
            > > > >
            > > > > Dim frm As Form
            > > > >
            > > > > Dim ctl As Control
            > > > >
            > > > > Dim varItm As Variant
            > > > >
            > > > >
            > > > >
            > > > > Set frm = Forms!frmExcurs ions
            > > > >
            > > > > Set ctl = frm! lboBulkList
            > > > >
            > > > > For Each varItm In ctl.ItemsSelect ed
            > > > >
            > > > >
            > > > >
            > > > > rs.Edit
            > > > >
            > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
            > > > >
            > > > > rs!Date = frm!txtDate
            > > > >
            > > > > rs!Faculty = frm!cboFaculty
            > > > >
            > > > > rs!Course = frm!Course
            > > > >
            > > > > rs!Cost = frm!ExCost
            > > > >
            > > > > rs.Update
            > > > >
            > > > > Next varItm
            > > > >
            > > > > rs.Close: Set rs = Nothing
            > > > >
            > > > >
            > > > > dixie
            > > > >
            > > > >
            > > >
            > > >[/color]
            > >
            > > "Pat" <noemail@ihates pam.bum> wrote in message
            > > news:AmFhc.8861 $NR5.255@fe1.te xas.rr.com...[color=darkred]
            > > > Dixie,
            > > > You are looping through your selected items, but you are not looping[/color]
            > > through[color=darkred]
            > > > your recordset (although looping through the recordset is only going[/color][/color][/color]
            to[color=blue][color=green]
            > > work[color=darkred]
            > > > if the records are in the _exact_ same order as your listbox). rs(0)[/color][/color][/color]
            is[color=blue][color=green]
            > > the[color=darkred]
            > > > first record in the returned recordset. As you loop through the[/color][/color][/color]
            items,[color=blue][color=green]
            > > you[color=darkred]
            > > > are editing the same record.
            > > >
            > > > Instead, you want to select each record that cooresponds to each[/color][/color]
            > selected[color=green][color=darkred]
            > > > listbox item and then update, or, pull all your records, find the one[/color][/color]
            > that[color=green][color=darkred]
            > > > matches the listbox item in the loop and then update.
            > > >
            > > > Hope that gets you started.
            > > > Pat
            > > >
            > > >
            > > >
            > > > "dixie" <dixiec@dogmail .com> wrote in message
            > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
            > > > > I have some code that adds new records into a table for each ID in a[/color]
            > > list[color=darkred]
            > > > > box when a button on a form is clicked. This works fine. My[/color][/color][/color]
            problem[color=blue][color=green]
            > > now[color=darkred]
            > > > is
            > > > > that I wish to be able to edit all the records for people whose ID[/color][/color][/color]
            is[color=blue]
            > in[color=green][color=darkred]
            > > > the
            > > > > list box. I made minor changes to the code (mainly replacing[/color][/color]
            > rs.AddNew[color=green][color=darkred]
            > > > with
            > > > > rs.Edit)and it appears to be updating only the first record and then
            > > > > overwriting that record with the next, etc until it runs out of ID's[/color][/color]
            > in[color=green][color=darkred]
            > > > the
            > > > > list box. In other words, it is stepping through the ID's in the[/color][/color][/color]
            list[color=blue][color=green][color=darkred]
            > > > box,
            > > > > but not the records. Is there a trick to this? I have spent many[/color][/color]
            > hours[color=green][color=darkred]
            > > > > doing minor changes and still have the same problem.
            > > > >
            > > > > The code follows (I have reduced the number of fields I am updating[/color][/color][/color]
            to[color=blue][color=green][color=darkred]
            > > > keep
            > > > > the size of the message down).
            > > > >
            > > > > Dim intIndex As Integer
            > > > >
            > > > > For intIndex = 0 To Me.lboBulkList. ListCount
            > > > >
            > > > > Me.lbo.BulkList .Selected(intIn dex) = True
            > > > >
            > > > > Next intIndex
            > > > >
            > > > >
            > > > >
            > > > > Dim db As Database
            > > > >
            > > > > Dim rs As Recordset
            > > > >
            > > > > Dim prm As Parameter
            > > > >
            > > > > Dim qdf As QueryDef
            > > > >
            > > > >
            > > > >
            > > > > Set db = CurrentDb()
            > > > >
            > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
            > > > >
            > > > >
            > > > >
            > > > > For Each prm In qdf.Parameters
            > > > >
            > > > > prm.Value = Eval(prm.Name)
            > > > >
            > > > > Next prm
            > > > >
            > > > >
            > > > >
            > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
            > > > >
            > > > >
            > > > >
            > > > > Dim frm As Form
            > > > >
            > > > > Dim ctl As Control
            > > > >
            > > > > Dim varItm As Variant
            > > > >
            > > > >
            > > > >
            > > > > Set frm = Forms!frmExcurs ions
            > > > >
            > > > > Set ctl = frm! lboBulkList
            > > > >
            > > > > For Each varItm In ctl.ItemsSelect ed
            > > > >
            > > > >
            > > > >
            > > > > rs.Edit
            > > > >
            > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
            > > > >
            > > > > rs!Date = frm!txtDate
            > > > >
            > > > > rs!Faculty = frm!cboFaculty
            > > > >
            > > > > rs!Course = frm!Course
            > > > >
            > > > > rs!Cost = frm!ExCost
            > > > >
            > > > > rs.Update
            > > > >
            > > > > Next varItm
            > > > >
            > > > > rs.Close: Set rs = Nothing
            > > > >
            > > > >
            > > > > dixie
            > > > >
            > > > >
            > > >
            > > >[/color]
            > >
            > >[/color]
            >
            >[/color]


            Comment

            • Pat

              #7
              Re: rs.Edit instead of rs.AddNew

              Dixie,
              It's hard to help too much without knowing what's in your listbox. Since
              the dynamic SQL statement seems to be returning the same record, it would be
              best to see what you are returning from the listbox. Use debug.print to
              display your items in the listbox and the fields in the record you are
              returning within the Immediate window.

              For Each varItm In ctl.ItemsSelect ed
              strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
              Set rs = CurrentDb.OpenR ecordset(strSQL )
              Debug.Print varItem
              Debug.Print Field1, Field2, Field3, etc

              I suspect that you have multiple columns in your listbox. This may be
              causing the problem - returning the same value from the listbox for each
              item selected, and thus retrieving the same record. If there are multiple
              columns, look at ItemsSelected in the help file. It will give you a sense
              of how to pull the right column of data you want to build your SQL statement
              with.

              Hope this helps,
              Pat



              "dixie" <dixiec@dogmail .com> wrote in message
              news:hlIhc.38$A h5.2978@nnrp1.o zemail.com.au.. .[color=blue]
              > Ok, tried that and strangely, I get exactly the same problem - that is, it
              > updates the first record, but not the others. I did not understand your
              > statement about you will need a loop to update each record in the set - is
              > this the bit I have missed.
              >
              > The general idea is that I have a list of details for an excursion that a
              > group of students are going on. I print a form out for each one and I[/color]
              print[color=blue]
              > a list of students attending. Now, I already have that bit working. The
              > bit I am trying to get here is if there was a mistake in the details or[/color]
              more[color=blue]
              > information had come to hand, I wan't to be able to do a "bulk edit" on[/color]
              each[color=blue]
              > entry (one per student). The ID is a unique student ID and there would be[/color]
              a[color=blue]
              > number of them (up to 100) in the list box, lboBulkList.
              >
              > Now, this is the fine detail and is currently what I interpreted your
              > previous post into.
              >
              > Private Sub btnEditTest_Cli ck()
              > Dim db As Database
              > Dim rs As Recordset
              > Dim strSQL As String
              >
              > Dim frm As Form
              > Dim ctl As Control
              > Dim varItm As Variant
              >
              > Set frm = Forms!frmExcurs ions
              > Set ctl = frm!lboBulkList
              >
              > For Each varItm In ctl.ItemsSelect ed
              > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
              >
              > Set rs = CurrentDb.OpenR ecordset(strSQL )
              > If rs.RecordCount > 0 Then
              > rs!Date = frm!txtDate
              > rs!ID_TCHR = frm!Teacher
              > rs!Faculty = frm!cboFaculty
              > rs!Course = frm!Course
              > rs!ExcursionTyp e = frm!Reason
              > rs!Details = frm!Comment
              > rs!Signatory = frm!TxtSignator y
              > rs!SignatoryAre a = frm!TxtSignator yArea
              > rs!ExcursionNam e = frm!ExName
              > rs!Cost = frm!ExCost
              > rs!Destination = frm!ExcursionDe stination
              > rs!DeparturePla ce = frm!DepartFrom
              > rs!ReturningPla ce = frm!ReturnTo
              > rs!DepartureTim e = frm!DepartTime
              > rs!ReturningTim e = frm!ReturnTime
              > rs!Representati ve = frm!Rep
              > rs!Uniform = frm!Dress
              > rs!Overnight = frm!Night
              > rs!Commendation Required = frm!Commendatio n
              > rs!Outdoors = frm!Outdoors
              > rs!Travel = frm!TravelType
              > rs!EntryDate = frm!txtEntryDat e
              > rs!Edited = -1
              > End If
              > Next varItm
              > End sub
              >
              > "Pat" <noemail@ihates pam.bum> wrote in message
              > news:IjHhc.1170 8$hR1.10135@fe2 .texas.rr.com.. .[color=green]
              > > Dixie,
              > > Your original post and your latest reply contains a loop that looks to[/color]
              > have[color=green]
              > > originally been designed to loop through all records in a recordset and
              > > update fields. From your description, you want to update only records
              > > listed in a listbox, for which you have their ID.
              > >
              > > If your listbox will likely contain a few items, it could create a[/color][/color]
              dynamic[color=blue][color=green]
              > > SLQ statment that selects only the record you want to edit. Then you[/color][/color]
              can[color=blue][color=green]
              > > edit that record.
              > >
              > > Set frm = Forms!frmExcurs ions
              > > Set ctl = frm! lboBulkList
              > > For Each varItm In ctl.ItemsSelect ed
              > > strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
              > > Set rs = CurrentDb.OpenR ecordset(strSql )
              > > If rs.RecordCount > 0 Then
              > > rs.FieldNameToE dit = YourNewValue
              > > End if
              > > Next varItm
              > >
              > > If you have more than one record that will return in the recordset, you[/color]
              > will[color=green]
              > > need a loop to handle update each record in the set.
              > >
              > > Hope this helps,
              > > Pat
              > >
              > >
              > >
              > > "dixie" <dixiec@dogmail .com> wrote in message
              > > news:gXFhc.17$A h5.1849@nnrp1.o zemail.com.au.. .[color=darkred]
              > > > OK Pat, I thought as much. You have confirmed what I believed was
              > > > happening. My problem is that I don't know how to combine the[/color][/color][/color]
              previous[color=blue][color=green]
              > > type[color=darkred]
              > > > of code that updates according to an ID in a list box and then this[/color][/color][/color]
              type[color=blue][color=green]
              > > of[color=darkred]
              > > > code
              > > >
              > > > With rs
              > > >
              > > > If rs.RecordCount > 0 Then
              > > > .MoveFirst
              > > > Do
              > > > .Edit
              > > > rs!Cost = 0
              > > > .Update
              > > > .MoveNext
              > > > Loop Until .EOF
              > > > End If
              > > > .Close: Set rs = Nothing
              > > >
              > > > End With
              > > >
              > > > I know that I need to step through the records in the recordset and[/color][/color][/color]
              edit[color=blue][color=green][color=darkred]
              > > > each field according to my ID in the list box, but I can't seem to[/color][/color]
              > combine[color=green][color=darkred]
              > > > the two ideas to get to the solution I want - which is for each person
              > > > selected to have their specific record edited according to a what is[/color][/color][/color]
              in[color=blue][color=green][color=darkred]
              > > > various controls on the form.
              > > >
              > > > dixie
              > > >
              > > > ----- Original Message -----
              > > > From: "Pat" <noemail@ihates pam.bum>
              > > > Newsgroups: comp.databases. ms-access
              > > > Sent: Thursday, April 22, 2004 11:38 AM
              > > > Subject: Re: rs.Edit instead of rs.AddNew
              > > >
              > > >
              > > > > Dixie,
              > > > > You are looping through your selected items, but you are not looping
              > > > through
              > > > > your recordset (although looping through the recordset is only going[/color][/color]
              > to[color=green][color=darkred]
              > > > work
              > > > > if the records are in the _exact_ same order as your listbox). rs(0)[/color][/color]
              > is[color=green][color=darkred]
              > > > the
              > > > > first record in the returned recordset. As you loop through the[/color][/color]
              > items,[color=green][color=darkred]
              > > > you
              > > > > are editing the same record.
              > > > >
              > > > > Instead, you want to select each record that cooresponds to each[/color]
              > > selected[color=darkred]
              > > > > listbox item and then update, or, pull all your records, find the[/color][/color][/color]
              one[color=blue][color=green]
              > > that[color=darkred]
              > > > > matches the listbox item in the loop and then update.
              > > > >
              > > > > Hope that gets you started.
              > > > > Pat
              > > > >
              > > > >
              > > > >
              > > > > "dixie" <dixiec@dogmail .com> wrote in message
              > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
              > > > > > I have some code that adds new records into a table for each ID in[/color][/color][/color]
              a[color=blue][color=green][color=darkred]
              > > > list
              > > > > > box when a button on a form is clicked. This works fine. My[/color][/color]
              > problem[color=green][color=darkred]
              > > > now
              > > > > is
              > > > > > that I wish to be able to edit all the records for people whose ID[/color][/color]
              > is[color=green]
              > > in[color=darkred]
              > > > > the
              > > > > > list box. I made minor changes to the code (mainly replacing[/color]
              > > rs.AddNew[color=darkred]
              > > > > with
              > > > > > rs.Edit)and it appears to be updating only the first record and[/color][/color][/color]
              then[color=blue][color=green][color=darkred]
              > > > > > overwriting that record with the next, etc until it runs out of[/color][/color][/color]
              ID's[color=blue][color=green]
              > > in[color=darkred]
              > > > > the
              > > > > > list box. In other words, it is stepping through the ID's in the[/color][/color]
              > list[color=green][color=darkred]
              > > > > box,
              > > > > > but not the records. Is there a trick to this? I have spent many[/color]
              > > hours[color=darkred]
              > > > > > doing minor changes and still have the same problem.
              > > > > >
              > > > > > The code follows (I have reduced the number of fields I am[/color][/color][/color]
              updating[color=blue]
              > to[color=green][color=darkred]
              > > > > keep
              > > > > > the size of the message down).
              > > > > >
              > > > > > Dim intIndex As Integer
              > > > > >
              > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
              > > > > >
              > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
              > > > > >
              > > > > > Next intIndex
              > > > > >
              > > > > >
              > > > > >
              > > > > > Dim db As Database
              > > > > >
              > > > > > Dim rs As Recordset
              > > > > >
              > > > > > Dim prm As Parameter
              > > > > >
              > > > > > Dim qdf As QueryDef
              > > > > >
              > > > > >
              > > > > >
              > > > > > Set db = CurrentDb()
              > > > > >
              > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
              > > > > >
              > > > > >
              > > > > >
              > > > > > For Each prm In qdf.Parameters
              > > > > >
              > > > > > prm.Value = Eval(prm.Name)
              > > > > >
              > > > > > Next prm
              > > > > >
              > > > > >
              > > > > >
              > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
              > > > > >
              > > > > >
              > > > > >
              > > > > > Dim frm As Form
              > > > > >
              > > > > > Dim ctl As Control
              > > > > >
              > > > > > Dim varItm As Variant
              > > > > >
              > > > > >
              > > > > >
              > > > > > Set frm = Forms!frmExcurs ions
              > > > > >
              > > > > > Set ctl = frm! lboBulkList
              > > > > >
              > > > > > For Each varItm In ctl.ItemsSelect ed
              > > > > >
              > > > > >
              > > > > >
              > > > > > rs.Edit
              > > > > >
              > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
              > > > > >
              > > > > > rs!Date = frm!txtDate
              > > > > >
              > > > > > rs!Faculty = frm!cboFaculty
              > > > > >
              > > > > > rs!Course = frm!Course
              > > > > >
              > > > > > rs!Cost = frm!ExCost
              > > > > >
              > > > > > rs.Update
              > > > > >
              > > > > > Next varItm
              > > > > >
              > > > > > rs.Close: Set rs = Nothing
              > > > > >
              > > > > >
              > > > > > dixie
              > > > > >
              > > > > >
              > > > >
              > > > >
              > > >
              > > > "Pat" <noemail@ihates pam.bum> wrote in message
              > > > news:AmFhc.8861 $NR5.255@fe1.te xas.rr.com...
              > > > > Dixie,
              > > > > You are looping through your selected items, but you are not looping
              > > > through
              > > > > your recordset (although looping through the recordset is only going[/color][/color]
              > to[color=green][color=darkred]
              > > > work
              > > > > if the records are in the _exact_ same order as your listbox). rs(0)[/color][/color]
              > is[color=green][color=darkred]
              > > > the
              > > > > first record in the returned recordset. As you loop through the[/color][/color]
              > items,[color=green][color=darkred]
              > > > you
              > > > > are editing the same record.
              > > > >
              > > > > Instead, you want to select each record that cooresponds to each[/color]
              > > selected[color=darkred]
              > > > > listbox item and then update, or, pull all your records, find the[/color][/color][/color]
              one[color=blue][color=green]
              > > that[color=darkred]
              > > > > matches the listbox item in the loop and then update.
              > > > >
              > > > > Hope that gets you started.
              > > > > Pat
              > > > >
              > > > >
              > > > >
              > > > > "dixie" <dixiec@dogmail .com> wrote in message
              > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
              > > > > > I have some code that adds new records into a table for each ID in[/color][/color][/color]
              a[color=blue][color=green][color=darkred]
              > > > list
              > > > > > box when a button on a form is clicked. This works fine. My[/color][/color]
              > problem[color=green][color=darkred]
              > > > now
              > > > > is
              > > > > > that I wish to be able to edit all the records for people whose ID[/color][/color]
              > is[color=green]
              > > in[color=darkred]
              > > > > the
              > > > > > list box. I made minor changes to the code (mainly replacing[/color]
              > > rs.AddNew[color=darkred]
              > > > > with
              > > > > > rs.Edit)and it appears to be updating only the first record and[/color][/color][/color]
              then[color=blue][color=green][color=darkred]
              > > > > > overwriting that record with the next, etc until it runs out of[/color][/color][/color]
              ID's[color=blue][color=green]
              > > in[color=darkred]
              > > > > the
              > > > > > list box. In other words, it is stepping through the ID's in the[/color][/color]
              > list[color=green][color=darkred]
              > > > > box,
              > > > > > but not the records. Is there a trick to this? I have spent many[/color]
              > > hours[color=darkred]
              > > > > > doing minor changes and still have the same problem.
              > > > > >
              > > > > > The code follows (I have reduced the number of fields I am[/color][/color][/color]
              updating[color=blue]
              > to[color=green][color=darkred]
              > > > > keep
              > > > > > the size of the message down).
              > > > > >
              > > > > > Dim intIndex As Integer
              > > > > >
              > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
              > > > > >
              > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
              > > > > >
              > > > > > Next intIndex
              > > > > >
              > > > > >
              > > > > >
              > > > > > Dim db As Database
              > > > > >
              > > > > > Dim rs As Recordset
              > > > > >
              > > > > > Dim prm As Parameter
              > > > > >
              > > > > > Dim qdf As QueryDef
              > > > > >
              > > > > >
              > > > > >
              > > > > > Set db = CurrentDb()
              > > > > >
              > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
              > > > > >
              > > > > >
              > > > > >
              > > > > > For Each prm In qdf.Parameters
              > > > > >
              > > > > > prm.Value = Eval(prm.Name)
              > > > > >
              > > > > > Next prm
              > > > > >
              > > > > >
              > > > > >
              > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
              > > > > >
              > > > > >
              > > > > >
              > > > > > Dim frm As Form
              > > > > >
              > > > > > Dim ctl As Control
              > > > > >
              > > > > > Dim varItm As Variant
              > > > > >
              > > > > >
              > > > > >
              > > > > > Set frm = Forms!frmExcurs ions
              > > > > >
              > > > > > Set ctl = frm! lboBulkList
              > > > > >
              > > > > > For Each varItm In ctl.ItemsSelect ed
              > > > > >
              > > > > >
              > > > > >
              > > > > > rs.Edit
              > > > > >
              > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
              > > > > >
              > > > > > rs!Date = frm!txtDate
              > > > > >
              > > > > > rs!Faculty = frm!cboFaculty
              > > > > >
              > > > > > rs!Course = frm!Course
              > > > > >
              > > > > > rs!Cost = frm!ExCost
              > > > > >
              > > > > > rs.Update
              > > > > >
              > > > > > Next varItm
              > > > > >
              > > > > > rs.Close: Set rs = Nothing
              > > > > >
              > > > > >
              > > > > > dixie
              > > > > >
              > > > > >
              > > > >
              > > > >
              > > >
              > > >[/color]
              > >
              > >[/color]
              >
              >[/color]


              Comment

              • Joe Black

                #8
                Re: rs.Edit instead of rs.AddNew

                Hi Dixie

                In your first version of the code you first select all items in the listbox
                and then iterate through all the selected items.
                If you want to process all the list items, it is not necessary to select
                them all first.

                Instead of "For Each varItm In ctl.ItemsSelect ed"
                you can do something like:

                Dim i As Integer
                For i = 0 To Me.lboBulkList. ListCount - 1

                strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" &
                Me.lboBulkList. ItemData(0)
                Set rs = CurrentDb.OpenR ecordset(strSQL )
                If rs.RecordCount > 0 Then
                rs!Date = frm!txtDate
                ...
                end if
                Next i

                You haven't allowed for the possibility that rs.RecordCount might be > 1.

                I also wonder whether the design of your database could be improved. If the
                details of an Excursion are needed to be changed, you should only have to
                change the details in one row of a table, not repeatedly change the same
                details for many rows.

                Regards - Joe

                "dixie" <dixiec@dogmail .com> wrote in message
                news:hlIhc.38$A h5.2978@nnrp1.o zemail.com.au.. .[color=blue]
                > Ok, tried that and strangely, I get exactly the same problem - that is, it
                > updates the first record, but not the others. I did not understand your
                > statement about you will need a loop to update each record in the set - is
                > this the bit I have missed.
                >
                > The general idea is that I have a list of details for an excursion that a
                > group of students are going on. I print a form out for each one and I[/color]
                print[color=blue]
                > a list of students attending. Now, I already have that bit working. The
                > bit I am trying to get here is if there was a mistake in the details or[/color]
                more[color=blue]
                > information had come to hand, I wan't to be able to do a "bulk edit" on[/color]
                each[color=blue]
                > entry (one per student). The ID is a unique student ID and there would be[/color]
                a[color=blue]
                > number of them (up to 100) in the list box, lboBulkList.
                >
                > Now, this is the fine detail and is currently what I interpreted your
                > previous post into.
                >
                > Private Sub btnEditTest_Cli ck()
                > Dim db As Database
                > Dim rs As Recordset
                > Dim strSQL As String
                >
                > Dim frm As Form
                > Dim ctl As Control
                > Dim varItm As Variant
                >
                > Set frm = Forms!frmExcurs ions
                > Set ctl = frm!lboBulkList
                >
                > For Each varItm In ctl.ItemsSelect ed
                > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
                >
                > Set rs = CurrentDb.OpenR ecordset(strSQL )
                > If rs.RecordCount > 0 Then
                > rs!Date = frm!txtDate
                > rs!ID_TCHR = frm!Teacher
                > rs!Faculty = frm!cboFaculty
                > rs!Course = frm!Course
                > rs!ExcursionTyp e = frm!Reason
                > rs!Details = frm!Comment
                > rs!Signatory = frm!TxtSignator y
                > rs!SignatoryAre a = frm!TxtSignator yArea
                > rs!ExcursionNam e = frm!ExName
                > rs!Cost = frm!ExCost
                > rs!Destination = frm!ExcursionDe stination
                > rs!DeparturePla ce = frm!DepartFrom
                > rs!ReturningPla ce = frm!ReturnTo
                > rs!DepartureTim e = frm!DepartTime
                > rs!ReturningTim e = frm!ReturnTime
                > rs!Representati ve = frm!Rep
                > rs!Uniform = frm!Dress
                > rs!Overnight = frm!Night
                > rs!Commendation Required = frm!Commendatio n
                > rs!Outdoors = frm!Outdoors
                > rs!Travel = frm!TravelType
                > rs!EntryDate = frm!txtEntryDat e
                > rs!Edited = -1
                > End If
                > Next varItm
                > End sub
                >
                > "Pat" <noemail@ihates pam.bum> wrote in message
                > news:IjHhc.1170 8$hR1.10135@fe2 .texas.rr.com.. .[color=green]
                > > Dixie,
                > > Your original post and your latest reply contains a loop that looks to[/color]
                > have[color=green]
                > > originally been designed to loop through all records in a recordset and
                > > update fields. From your description, you want to update only records
                > > listed in a listbox, for which you have their ID.
                > >
                > > If your listbox will likely contain a few items, it could create a[/color][/color]
                dynamic[color=blue][color=green]
                > > SLQ statment that selects only the record you want to edit. Then you[/color][/color]
                can[color=blue][color=green]
                > > edit that record.
                > >
                > > Set frm = Forms!frmExcurs ions
                > > Set ctl = frm! lboBulkList
                > > For Each varItm In ctl.ItemsSelect ed
                > > strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
                > > Set rs = CurrentDb.OpenR ecordset(strSql )
                > > If rs.RecordCount > 0 Then
                > > rs.FieldNameToE dit = YourNewValue
                > > End if
                > > Next varItm
                > >
                > > If you have more than one record that will return in the recordset, you[/color]
                > will[color=green]
                > > need a loop to handle update each record in the set.
                > >
                > > Hope this helps,
                > > Pat
                > >
                > >
                > >
                > > "dixie" <dixiec@dogmail .com> wrote in message
                > > news:gXFhc.17$A h5.1849@nnrp1.o zemail.com.au.. .[color=darkred]
                > > > OK Pat, I thought as much. You have confirmed what I believed was
                > > > happening. My problem is that I don't know how to combine the[/color][/color][/color]
                previous[color=blue][color=green]
                > > type[color=darkred]
                > > > of code that updates according to an ID in a list box and then this[/color][/color][/color]
                type[color=blue][color=green]
                > > of[color=darkred]
                > > > code
                > > >
                > > > With rs
                > > >
                > > > If rs.RecordCount > 0 Then
                > > > .MoveFirst
                > > > Do
                > > > .Edit
                > > > rs!Cost = 0
                > > > .Update
                > > > .MoveNext
                > > > Loop Until .EOF
                > > > End If
                > > > .Close: Set rs = Nothing
                > > >
                > > > End With
                > > >
                > > > I know that I need to step through the records in the recordset and[/color][/color][/color]
                edit[color=blue][color=green][color=darkred]
                > > > each field according to my ID in the list box, but I can't seem to[/color][/color]
                > combine[color=green][color=darkred]
                > > > the two ideas to get to the solution I want - which is for each person
                > > > selected to have their specific record edited according to a what is[/color][/color][/color]
                in[color=blue][color=green][color=darkred]
                > > > various controls on the form.
                > > >
                > > > dixie
                > > >
                > > > ----- Original Message -----
                > > > From: "Pat" <noemail@ihates pam.bum>
                > > > Newsgroups: comp.databases. ms-access
                > > > Sent: Thursday, April 22, 2004 11:38 AM
                > > > Subject: Re: rs.Edit instead of rs.AddNew
                > > >
                > > >
                > > > > Dixie,
                > > > > You are looping through your selected items, but you are not looping
                > > > through
                > > > > your recordset (although looping through the recordset is only going[/color][/color]
                > to[color=green][color=darkred]
                > > > work
                > > > > if the records are in the _exact_ same order as your listbox). rs(0)[/color][/color]
                > is[color=green][color=darkred]
                > > > the
                > > > > first record in the returned recordset. As you loop through the[/color][/color]
                > items,[color=green][color=darkred]
                > > > you
                > > > > are editing the same record.
                > > > >
                > > > > Instead, you want to select each record that cooresponds to each[/color]
                > > selected[color=darkred]
                > > > > listbox item and then update, or, pull all your records, find the[/color][/color][/color]
                one[color=blue][color=green]
                > > that[color=darkred]
                > > > > matches the listbox item in the loop and then update.
                > > > >
                > > > > Hope that gets you started.
                > > > > Pat
                > > > >
                > > > >
                > > > >
                > > > > "dixie" <dixiec@dogmail .com> wrote in message
                > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
                > > > > > I have some code that adds new records into a table for each ID in[/color][/color][/color]
                a[color=blue][color=green][color=darkred]
                > > > list
                > > > > > box when a button on a form is clicked. This works fine. My[/color][/color]
                > problem[color=green][color=darkred]
                > > > now
                > > > > is
                > > > > > that I wish to be able to edit all the records for people whose ID[/color][/color]
                > is[color=green]
                > > in[color=darkred]
                > > > > the
                > > > > > list box. I made minor changes to the code (mainly replacing[/color]
                > > rs.AddNew[color=darkred]
                > > > > with
                > > > > > rs.Edit)and it appears to be updating only the first record and[/color][/color][/color]
                then[color=blue][color=green][color=darkred]
                > > > > > overwriting that record with the next, etc until it runs out of[/color][/color][/color]
                ID's[color=blue][color=green]
                > > in[color=darkred]
                > > > > the
                > > > > > list box. In other words, it is stepping through the ID's in the[/color][/color]
                > list[color=green][color=darkred]
                > > > > box,
                > > > > > but not the records. Is there a trick to this? I have spent many[/color]
                > > hours[color=darkred]
                > > > > > doing minor changes and still have the same problem.
                > > > > >
                > > > > > The code follows (I have reduced the number of fields I am[/color][/color][/color]
                updating[color=blue]
                > to[color=green][color=darkred]
                > > > > keep
                > > > > > the size of the message down).
                > > > > >
                > > > > > Dim intIndex As Integer
                > > > > >
                > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
                > > > > >
                > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
                > > > > >
                > > > > > Next intIndex
                > > > > >
                > > > > >
                > > > > >
                > > > > > Dim db As Database
                > > > > >
                > > > > > Dim rs As Recordset
                > > > > >
                > > > > > Dim prm As Parameter
                > > > > >
                > > > > > Dim qdf As QueryDef
                > > > > >
                > > > > >
                > > > > >
                > > > > > Set db = CurrentDb()
                > > > > >
                > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
                > > > > >
                > > > > >
                > > > > >
                > > > > > For Each prm In qdf.Parameters
                > > > > >
                > > > > > prm.Value = Eval(prm.Name)
                > > > > >
                > > > > > Next prm
                > > > > >
                > > > > >
                > > > > >
                > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
                > > > > >
                > > > > >
                > > > > >
                > > > > > Dim frm As Form
                > > > > >
                > > > > > Dim ctl As Control
                > > > > >
                > > > > > Dim varItm As Variant
                > > > > >
                > > > > >
                > > > > >
                > > > > > Set frm = Forms!frmExcurs ions
                > > > > >
                > > > > > Set ctl = frm! lboBulkList
                > > > > >
                > > > > > For Each varItm In ctl.ItemsSelect ed
                > > > > >
                > > > > >
                > > > > >
                > > > > > rs.Edit
                > > > > >
                > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
                > > > > >
                > > > > > rs!Date = frm!txtDate
                > > > > >
                > > > > > rs!Faculty = frm!cboFaculty
                > > > > >
                > > > > > rs!Course = frm!Course
                > > > > >
                > > > > > rs!Cost = frm!ExCost
                > > > > >
                > > > > > rs.Update
                > > > > >
                > > > > > Next varItm
                > > > > >
                > > > > > rs.Close: Set rs = Nothing
                > > > > >
                > > > > >
                > > > > > dixie
                > > > > >
                > > > > >
                > > > >
                > > > >
                > > >
                > > > "Pat" <noemail@ihates pam.bum> wrote in message
                > > > news:AmFhc.8861 $NR5.255@fe1.te xas.rr.com...
                > > > > Dixie,
                > > > > You are looping through your selected items, but you are not looping
                > > > through
                > > > > your recordset (although looping through the recordset is only going[/color][/color]
                > to[color=green][color=darkred]
                > > > work
                > > > > if the records are in the _exact_ same order as your listbox). rs(0)[/color][/color]
                > is[color=green][color=darkred]
                > > > the
                > > > > first record in the returned recordset. As you loop through the[/color][/color]
                > items,[color=green][color=darkred]
                > > > you
                > > > > are editing the same record.
                > > > >
                > > > > Instead, you want to select each record that cooresponds to each[/color]
                > > selected[color=darkred]
                > > > > listbox item and then update, or, pull all your records, find the[/color][/color][/color]
                one[color=blue][color=green]
                > > that[color=darkred]
                > > > > matches the listbox item in the loop and then update.
                > > > >
                > > > > Hope that gets you started.
                > > > > Pat
                > > > >
                > > > >
                > > > >
                > > > > "dixie" <dixiec@dogmail .com> wrote in message
                > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
                > > > > > I have some code that adds new records into a table for each ID in[/color][/color][/color]
                a[color=blue][color=green][color=darkred]
                > > > list
                > > > > > box when a button on a form is clicked. This works fine. My[/color][/color]
                > problem[color=green][color=darkred]
                > > > now
                > > > > is
                > > > > > that I wish to be able to edit all the records for people whose ID[/color][/color]
                > is[color=green]
                > > in[color=darkred]
                > > > > the
                > > > > > list box. I made minor changes to the code (mainly replacing[/color]
                > > rs.AddNew[color=darkred]
                > > > > with
                > > > > > rs.Edit)and it appears to be updating only the first record and[/color][/color][/color]
                then[color=blue][color=green][color=darkred]
                > > > > > overwriting that record with the next, etc until it runs out of[/color][/color][/color]
                ID's[color=blue][color=green]
                > > in[color=darkred]
                > > > > the
                > > > > > list box. In other words, it is stepping through the ID's in the[/color][/color]
                > list[color=green][color=darkred]
                > > > > box,
                > > > > > but not the records. Is there a trick to this? I have spent many[/color]
                > > hours[color=darkred]
                > > > > > doing minor changes and still have the same problem.
                > > > > >
                > > > > > The code follows (I have reduced the number of fields I am[/color][/color][/color]
                updating[color=blue]
                > to[color=green][color=darkred]
                > > > > keep
                > > > > > the size of the message down).
                > > > > >
                > > > > > Dim intIndex As Integer
                > > > > >
                > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
                > > > > >
                > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
                > > > > >
                > > > > > Next intIndex
                > > > > >
                > > > > >
                > > > > >
                > > > > > Dim db As Database
                > > > > >
                > > > > > Dim rs As Recordset
                > > > > >
                > > > > > Dim prm As Parameter
                > > > > >
                > > > > > Dim qdf As QueryDef
                > > > > >
                > > > > >
                > > > > >
                > > > > > Set db = CurrentDb()
                > > > > >
                > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
                > > > > >
                > > > > >
                > > > > >
                > > > > > For Each prm In qdf.Parameters
                > > > > >
                > > > > > prm.Value = Eval(prm.Name)
                > > > > >
                > > > > > Next prm
                > > > > >
                > > > > >
                > > > > >
                > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
                > > > > >
                > > > > >
                > > > > >
                > > > > > Dim frm As Form
                > > > > >
                > > > > > Dim ctl As Control
                > > > > >
                > > > > > Dim varItm As Variant
                > > > > >
                > > > > >
                > > > > >
                > > > > > Set frm = Forms!frmExcurs ions
                > > > > >
                > > > > > Set ctl = frm! lboBulkList
                > > > > >
                > > > > > For Each varItm In ctl.ItemsSelect ed
                > > > > >
                > > > > >
                > > > > >
                > > > > > rs.Edit
                > > > > >
                > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
                > > > > >
                > > > > > rs!Date = frm!txtDate
                > > > > >
                > > > > > rs!Faculty = frm!cboFaculty
                > > > > >
                > > > > > rs!Course = frm!Course
                > > > > >
                > > > > > rs!Cost = frm!ExCost
                > > > > >
                > > > > > rs.Update
                > > > > >
                > > > > > Next varItm
                > > > > >
                > > > > > rs.Close: Set rs = Nothing
                > > > > >
                > > > > >
                > > > > > dixie
                > > > > >
                > > > > >
                > > > >
                > > > >
                > > >
                > > >[/color]
                > >
                > >[/color]
                >
                >[/color]


                Comment

                • dixie

                  #9
                  Re: rs.Edit instead of rs.AddNew

                  The listbox contains a list of Student IDs which are text, not numbers.
                  There is a second column, which is a student name (the visible things in the
                  list box). The bound column is however the ID which is the first column.

                  dixie

                  "Pat" <noemail@ihates pam.bum> wrote in message
                  news:RSPhc.1162 0$NR5.9097@fe1. texas.rr.com...[color=blue]
                  > Dixie,
                  > It's hard to help too much without knowing what's in your listbox. Since
                  > the dynamic SQL statement seems to be returning the same record, it would[/color]
                  be[color=blue]
                  > best to see what you are returning from the listbox. Use debug.print to
                  > display your items in the listbox and the fields in the record you are
                  > returning within the Immediate window.
                  >
                  > For Each varItm In ctl.ItemsSelect ed
                  > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
                  > Set rs = CurrentDb.OpenR ecordset(strSQL )
                  > Debug.Print varItem
                  > Debug.Print Field1, Field2, Field3, etc
                  >
                  > I suspect that you have multiple columns in your listbox. This may be
                  > causing the problem - returning the same value from the listbox for each
                  > item selected, and thus retrieving the same record. If there are multiple
                  > columns, look at ItemsSelected in the help file. It will give you a sense
                  > of how to pull the right column of data you want to build your SQL[/color]
                  statement[color=blue]
                  > with.
                  >
                  > Hope this helps,
                  > Pat
                  >
                  >
                  >
                  > "dixie" <dixiec@dogmail .com> wrote in message
                  > news:hlIhc.38$A h5.2978@nnrp1.o zemail.com.au.. .[color=green]
                  > > Ok, tried that and strangely, I get exactly the same problem - that is,[/color][/color]
                  it[color=blue][color=green]
                  > > updates the first record, but not the others. I did not understand your
                  > > statement about you will need a loop to update each record in the set -[/color][/color]
                  is[color=blue][color=green]
                  > > this the bit I have missed.
                  > >
                  > > The general idea is that I have a list of details for an excursion that[/color][/color]
                  a[color=blue][color=green]
                  > > group of students are going on. I print a form out for each one and I[/color]
                  > print[color=green]
                  > > a list of students attending. Now, I already have that bit working.[/color][/color]
                  The[color=blue][color=green]
                  > > bit I am trying to get here is if there was a mistake in the details or[/color]
                  > more[color=green]
                  > > information had come to hand, I wan't to be able to do a "bulk edit" on[/color]
                  > each[color=green]
                  > > entry (one per student). The ID is a unique student ID and there would[/color][/color]
                  be[color=blue]
                  > a[color=green]
                  > > number of them (up to 100) in the list box, lboBulkList.
                  > >
                  > > Now, this is the fine detail and is currently what I interpreted your
                  > > previous post into.
                  > >
                  > > Private Sub btnEditTest_Cli ck()
                  > > Dim db As Database
                  > > Dim rs As Recordset
                  > > Dim strSQL As String
                  > >
                  > > Dim frm As Form
                  > > Dim ctl As Control
                  > > Dim varItm As Variant
                  > >
                  > > Set frm = Forms!frmExcurs ions
                  > > Set ctl = frm!lboBulkList
                  > >
                  > > For Each varItm In ctl.ItemsSelect ed
                  > > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
                  > >
                  > > Set rs = CurrentDb.OpenR ecordset(strSQL )
                  > > If rs.RecordCount > 0 Then
                  > > rs!Date = frm!txtDate
                  > > rs!ID_TCHR = frm!Teacher
                  > > rs!Faculty = frm!cboFaculty
                  > > rs!Course = frm!Course
                  > > rs!ExcursionTyp e = frm!Reason
                  > > rs!Details = frm!Comment
                  > > rs!Signatory = frm!TxtSignator y
                  > > rs!SignatoryAre a = frm!TxtSignator yArea
                  > > rs!ExcursionNam e = frm!ExName
                  > > rs!Cost = frm!ExCost
                  > > rs!Destination = frm!ExcursionDe stination
                  > > rs!DeparturePla ce = frm!DepartFrom
                  > > rs!ReturningPla ce = frm!ReturnTo
                  > > rs!DepartureTim e = frm!DepartTime
                  > > rs!ReturningTim e = frm!ReturnTime
                  > > rs!Representati ve = frm!Rep
                  > > rs!Uniform = frm!Dress
                  > > rs!Overnight = frm!Night
                  > > rs!Commendation Required = frm!Commendatio n
                  > > rs!Outdoors = frm!Outdoors
                  > > rs!Travel = frm!TravelType
                  > > rs!EntryDate = frm!txtEntryDat e
                  > > rs!Edited = -1
                  > > End If
                  > > Next varItm
                  > > End sub
                  > >
                  > > "Pat" <noemail@ihates pam.bum> wrote in message
                  > > news:IjHhc.1170 8$hR1.10135@fe2 .texas.rr.com.. .[color=darkred]
                  > > > Dixie,
                  > > > Your original post and your latest reply contains a loop that looks to[/color]
                  > > have[color=darkred]
                  > > > originally been designed to loop through all records in a recordset[/color][/color][/color]
                  and[color=blue][color=green][color=darkred]
                  > > > update fields. From your description, you want to update only records
                  > > > listed in a listbox, for which you have their ID.
                  > > >
                  > > > If your listbox will likely contain a few items, it could create a[/color][/color]
                  > dynamic[color=green][color=darkred]
                  > > > SLQ statment that selects only the record you want to edit. Then you[/color][/color]
                  > can[color=green][color=darkred]
                  > > > edit that record.
                  > > >
                  > > > Set frm = Forms!frmExcurs ions
                  > > > Set ctl = frm! lboBulkList
                  > > > For Each varItm In ctl.ItemsSelect ed
                  > > > strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
                  > > > Set rs = CurrentDb.OpenR ecordset(strSql )
                  > > > If rs.RecordCount > 0 Then
                  > > > rs.FieldNameToE dit = YourNewValue
                  > > > End if
                  > > > Next varItm
                  > > >
                  > > > If you have more than one record that will return in the recordset,[/color][/color][/color]
                  you[color=blue][color=green]
                  > > will[color=darkred]
                  > > > need a loop to handle update each record in the set.
                  > > >
                  > > > Hope this helps,
                  > > > Pat
                  > > >
                  > > >
                  > > >
                  > > > "dixie" <dixiec@dogmail .com> wrote in message
                  > > > news:gXFhc.17$A h5.1849@nnrp1.o zemail.com.au.. .
                  > > > > OK Pat, I thought as much. You have confirmed what I believed was
                  > > > > happening. My problem is that I don't know how to combine the[/color][/color]
                  > previous[color=green][color=darkred]
                  > > > type
                  > > > > of code that updates according to an ID in a list box and then this[/color][/color]
                  > type[color=green][color=darkred]
                  > > > of
                  > > > > code
                  > > > >
                  > > > > With rs
                  > > > >
                  > > > > If rs.RecordCount > 0 Then
                  > > > > .MoveFirst
                  > > > > Do
                  > > > > .Edit
                  > > > > rs!Cost = 0
                  > > > > .Update
                  > > > > .MoveNext
                  > > > > Loop Until .EOF
                  > > > > End If
                  > > > > .Close: Set rs = Nothing
                  > > > >
                  > > > > End With
                  > > > >
                  > > > > I know that I need to step through the records in the recordset and[/color][/color]
                  > edit[color=green][color=darkred]
                  > > > > each field according to my ID in the list box, but I can't seem to[/color]
                  > > combine[color=darkred]
                  > > > > the two ideas to get to the solution I want - which is for each[/color][/color][/color]
                  person[color=blue][color=green][color=darkred]
                  > > > > selected to have their specific record edited according to a what is[/color][/color]
                  > in[color=green][color=darkred]
                  > > > > various controls on the form.
                  > > > >
                  > > > > dixie
                  > > > >
                  > > > > ----- Original Message -----
                  > > > > From: "Pat" <noemail@ihates pam.bum>
                  > > > > Newsgroups: comp.databases. ms-access
                  > > > > Sent: Thursday, April 22, 2004 11:38 AM
                  > > > > Subject: Re: rs.Edit instead of rs.AddNew
                  > > > >
                  > > > >
                  > > > > > Dixie,
                  > > > > > You are looping through your selected items, but you are not[/color][/color][/color]
                  looping[color=blue][color=green][color=darkred]
                  > > > > through
                  > > > > > your recordset (although looping through the recordset is only[/color][/color][/color]
                  going[color=blue][color=green]
                  > > to[color=darkred]
                  > > > > work
                  > > > > > if the records are in the _exact_ same order as your listbox).[/color][/color][/color]
                  rs(0)[color=blue][color=green]
                  > > is[color=darkred]
                  > > > > the
                  > > > > > first record in the returned recordset. As you loop through the[/color]
                  > > items,[color=darkred]
                  > > > > you
                  > > > > > are editing the same record.
                  > > > > >
                  > > > > > Instead, you want to select each record that cooresponds to each
                  > > > selected
                  > > > > > listbox item and then update, or, pull all your records, find the[/color][/color]
                  > one[color=green][color=darkred]
                  > > > that
                  > > > > > matches the listbox item in the loop and then update.
                  > > > > >
                  > > > > > Hope that gets you started.
                  > > > > > Pat
                  > > > > >
                  > > > > >
                  > > > > >
                  > > > > > "dixie" <dixiec@dogmail .com> wrote in message
                  > > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
                  > > > > > > I have some code that adds new records into a table for each ID[/color][/color][/color]
                  in[color=blue]
                  > a[color=green][color=darkred]
                  > > > > list
                  > > > > > > box when a button on a form is clicked. This works fine. My[/color]
                  > > problem[color=darkred]
                  > > > > now
                  > > > > > is
                  > > > > > > that I wish to be able to edit all the records for people whose[/color][/color][/color]
                  ID[color=blue][color=green]
                  > > is[color=darkred]
                  > > > in
                  > > > > > the
                  > > > > > > list box. I made minor changes to the code (mainly replacing
                  > > > rs.AddNew
                  > > > > > with
                  > > > > > > rs.Edit)and it appears to be updating only the first record and[/color][/color]
                  > then[color=green][color=darkred]
                  > > > > > > overwriting that record with the next, etc until it runs out of[/color][/color]
                  > ID's[color=green][color=darkred]
                  > > > in
                  > > > > > the
                  > > > > > > list box. In other words, it is stepping through the ID's in[/color][/color][/color]
                  the[color=blue][color=green]
                  > > list[color=darkred]
                  > > > > > box,
                  > > > > > > but not the records. Is there a trick to this? I have spent[/color][/color][/color]
                  many[color=blue][color=green][color=darkred]
                  > > > hours
                  > > > > > > doing minor changes and still have the same problem.
                  > > > > > >
                  > > > > > > The code follows (I have reduced the number of fields I am[/color][/color]
                  > updating[color=green]
                  > > to[color=darkred]
                  > > > > > keep
                  > > > > > > the size of the message down).
                  > > > > > >
                  > > > > > > Dim intIndex As Integer
                  > > > > > >
                  > > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
                  > > > > > >
                  > > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
                  > > > > > >
                  > > > > > > Next intIndex
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > Dim db As Database
                  > > > > > >
                  > > > > > > Dim rs As Recordset
                  > > > > > >
                  > > > > > > Dim prm As Parameter
                  > > > > > >
                  > > > > > > Dim qdf As QueryDef
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > Set db = CurrentDb()
                  > > > > > >
                  > > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > For Each prm In qdf.Parameters
                  > > > > > >
                  > > > > > > prm.Value = Eval(prm.Name)
                  > > > > > >
                  > > > > > > Next prm
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > Dim frm As Form
                  > > > > > >
                  > > > > > > Dim ctl As Control
                  > > > > > >
                  > > > > > > Dim varItm As Variant
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > Set frm = Forms!frmExcurs ions
                  > > > > > >
                  > > > > > > Set ctl = frm! lboBulkList
                  > > > > > >
                  > > > > > > For Each varItm In ctl.ItemsSelect ed
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > rs.Edit
                  > > > > > >
                  > > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
                  > > > > > >
                  > > > > > > rs!Date = frm!txtDate
                  > > > > > >
                  > > > > > > rs!Faculty = frm!cboFaculty
                  > > > > > >
                  > > > > > > rs!Course = frm!Course
                  > > > > > >
                  > > > > > > rs!Cost = frm!ExCost
                  > > > > > >
                  > > > > > > rs.Update
                  > > > > > >
                  > > > > > > Next varItm
                  > > > > > >
                  > > > > > > rs.Close: Set rs = Nothing
                  > > > > > >
                  > > > > > >
                  > > > > > > dixie
                  > > > > > >
                  > > > > > >
                  > > > > >
                  > > > > >
                  > > > >
                  > > > > "Pat" <noemail@ihates pam.bum> wrote in message
                  > > > > news:AmFhc.8861 $NR5.255@fe1.te xas.rr.com...
                  > > > > > Dixie,
                  > > > > > You are looping through your selected items, but you are not[/color][/color][/color]
                  looping[color=blue][color=green][color=darkred]
                  > > > > through
                  > > > > > your recordset (although looping through the recordset is only[/color][/color][/color]
                  going[color=blue][color=green]
                  > > to[color=darkred]
                  > > > > work
                  > > > > > if the records are in the _exact_ same order as your listbox).[/color][/color][/color]
                  rs(0)[color=blue][color=green]
                  > > is[color=darkred]
                  > > > > the
                  > > > > > first record in the returned recordset. As you loop through the[/color]
                  > > items,[color=darkred]
                  > > > > you
                  > > > > > are editing the same record.
                  > > > > >
                  > > > > > Instead, you want to select each record that cooresponds to each
                  > > > selected
                  > > > > > listbox item and then update, or, pull all your records, find the[/color][/color]
                  > one[color=green][color=darkred]
                  > > > that
                  > > > > > matches the listbox item in the loop and then update.
                  > > > > >
                  > > > > > Hope that gets you started.
                  > > > > > Pat
                  > > > > >
                  > > > > >
                  > > > > >
                  > > > > > "dixie" <dixiec@dogmail .com> wrote in message
                  > > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
                  > > > > > > I have some code that adds new records into a table for each ID[/color][/color][/color]
                  in[color=blue]
                  > a[color=green][color=darkred]
                  > > > > list
                  > > > > > > box when a button on a form is clicked. This works fine. My[/color]
                  > > problem[color=darkred]
                  > > > > now
                  > > > > > is
                  > > > > > > that I wish to be able to edit all the records for people whose[/color][/color][/color]
                  ID[color=blue][color=green]
                  > > is[color=darkred]
                  > > > in
                  > > > > > the
                  > > > > > > list box. I made minor changes to the code (mainly replacing
                  > > > rs.AddNew
                  > > > > > with
                  > > > > > > rs.Edit)and it appears to be updating only the first record and[/color][/color]
                  > then[color=green][color=darkred]
                  > > > > > > overwriting that record with the next, etc until it runs out of[/color][/color]
                  > ID's[color=green][color=darkred]
                  > > > in
                  > > > > > the
                  > > > > > > list box. In other words, it is stepping through the ID's in[/color][/color][/color]
                  the[color=blue][color=green]
                  > > list[color=darkred]
                  > > > > > box,
                  > > > > > > but not the records. Is there a trick to this? I have spent[/color][/color][/color]
                  many[color=blue][color=green][color=darkred]
                  > > > hours
                  > > > > > > doing minor changes and still have the same problem.
                  > > > > > >
                  > > > > > > The code follows (I have reduced the number of fields I am[/color][/color]
                  > updating[color=green]
                  > > to[color=darkred]
                  > > > > > keep
                  > > > > > > the size of the message down).
                  > > > > > >
                  > > > > > > Dim intIndex As Integer
                  > > > > > >
                  > > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
                  > > > > > >
                  > > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
                  > > > > > >
                  > > > > > > Next intIndex
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > Dim db As Database
                  > > > > > >
                  > > > > > > Dim rs As Recordset
                  > > > > > >
                  > > > > > > Dim prm As Parameter
                  > > > > > >
                  > > > > > > Dim qdf As QueryDef
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > Set db = CurrentDb()
                  > > > > > >
                  > > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > For Each prm In qdf.Parameters
                  > > > > > >
                  > > > > > > prm.Value = Eval(prm.Name)
                  > > > > > >
                  > > > > > > Next prm
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > Dim frm As Form
                  > > > > > >
                  > > > > > > Dim ctl As Control
                  > > > > > >
                  > > > > > > Dim varItm As Variant
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > Set frm = Forms!frmExcurs ions
                  > > > > > >
                  > > > > > > Set ctl = frm! lboBulkList
                  > > > > > >
                  > > > > > > For Each varItm In ctl.ItemsSelect ed
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > rs.Edit
                  > > > > > >
                  > > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
                  > > > > > >
                  > > > > > > rs!Date = frm!txtDate
                  > > > > > >
                  > > > > > > rs!Faculty = frm!cboFaculty
                  > > > > > >
                  > > > > > > rs!Course = frm!Course
                  > > > > > >
                  > > > > > > rs!Cost = frm!ExCost
                  > > > > > >
                  > > > > > > rs.Update
                  > > > > > >
                  > > > > > > Next varItm
                  > > > > > >
                  > > > > > > rs.Close: Set rs = Nothing
                  > > > > > >
                  > > > > > >
                  > > > > > > dixie
                  > > > > > >
                  > > > > > >
                  > > > > >
                  > > > > >
                  > > > >
                  > > > >
                  > > >
                  > > >[/color]
                  > >
                  > >[/color]
                  >
                  >[/color]


                  Comment

                  • dixie

                    #10
                    Re: rs.Edit instead of rs.AddNew

                    Hi Joe,

                    I tried your idea, but I get an error 3464 - Data type mismatch in criteria
                    expression, which I presume is the WHERE [ID] =" &
                    Me.lboBulkList. ItemData(0)

                    I played around with variations, but couldn't get it to work.

                    The ID field is a text field if this helps and is the first of two columns
                    in the list box as well as being the bound field in the Row Source of the
                    listbox.

                    You are definitely right about the database design. It is an old one I have
                    inherited and I am at this stage just trying to add the ability to change
                    the details of an excursion and produce a new form for all students
                    containing those changes. Database design changes are on the agenda, but
                    further down the track.

                    This is what I have now.

                    Private Sub btnEditTest2_Cl ick()

                    Dim db As Database
                    Dim rs As Recordset
                    Dim strSQL As String

                    Dim frm As Form
                    Dim ctl As Control
                    Dim varItm As Variant

                    Set frm = Forms!frmExcurs ions
                    Set ctl = frm!lboBulkList

                    Dim i As Integer
                    For i = 0 To Me.lboBulkList. ListCount - 1

                    strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" &
                    Me.lboBulkList. ItemData(0)

                    Set rs = CurrentDb.OpenR ecordset(strSQL ) <-- It is halting with this
                    line hilighted
                    If rs.RecordCount > 0 Then
                    rs!Date = frm!txtDate
                    rs!ID_TCHR = frm!Teacher
                    rs!Faculty = frm!cboFaculty
                    rs!Course = frm!Course
                    rs!ExcursionTyp e = frm!Reason
                    rs!Details = frm!Comment
                    rs!Signatory = frm!TxtSignator y
                    rs!SignatoryAre a = frm!TxtSignator yArea
                    rs!ExcursionNam e = frm!ExName
                    rs!Cost = frm!ExCost
                    rs!Destination = frm!ExcursionDe stination
                    rs!DeparturePla ce = frm!DepartFrom
                    rs!ReturningPla ce = frm!ReturnTo
                    rs!DepartureTim e = frm!DepartTime
                    rs!ReturningTim e = frm!ReturnTime
                    rs!Representati ve = frm!Rep
                    rs!Uniform = frm!Dress
                    rs!Overnight = frm!Night
                    rs!Commendation Required = frm!Commendatio n
                    rs!Outdoors = frm!Outdoors
                    rs!Travel = frm!TravelType
                    rs!EntryDate = frm!txtEntryDat e
                    rs!Edited = -1
                    End If
                    Next i


                    End Sub

                    Can you see what is wrong?

                    dixie


                    "Joe Black" <joeblack99@hot mail.com> wrote in message
                    news:5yQhc.2415 $_s.68732@news. xtra.co.nz...[color=blue]
                    > Hi Dixie
                    >
                    > In your first version of the code you first select all items in the[/color]
                    listbox[color=blue]
                    > and then iterate through all the selected items.
                    > If you want to process all the list items, it is not necessary to select
                    > them all first.
                    >
                    > Instead of "For Each varItm In ctl.ItemsSelect ed"
                    > you can do something like:
                    >
                    > Dim i As Integer
                    > For i = 0 To Me.lboBulkList. ListCount - 1
                    >
                    > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" &
                    > Me.lboBulkList. ItemData(0)
                    > Set rs = CurrentDb.OpenR ecordset(strSQL )
                    > If rs.RecordCount > 0 Then
                    > rs!Date = frm!txtDate
                    > ...
                    > end if
                    > Next i
                    >
                    > You haven't allowed for the possibility that rs.RecordCount might be > 1.
                    >
                    > I also wonder whether the design of your database could be improved. If[/color]
                    the[color=blue]
                    > details of an Excursion are needed to be changed, you should only have to
                    > change the details in one row of a table, not repeatedly change the same
                    > details for many rows.
                    >
                    > Regards - Joe
                    >
                    > "dixie" <dixiec@dogmail .com> wrote in message
                    > news:hlIhc.38$A h5.2978@nnrp1.o zemail.com.au.. .[color=green]
                    > > Ok, tried that and strangely, I get exactly the same problem - that is,[/color][/color]
                    it[color=blue][color=green]
                    > > updates the first record, but not the others. I did not understand your
                    > > statement about you will need a loop to update each record in the set -[/color][/color]
                    is[color=blue][color=green]
                    > > this the bit I have missed.
                    > >
                    > > The general idea is that I have a list of details for an excursion that[/color][/color]
                    a[color=blue][color=green]
                    > > group of students are going on. I print a form out for each one and I[/color]
                    > print[color=green]
                    > > a list of students attending. Now, I already have that bit working.[/color][/color]
                    The[color=blue][color=green]
                    > > bit I am trying to get here is if there was a mistake in the details or[/color]
                    > more[color=green]
                    > > information had come to hand, I wan't to be able to do a "bulk edit" on[/color]
                    > each[color=green]
                    > > entry (one per student). The ID is a unique student ID and there would[/color][/color]
                    be[color=blue]
                    > a[color=green]
                    > > number of them (up to 100) in the list box, lboBulkList.
                    > >
                    > > Now, this is the fine detail and is currently what I interpreted your
                    > > previous post into.
                    > >
                    > > Private Sub btnEditTest_Cli ck()
                    > > Dim db As Database
                    > > Dim rs As Recordset
                    > > Dim strSQL As String
                    > >
                    > > Dim frm As Form
                    > > Dim ctl As Control
                    > > Dim varItm As Variant
                    > >
                    > > Set frm = Forms!frmExcurs ions
                    > > Set ctl = frm!lboBulkList
                    > >
                    > > For Each varItm In ctl.ItemsSelect ed
                    > > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
                    > >
                    > > Set rs = CurrentDb.OpenR ecordset(strSQL )
                    > > If rs.RecordCount > 0 Then
                    > > rs!Date = frm!txtDate
                    > > rs!ID_TCHR = frm!Teacher
                    > > rs!Faculty = frm!cboFaculty
                    > > rs!Course = frm!Course
                    > > rs!ExcursionTyp e = frm!Reason
                    > > rs!Details = frm!Comment
                    > > rs!Signatory = frm!TxtSignator y
                    > > rs!SignatoryAre a = frm!TxtSignator yArea
                    > > rs!ExcursionNam e = frm!ExName
                    > > rs!Cost = frm!ExCost
                    > > rs!Destination = frm!ExcursionDe stination
                    > > rs!DeparturePla ce = frm!DepartFrom
                    > > rs!ReturningPla ce = frm!ReturnTo
                    > > rs!DepartureTim e = frm!DepartTime
                    > > rs!ReturningTim e = frm!ReturnTime
                    > > rs!Representati ve = frm!Rep
                    > > rs!Uniform = frm!Dress
                    > > rs!Overnight = frm!Night
                    > > rs!Commendation Required = frm!Commendatio n
                    > > rs!Outdoors = frm!Outdoors
                    > > rs!Travel = frm!TravelType
                    > > rs!EntryDate = frm!txtEntryDat e
                    > > rs!Edited = -1
                    > > End If
                    > > Next varItm
                    > > End sub
                    > >
                    > > "Pat" <noemail@ihates pam.bum> wrote in message
                    > > news:IjHhc.1170 8$hR1.10135@fe2 .texas.rr.com.. .[color=darkred]
                    > > > Dixie,
                    > > > Your original post and your latest reply contains a loop that looks to[/color]
                    > > have[color=darkred]
                    > > > originally been designed to loop through all records in a recordset[/color][/color][/color]
                    and[color=blue][color=green][color=darkred]
                    > > > update fields. From your description, you want to update only records
                    > > > listed in a listbox, for which you have their ID.
                    > > >
                    > > > If your listbox will likely contain a few items, it could create a[/color][/color]
                    > dynamic[color=green][color=darkred]
                    > > > SLQ statment that selects only the record you want to edit. Then you[/color][/color]
                    > can[color=green][color=darkred]
                    > > > edit that record.
                    > > >
                    > > > Set frm = Forms!frmExcurs ions
                    > > > Set ctl = frm! lboBulkList
                    > > > For Each varItm In ctl.ItemsSelect ed
                    > > > strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
                    > > > Set rs = CurrentDb.OpenR ecordset(strSql )
                    > > > If rs.RecordCount > 0 Then
                    > > > rs.FieldNameToE dit = YourNewValue
                    > > > End if
                    > > > Next varItm
                    > > >
                    > > > If you have more than one record that will return in the recordset,[/color][/color][/color]
                    you[color=blue][color=green]
                    > > will[color=darkred]
                    > > > need a loop to handle update each record in the set.
                    > > >
                    > > > Hope this helps,
                    > > > Pat
                    > > >
                    > > >
                    > > >
                    > > > "dixie" <dixiec@dogmail .com> wrote in message
                    > > > news:gXFhc.17$A h5.1849@nnrp1.o zemail.com.au.. .
                    > > > > OK Pat, I thought as much. You have confirmed what I believed was
                    > > > > happening. My problem is that I don't know how to combine the[/color][/color]
                    > previous[color=green][color=darkred]
                    > > > type
                    > > > > of code that updates according to an ID in a list box and then this[/color][/color]
                    > type[color=green][color=darkred]
                    > > > of
                    > > > > code
                    > > > >
                    > > > > With rs
                    > > > >
                    > > > > If rs.RecordCount > 0 Then
                    > > > > .MoveFirst
                    > > > > Do
                    > > > > .Edit
                    > > > > rs!Cost = 0
                    > > > > .Update
                    > > > > .MoveNext
                    > > > > Loop Until .EOF
                    > > > > End If
                    > > > > .Close: Set rs = Nothing
                    > > > >
                    > > > > End With
                    > > > >
                    > > > > I know that I need to step through the records in the recordset and[/color][/color]
                    > edit[color=green][color=darkred]
                    > > > > each field according to my ID in the list box, but I can't seem to[/color]
                    > > combine[color=darkred]
                    > > > > the two ideas to get to the solution I want - which is for each[/color][/color][/color]
                    person[color=blue][color=green][color=darkred]
                    > > > > selected to have their specific record edited according to a what is[/color][/color]
                    > in[color=green][color=darkred]
                    > > > > various controls on the form.
                    > > > >
                    > > > > dixie
                    > > > >
                    > > > > ----- Original Message -----
                    > > > > From: "Pat" <noemail@ihates pam.bum>
                    > > > > Newsgroups: comp.databases. ms-access
                    > > > > Sent: Thursday, April 22, 2004 11:38 AM
                    > > > > Subject: Re: rs.Edit instead of rs.AddNew
                    > > > >
                    > > > >
                    > > > > > Dixie,
                    > > > > > You are looping through your selected items, but you are not[/color][/color][/color]
                    looping[color=blue][color=green][color=darkred]
                    > > > > through
                    > > > > > your recordset (although looping through the recordset is only[/color][/color][/color]
                    going[color=blue][color=green]
                    > > to[color=darkred]
                    > > > > work
                    > > > > > if the records are in the _exact_ same order as your listbox).[/color][/color][/color]
                    rs(0)[color=blue][color=green]
                    > > is[color=darkred]
                    > > > > the
                    > > > > > first record in the returned recordset. As you loop through the[/color]
                    > > items,[color=darkred]
                    > > > > you
                    > > > > > are editing the same record.
                    > > > > >
                    > > > > > Instead, you want to select each record that cooresponds to each
                    > > > selected
                    > > > > > listbox item and then update, or, pull all your records, find the[/color][/color]
                    > one[color=green][color=darkred]
                    > > > that
                    > > > > > matches the listbox item in the loop and then update.
                    > > > > >
                    > > > > > Hope that gets you started.
                    > > > > > Pat
                    > > > > >
                    > > > > >
                    > > > > >
                    > > > > > "dixie" <dixiec@dogmail .com> wrote in message
                    > > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
                    > > > > > > I have some code that adds new records into a table for each ID[/color][/color][/color]
                    in[color=blue]
                    > a[color=green][color=darkred]
                    > > > > list
                    > > > > > > box when a button on a form is clicked. This works fine. My[/color]
                    > > problem[color=darkred]
                    > > > > now
                    > > > > > is
                    > > > > > > that I wish to be able to edit all the records for people whose[/color][/color][/color]
                    ID[color=blue][color=green]
                    > > is[color=darkred]
                    > > > in
                    > > > > > the
                    > > > > > > list box. I made minor changes to the code (mainly replacing
                    > > > rs.AddNew
                    > > > > > with
                    > > > > > > rs.Edit)and it appears to be updating only the first record and[/color][/color]
                    > then[color=green][color=darkred]
                    > > > > > > overwriting that record with the next, etc until it runs out of[/color][/color]
                    > ID's[color=green][color=darkred]
                    > > > in
                    > > > > > the
                    > > > > > > list box. In other words, it is stepping through the ID's in[/color][/color][/color]
                    the[color=blue][color=green]
                    > > list[color=darkred]
                    > > > > > box,
                    > > > > > > but not the records. Is there a trick to this? I have spent[/color][/color][/color]
                    many[color=blue][color=green][color=darkred]
                    > > > hours
                    > > > > > > doing minor changes and still have the same problem.
                    > > > > > >
                    > > > > > > The code follows (I have reduced the number of fields I am[/color][/color]
                    > updating[color=green]
                    > > to[color=darkred]
                    > > > > > keep
                    > > > > > > the size of the message down).
                    > > > > > >
                    > > > > > > Dim intIndex As Integer
                    > > > > > >
                    > > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
                    > > > > > >
                    > > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
                    > > > > > >
                    > > > > > > Next intIndex
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > Dim db As Database
                    > > > > > >
                    > > > > > > Dim rs As Recordset
                    > > > > > >
                    > > > > > > Dim prm As Parameter
                    > > > > > >
                    > > > > > > Dim qdf As QueryDef
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > Set db = CurrentDb()
                    > > > > > >
                    > > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > For Each prm In qdf.Parameters
                    > > > > > >
                    > > > > > > prm.Value = Eval(prm.Name)
                    > > > > > >
                    > > > > > > Next prm
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > Dim frm As Form
                    > > > > > >
                    > > > > > > Dim ctl As Control
                    > > > > > >
                    > > > > > > Dim varItm As Variant
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > Set frm = Forms!frmExcurs ions
                    > > > > > >
                    > > > > > > Set ctl = frm! lboBulkList
                    > > > > > >
                    > > > > > > For Each varItm In ctl.ItemsSelect ed
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > rs.Edit
                    > > > > > >
                    > > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
                    > > > > > >
                    > > > > > > rs!Date = frm!txtDate
                    > > > > > >
                    > > > > > > rs!Faculty = frm!cboFaculty
                    > > > > > >
                    > > > > > > rs!Course = frm!Course
                    > > > > > >
                    > > > > > > rs!Cost = frm!ExCost
                    > > > > > >
                    > > > > > > rs.Update
                    > > > > > >
                    > > > > > > Next varItm
                    > > > > > >
                    > > > > > > rs.Close: Set rs = Nothing
                    > > > > > >
                    > > > > > >
                    > > > > > > dixie
                    > > > > > >
                    > > > > > >
                    > > > > >
                    > > > > >
                    > > > >
                    > > > > "Pat" <noemail@ihates pam.bum> wrote in message
                    > > > > news:AmFhc.8861 $NR5.255@fe1.te xas.rr.com...
                    > > > > > Dixie,
                    > > > > > You are looping through your selected items, but you are not[/color][/color][/color]
                    looping[color=blue][color=green][color=darkred]
                    > > > > through
                    > > > > > your recordset (although looping through the recordset is only[/color][/color][/color]
                    going[color=blue][color=green]
                    > > to[color=darkred]
                    > > > > work
                    > > > > > if the records are in the _exact_ same order as your listbox).[/color][/color][/color]
                    rs(0)[color=blue][color=green]
                    > > is[color=darkred]
                    > > > > the
                    > > > > > first record in the returned recordset. As you loop through the[/color]
                    > > items,[color=darkred]
                    > > > > you
                    > > > > > are editing the same record.
                    > > > > >
                    > > > > > Instead, you want to select each record that cooresponds to each
                    > > > selected
                    > > > > > listbox item and then update, or, pull all your records, find the[/color][/color]
                    > one[color=green][color=darkred]
                    > > > that
                    > > > > > matches the listbox item in the loop and then update.
                    > > > > >
                    > > > > > Hope that gets you started.
                    > > > > > Pat
                    > > > > >
                    > > > > >
                    > > > > >
                    > > > > > "dixie" <dixiec@dogmail .com> wrote in message
                    > > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
                    > > > > > > I have some code that adds new records into a table for each ID[/color][/color][/color]
                    in[color=blue]
                    > a[color=green][color=darkred]
                    > > > > list
                    > > > > > > box when a button on a form is clicked. This works fine. My[/color]
                    > > problem[color=darkred]
                    > > > > now
                    > > > > > is
                    > > > > > > that I wish to be able to edit all the records for people whose[/color][/color][/color]
                    ID[color=blue][color=green]
                    > > is[color=darkred]
                    > > > in
                    > > > > > the
                    > > > > > > list box. I made minor changes to the code (mainly replacing
                    > > > rs.AddNew
                    > > > > > with
                    > > > > > > rs.Edit)and it appears to be updating only the first record and[/color][/color]
                    > then[color=green][color=darkred]
                    > > > > > > overwriting that record with the next, etc until it runs out of[/color][/color]
                    > ID's[color=green][color=darkred]
                    > > > in
                    > > > > > the
                    > > > > > > list box. In other words, it is stepping through the ID's in[/color][/color][/color]
                    the[color=blue][color=green]
                    > > list[color=darkred]
                    > > > > > box,
                    > > > > > > but not the records. Is there a trick to this? I have spent[/color][/color][/color]
                    many[color=blue][color=green][color=darkred]
                    > > > hours
                    > > > > > > doing minor changes and still have the same problem.
                    > > > > > >
                    > > > > > > The code follows (I have reduced the number of fields I am[/color][/color]
                    > updating[color=green]
                    > > to[color=darkred]
                    > > > > > keep
                    > > > > > > the size of the message down).
                    > > > > > >
                    > > > > > > Dim intIndex As Integer
                    > > > > > >
                    > > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
                    > > > > > >
                    > > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
                    > > > > > >
                    > > > > > > Next intIndex
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > Dim db As Database
                    > > > > > >
                    > > > > > > Dim rs As Recordset
                    > > > > > >
                    > > > > > > Dim prm As Parameter
                    > > > > > >
                    > > > > > > Dim qdf As QueryDef
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > Set db = CurrentDb()
                    > > > > > >
                    > > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > For Each prm In qdf.Parameters
                    > > > > > >
                    > > > > > > prm.Value = Eval(prm.Name)
                    > > > > > >
                    > > > > > > Next prm
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > Dim frm As Form
                    > > > > > >
                    > > > > > > Dim ctl As Control
                    > > > > > >
                    > > > > > > Dim varItm As Variant
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > Set frm = Forms!frmExcurs ions
                    > > > > > >
                    > > > > > > Set ctl = frm! lboBulkList
                    > > > > > >
                    > > > > > > For Each varItm In ctl.ItemsSelect ed
                    > > > > > >
                    > > > > > >
                    > > > > > >
                    > > > > > > rs.Edit
                    > > > > > >
                    > > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
                    > > > > > >
                    > > > > > > rs!Date = frm!txtDate
                    > > > > > >
                    > > > > > > rs!Faculty = frm!cboFaculty
                    > > > > > >
                    > > > > > > rs!Course = frm!Course
                    > > > > > >
                    > > > > > > rs!Cost = frm!ExCost
                    > > > > > >
                    > > > > > > rs.Update
                    > > > > > >
                    > > > > > > Next varItm
                    > > > > > >
                    > > > > > > rs.Close: Set rs = Nothing
                    > > > > > >
                    > > > > > >
                    > > > > > > dixie
                    > > > > > >
                    > > > > > >
                    > > > > >
                    > > > > >
                    > > > >
                    > > > >
                    > > >
                    > > >[/color]
                    > >
                    > >[/color]
                    >
                    >[/color]


                    Comment

                    • dixie

                      #11
                      Re: rs.Edit instead of rs.AddNew

                      Joe
                      Further to that last message, I refined my strSQL into a query that picks up
                      only those students in the current excursion. I am now getting the error
                      message - Runtime error 3061 Too few parameters. Expected 1.

                      It is still halting on the same line which is the line
                      Set rs = CurrentDb.OpenR ecordset(strSQL )

                      I can see the ID of the first student if I hover over the strSQL line.

                      dixie

                      "dixie" <dixiec@dogmail .com> wrote in message
                      news:TcWhc.169$ Ah5.9387@nnrp1. ozemail.com.au. ..[color=blue]
                      > Hi Joe,
                      >
                      > I tried your idea, but I get an error 3464 - Data type mismatch in[/color]
                      criteria[color=blue]
                      > expression, which I presume is the WHERE [ID] =" &
                      > Me.lboBulkList. ItemData(0)
                      >
                      > I played around with variations, but couldn't get it to work.
                      >
                      > The ID field is a text field if this helps and is the first of two columns
                      > in the list box as well as being the bound field in the Row Source of the
                      > listbox.
                      >
                      > You are definitely right about the database design. It is an old one I[/color]
                      have[color=blue]
                      > inherited and I am at this stage just trying to add the ability to change
                      > the details of an excursion and produce a new form for all students
                      > containing those changes. Database design changes are on the agenda, but
                      > further down the track.
                      >
                      > This is what I have now.
                      >
                      > Private Sub btnEditTest2_Cl ick()
                      >
                      > Dim db As Database
                      > Dim rs As Recordset
                      > Dim strSQL As String
                      >
                      > Dim frm As Form
                      > Dim ctl As Control
                      > Dim varItm As Variant
                      >
                      > Set frm = Forms!frmExcurs ions
                      > Set ctl = frm!lboBulkList
                      >
                      > Dim i As Integer
                      > For i = 0 To Me.lboBulkList. ListCount - 1
                      >
                      > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" &
                      > Me.lboBulkList. ItemData(0)
                      >
                      > Set rs = CurrentDb.OpenR ecordset(strSQL ) <-- It is halting with[/color]
                      this[color=blue]
                      > line hilighted
                      > If rs.RecordCount > 0 Then
                      > rs!Date = frm!txtDate
                      > rs!ID_TCHR = frm!Teacher
                      > rs!Faculty = frm!cboFaculty
                      > rs!Course = frm!Course
                      > rs!ExcursionTyp e = frm!Reason
                      > rs!Details = frm!Comment
                      > rs!Signatory = frm!TxtSignator y
                      > rs!SignatoryAre a = frm!TxtSignator yArea
                      > rs!ExcursionNam e = frm!ExName
                      > rs!Cost = frm!ExCost
                      > rs!Destination = frm!ExcursionDe stination
                      > rs!DeparturePla ce = frm!DepartFrom
                      > rs!ReturningPla ce = frm!ReturnTo
                      > rs!DepartureTim e = frm!DepartTime
                      > rs!ReturningTim e = frm!ReturnTime
                      > rs!Representati ve = frm!Rep
                      > rs!Uniform = frm!Dress
                      > rs!Overnight = frm!Night
                      > rs!Commendation Required = frm!Commendatio n
                      > rs!Outdoors = frm!Outdoors
                      > rs!Travel = frm!TravelType
                      > rs!EntryDate = frm!txtEntryDat e
                      > rs!Edited = -1
                      > End If
                      > Next i
                      >
                      >
                      > End Sub
                      >
                      > Can you see what is wrong?
                      >
                      > dixie
                      >
                      >
                      > "Joe Black" <joeblack99@hot mail.com> wrote in message
                      > news:5yQhc.2415 $_s.68732@news. xtra.co.nz...[color=green]
                      > > Hi Dixie
                      > >
                      > > In your first version of the code you first select all items in the[/color]
                      > listbox[color=green]
                      > > and then iterate through all the selected items.
                      > > If you want to process all the list items, it is not necessary to select
                      > > them all first.
                      > >
                      > > Instead of "For Each varItm In ctl.ItemsSelect ed"
                      > > you can do something like:
                      > >
                      > > Dim i As Integer
                      > > For i = 0 To Me.lboBulkList. ListCount - 1
                      > >
                      > > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" &
                      > > Me.lboBulkList. ItemData(0)
                      > > Set rs = CurrentDb.OpenR ecordset(strSQL )
                      > > If rs.RecordCount > 0 Then
                      > > rs!Date = frm!txtDate
                      > > ...
                      > > end if
                      > > Next i
                      > >
                      > > You haven't allowed for the possibility that rs.RecordCount might be >[/color][/color]
                      1.[color=blue][color=green]
                      > >
                      > > I also wonder whether the design of your database could be improved. If[/color]
                      > the[color=green]
                      > > details of an Excursion are needed to be changed, you should only have[/color][/color]
                      to[color=blue][color=green]
                      > > change the details in one row of a table, not repeatedly change the same
                      > > details for many rows.
                      > >
                      > > Regards - Joe
                      > >
                      > > "dixie" <dixiec@dogmail .com> wrote in message
                      > > news:hlIhc.38$A h5.2978@nnrp1.o zemail.com.au.. .[color=darkred]
                      > > > Ok, tried that and strangely, I get exactly the same problem - that[/color][/color][/color]
                      is,[color=blue]
                      > it[color=green][color=darkred]
                      > > > updates the first record, but not the others. I did not understand[/color][/color][/color]
                      your[color=blue][color=green][color=darkred]
                      > > > statement about you will need a loop to update each record in the[/color][/color][/color]
                      set -[color=blue]
                      > is[color=green][color=darkred]
                      > > > this the bit I have missed.
                      > > >
                      > > > The general idea is that I have a list of details for an excursion[/color][/color][/color]
                      that[color=blue]
                      > a[color=green][color=darkred]
                      > > > group of students are going on. I print a form out for each one and I[/color]
                      > > print[color=darkred]
                      > > > a list of students attending. Now, I already have that bit working.[/color][/color]
                      > The[color=green][color=darkred]
                      > > > bit I am trying to get here is if there was a mistake in the details[/color][/color][/color]
                      or[color=blue][color=green]
                      > > more[color=darkred]
                      > > > information had come to hand, I wan't to be able to do a "bulk edit"[/color][/color][/color]
                      on[color=blue][color=green]
                      > > each[color=darkred]
                      > > > entry (one per student). The ID is a unique student ID and there[/color][/color][/color]
                      would[color=blue]
                      > be[color=green]
                      > > a[color=darkred]
                      > > > number of them (up to 100) in the list box, lboBulkList.
                      > > >
                      > > > Now, this is the fine detail and is currently what I interpreted your
                      > > > previous post into.
                      > > >
                      > > > Private Sub btnEditTest_Cli ck()
                      > > > Dim db As Database
                      > > > Dim rs As Recordset
                      > > > Dim strSQL As String
                      > > >
                      > > > Dim frm As Form
                      > > > Dim ctl As Control
                      > > > Dim varItm As Variant
                      > > >
                      > > > Set frm = Forms!frmExcurs ions
                      > > > Set ctl = frm!lboBulkList
                      > > >
                      > > > For Each varItm In ctl.ItemsSelect ed
                      > > > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
                      > > >
                      > > > Set rs = CurrentDb.OpenR ecordset(strSQL )
                      > > > If rs.RecordCount > 0 Then
                      > > > rs!Date = frm!txtDate
                      > > > rs!ID_TCHR = frm!Teacher
                      > > > rs!Faculty = frm!cboFaculty
                      > > > rs!Course = frm!Course
                      > > > rs!ExcursionTyp e = frm!Reason
                      > > > rs!Details = frm!Comment
                      > > > rs!Signatory = frm!TxtSignator y
                      > > > rs!SignatoryAre a = frm!TxtSignator yArea
                      > > > rs!ExcursionNam e = frm!ExName
                      > > > rs!Cost = frm!ExCost
                      > > > rs!Destination = frm!ExcursionDe stination
                      > > > rs!DeparturePla ce = frm!DepartFrom
                      > > > rs!ReturningPla ce = frm!ReturnTo
                      > > > rs!DepartureTim e = frm!DepartTime
                      > > > rs!ReturningTim e = frm!ReturnTime
                      > > > rs!Representati ve = frm!Rep
                      > > > rs!Uniform = frm!Dress
                      > > > rs!Overnight = frm!Night
                      > > > rs!Commendation Required = frm!Commendatio n
                      > > > rs!Outdoors = frm!Outdoors
                      > > > rs!Travel = frm!TravelType
                      > > > rs!EntryDate = frm!txtEntryDat e
                      > > > rs!Edited = -1
                      > > > End If
                      > > > Next varItm
                      > > > End sub
                      > > >
                      > > > "Pat" <noemail@ihates pam.bum> wrote in message
                      > > > news:IjHhc.1170 8$hR1.10135@fe2 .texas.rr.com.. .
                      > > > > Dixie,
                      > > > > Your original post and your latest reply contains a loop that looks[/color][/color][/color]
                      to[color=blue][color=green][color=darkred]
                      > > > have
                      > > > > originally been designed to loop through all records in a recordset[/color][/color]
                      > and[color=green][color=darkred]
                      > > > > update fields. From your description, you want to update only[/color][/color][/color]
                      records[color=blue][color=green][color=darkred]
                      > > > > listed in a listbox, for which you have their ID.
                      > > > >
                      > > > > If your listbox will likely contain a few items, it could create a[/color]
                      > > dynamic[color=darkred]
                      > > > > SLQ statment that selects only the record you want to edit. Then[/color][/color][/color]
                      you[color=blue][color=green]
                      > > can[color=darkred]
                      > > > > edit that record.
                      > > > >
                      > > > > Set frm = Forms!frmExcurs ions
                      > > > > Set ctl = frm! lboBulkList
                      > > > > For Each varItm In ctl.ItemsSelect ed
                      > > > > strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
                      > > > > Set rs = CurrentDb.OpenR ecordset(strSql )
                      > > > > If rs.RecordCount > 0 Then
                      > > > > rs.FieldNameToE dit = YourNewValue
                      > > > > End if
                      > > > > Next varItm
                      > > > >
                      > > > > If you have more than one record that will return in the recordset,[/color][/color]
                      > you[color=green][color=darkred]
                      > > > will
                      > > > > need a loop to handle update each record in the set.
                      > > > >
                      > > > > Hope this helps,
                      > > > > Pat
                      > > > >
                      > > > >
                      > > > >
                      > > > > "dixie" <dixiec@dogmail .com> wrote in message
                      > > > > news:gXFhc.17$A h5.1849@nnrp1.o zemail.com.au.. .
                      > > > > > OK Pat, I thought as much. You have confirmed what I believed was
                      > > > > > happening. My problem is that I don't know how to combine the[/color]
                      > > previous[color=darkred]
                      > > > > type
                      > > > > > of code that updates according to an ID in a list box and then[/color][/color][/color]
                      this[color=blue][color=green]
                      > > type[color=darkred]
                      > > > > of
                      > > > > > code
                      > > > > >
                      > > > > > With rs
                      > > > > >
                      > > > > > If rs.RecordCount > 0 Then
                      > > > > > .MoveFirst
                      > > > > > Do
                      > > > > > .Edit
                      > > > > > rs!Cost = 0
                      > > > > > .Update
                      > > > > > .MoveNext
                      > > > > > Loop Until .EOF
                      > > > > > End If
                      > > > > > .Close: Set rs = Nothing
                      > > > > >
                      > > > > > End With
                      > > > > >
                      > > > > > I know that I need to step through the records in the recordset[/color][/color][/color]
                      and[color=blue][color=green]
                      > > edit[color=darkred]
                      > > > > > each field according to my ID in the list box, but I can't seem to
                      > > > combine
                      > > > > > the two ideas to get to the solution I want - which is for each[/color][/color]
                      > person[color=green][color=darkred]
                      > > > > > selected to have their specific record edited according to a what[/color][/color][/color]
                      is[color=blue][color=green]
                      > > in[color=darkred]
                      > > > > > various controls on the form.
                      > > > > >
                      > > > > > dixie
                      > > > > >
                      > > > > > ----- Original Message -----
                      > > > > > From: "Pat" <noemail@ihates pam.bum>
                      > > > > > Newsgroups: comp.databases. ms-access
                      > > > > > Sent: Thursday, April 22, 2004 11:38 AM
                      > > > > > Subject: Re: rs.Edit instead of rs.AddNew
                      > > > > >
                      > > > > >
                      > > > > > > Dixie,
                      > > > > > > You are looping through your selected items, but you are not[/color][/color]
                      > looping[color=green][color=darkred]
                      > > > > > through
                      > > > > > > your recordset (although looping through the recordset is only[/color][/color]
                      > going[color=green][color=darkred]
                      > > > to
                      > > > > > work
                      > > > > > > if the records are in the _exact_ same order as your listbox).[/color][/color]
                      > rs(0)[color=green][color=darkred]
                      > > > is
                      > > > > > the
                      > > > > > > first record in the returned recordset. As you loop through the
                      > > > items,
                      > > > > > you
                      > > > > > > are editing the same record.
                      > > > > > >
                      > > > > > > Instead, you want to select each record that cooresponds to each
                      > > > > selected
                      > > > > > > listbox item and then update, or, pull all your records, find[/color][/color][/color]
                      the[color=blue][color=green]
                      > > one[color=darkred]
                      > > > > that
                      > > > > > > matches the listbox item in the loop and then update.
                      > > > > > >
                      > > > > > > Hope that gets you started.
                      > > > > > > Pat
                      > > > > > >
                      > > > > > >
                      > > > > > >
                      > > > > > > "dixie" <dixiec@dogmail .com> wrote in message
                      > > > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
                      > > > > > > > I have some code that adds new records into a table for each[/color][/color][/color]
                      ID[color=blue]
                      > in[color=green]
                      > > a[color=darkred]
                      > > > > > list
                      > > > > > > > box when a button on a form is clicked. This works fine. My
                      > > > problem
                      > > > > > now
                      > > > > > > is
                      > > > > > > > that I wish to be able to edit all the records for people[/color][/color][/color]
                      whose[color=blue]
                      > ID[color=green][color=darkred]
                      > > > is
                      > > > > in
                      > > > > > > the
                      > > > > > > > list box. I made minor changes to the code (mainly replacing
                      > > > > rs.AddNew
                      > > > > > > with
                      > > > > > > > rs.Edit)and it appears to be updating only the first record[/color][/color][/color]
                      and[color=blue][color=green]
                      > > then[color=darkred]
                      > > > > > > > overwriting that record with the next, etc until it runs out[/color][/color][/color]
                      of[color=blue][color=green]
                      > > ID's[color=darkred]
                      > > > > in
                      > > > > > > the
                      > > > > > > > list box. In other words, it is stepping through the ID's in[/color][/color]
                      > the[color=green][color=darkred]
                      > > > list
                      > > > > > > box,
                      > > > > > > > but not the records. Is there a trick to this? I have spent[/color][/color]
                      > many[color=green][color=darkred]
                      > > > > hours
                      > > > > > > > doing minor changes and still have the same problem.
                      > > > > > > >
                      > > > > > > > The code follows (I have reduced the number of fields I am[/color]
                      > > updating[color=darkred]
                      > > > to
                      > > > > > > keep
                      > > > > > > > the size of the message down).
                      > > > > > > >
                      > > > > > > > Dim intIndex As Integer
                      > > > > > > >
                      > > > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
                      > > > > > > >
                      > > > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
                      > > > > > > >
                      > > > > > > > Next intIndex
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > Dim db As Database
                      > > > > > > >
                      > > > > > > > Dim rs As Recordset
                      > > > > > > >
                      > > > > > > > Dim prm As Parameter
                      > > > > > > >
                      > > > > > > > Dim qdf As QueryDef
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > Set db = CurrentDb()
                      > > > > > > >
                      > > > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > For Each prm In qdf.Parameters
                      > > > > > > >
                      > > > > > > > prm.Value = Eval(prm.Name)
                      > > > > > > >
                      > > > > > > > Next prm
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > Dim frm As Form
                      > > > > > > >
                      > > > > > > > Dim ctl As Control
                      > > > > > > >
                      > > > > > > > Dim varItm As Variant
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > Set frm = Forms!frmExcurs ions
                      > > > > > > >
                      > > > > > > > Set ctl = frm! lboBulkList
                      > > > > > > >
                      > > > > > > > For Each varItm In ctl.ItemsSelect ed
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > rs.Edit
                      > > > > > > >
                      > > > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
                      > > > > > > >
                      > > > > > > > rs!Date = frm!txtDate
                      > > > > > > >
                      > > > > > > > rs!Faculty = frm!cboFaculty
                      > > > > > > >
                      > > > > > > > rs!Course = frm!Course
                      > > > > > > >
                      > > > > > > > rs!Cost = frm!ExCost
                      > > > > > > >
                      > > > > > > > rs.Update
                      > > > > > > >
                      > > > > > > > Next varItm
                      > > > > > > >
                      > > > > > > > rs.Close: Set rs = Nothing
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > dixie
                      > > > > > > >
                      > > > > > > >
                      > > > > > >
                      > > > > > >
                      > > > > >
                      > > > > > "Pat" <noemail@ihates pam.bum> wrote in message
                      > > > > > news:AmFhc.8861 $NR5.255@fe1.te xas.rr.com...
                      > > > > > > Dixie,
                      > > > > > > You are looping through your selected items, but you are not[/color][/color]
                      > looping[color=green][color=darkred]
                      > > > > > through
                      > > > > > > your recordset (although looping through the recordset is only[/color][/color]
                      > going[color=green][color=darkred]
                      > > > to
                      > > > > > work
                      > > > > > > if the records are in the _exact_ same order as your listbox).[/color][/color]
                      > rs(0)[color=green][color=darkred]
                      > > > is
                      > > > > > the
                      > > > > > > first record in the returned recordset. As you loop through the
                      > > > items,
                      > > > > > you
                      > > > > > > are editing the same record.
                      > > > > > >
                      > > > > > > Instead, you want to select each record that cooresponds to each
                      > > > > selected
                      > > > > > > listbox item and then update, or, pull all your records, find[/color][/color][/color]
                      the[color=blue][color=green]
                      > > one[color=darkred]
                      > > > > that
                      > > > > > > matches the listbox item in the loop and then update.
                      > > > > > >
                      > > > > > > Hope that gets you started.
                      > > > > > > Pat
                      > > > > > >
                      > > > > > >
                      > > > > > >
                      > > > > > > "dixie" <dixiec@dogmail .com> wrote in message
                      > > > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
                      > > > > > > > I have some code that adds new records into a table for each[/color][/color][/color]
                      ID[color=blue]
                      > in[color=green]
                      > > a[color=darkred]
                      > > > > > list
                      > > > > > > > box when a button on a form is clicked. This works fine. My
                      > > > problem
                      > > > > > now
                      > > > > > > is
                      > > > > > > > that I wish to be able to edit all the records for people[/color][/color][/color]
                      whose[color=blue]
                      > ID[color=green][color=darkred]
                      > > > is
                      > > > > in
                      > > > > > > the
                      > > > > > > > list box. I made minor changes to the code (mainly replacing
                      > > > > rs.AddNew
                      > > > > > > with
                      > > > > > > > rs.Edit)and it appears to be updating only the first record[/color][/color][/color]
                      and[color=blue][color=green]
                      > > then[color=darkred]
                      > > > > > > > overwriting that record with the next, etc until it runs out[/color][/color][/color]
                      of[color=blue][color=green]
                      > > ID's[color=darkred]
                      > > > > in
                      > > > > > > the
                      > > > > > > > list box. In other words, it is stepping through the ID's in[/color][/color]
                      > the[color=green][color=darkred]
                      > > > list
                      > > > > > > box,
                      > > > > > > > but not the records. Is there a trick to this? I have spent[/color][/color]
                      > many[color=green][color=darkred]
                      > > > > hours
                      > > > > > > > doing minor changes and still have the same problem.
                      > > > > > > >
                      > > > > > > > The code follows (I have reduced the number of fields I am[/color]
                      > > updating[color=darkred]
                      > > > to
                      > > > > > > keep
                      > > > > > > > the size of the message down).
                      > > > > > > >
                      > > > > > > > Dim intIndex As Integer
                      > > > > > > >
                      > > > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
                      > > > > > > >
                      > > > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
                      > > > > > > >
                      > > > > > > > Next intIndex
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > Dim db As Database
                      > > > > > > >
                      > > > > > > > Dim rs As Recordset
                      > > > > > > >
                      > > > > > > > Dim prm As Parameter
                      > > > > > > >
                      > > > > > > > Dim qdf As QueryDef
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > Set db = CurrentDb()
                      > > > > > > >
                      > > > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > For Each prm In qdf.Parameters
                      > > > > > > >
                      > > > > > > > prm.Value = Eval(prm.Name)
                      > > > > > > >
                      > > > > > > > Next prm
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > Dim frm As Form
                      > > > > > > >
                      > > > > > > > Dim ctl As Control
                      > > > > > > >
                      > > > > > > > Dim varItm As Variant
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > Set frm = Forms!frmExcurs ions
                      > > > > > > >
                      > > > > > > > Set ctl = frm! lboBulkList
                      > > > > > > >
                      > > > > > > > For Each varItm In ctl.ItemsSelect ed
                      > > > > > > >
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > rs.Edit
                      > > > > > > >
                      > > > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
                      > > > > > > >
                      > > > > > > > rs!Date = frm!txtDate
                      > > > > > > >
                      > > > > > > > rs!Faculty = frm!cboFaculty
                      > > > > > > >
                      > > > > > > > rs!Course = frm!Course
                      > > > > > > >
                      > > > > > > > rs!Cost = frm!ExCost
                      > > > > > > >
                      > > > > > > > rs.Update
                      > > > > > > >
                      > > > > > > > Next varItm
                      > > > > > > >
                      > > > > > > > rs.Close: Set rs = Nothing
                      > > > > > > >
                      > > > > > > >
                      > > > > > > > dixie
                      > > > > > > >
                      > > > > > > >
                      > > > > > >
                      > > > > > >
                      > > > > >
                      > > > > >
                      > > > >
                      > > > >
                      > > >
                      > > >[/color]
                      > >
                      > >[/color]
                      >
                      >[/color]


                      Comment

                      • Bob Quintal

                        #12
                        Re: rs.Edit instead of rs.AddNew

                        "dixie" <dixiec@dogmail .com> wrote in
                        news:h5Xhc.177$ Ah5.9654@nnrp1. ozemail.com.au:
                        [color=blue]
                        > Joe
                        > Further to that last message, I refined my strSQL into a query
                        > that picks up only those students in the current excursion. I
                        > am now getting the error message - Runtime error 3061 Too few
                        > parameters. Expected 1.
                        >
                        > It is still halting on the same line which is the line
                        > Set rs = CurrentDb.OpenR ecordset(strSQL )
                        >[/color]
                        Since your ID 8is a text type, your sql should include some extra
                        quotes[color=blue][color=green]
                        >> strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" &
                        >> Me.lboBulkList. ItemData(0)[/color][/color]

                        strSQL = "SELECT * FROM tblExcursions WHERE [ID] =""" &
                        Me.lboBulkList. ItemData(0) & """"

                        Second, after you perform your updates, you need to do rs.update
                        to commit the changes.You also should have a rs.edit before
                        making changes to the record.

                        e.g[color=blue][color=green]
                        >> If rs.RecordCount > 0 Then[/color][/color]
                        rs.edit[color=blue][color=green]
                        >> rs!Date = frm!txtDate
                        >> rs!ID_TCHR = frm!Teacher[/color][/color]
                        ...[color=blue][color=green]
                        >> rs!Edited = -1[/color][/color]
                        rs.update[color=blue][color=green]
                        >> End If[/color][/color]

                        [color=blue]
                        > I can see the ID of the first student if I hover over the
                        > strSQL line.
                        >
                        > dixie
                        >[color=green]
                        >>
                        >> Can you see what is wrong?
                        >>
                        >> dixie[/color][/color]

                        Comment

                        • Joe Black

                          #13
                          Re: rs.Edit instead of rs.AddNew

                          Hi dixie

                          I didn't expect the ID field to be text.
                          Text needs to be enclosed in speech marks.

                          Try changing to:
                          WHERE [ID] = " & Chr(34) & Me.lboBulkList. ItemData(0) & Chr(34) & ";"

                          Regards - Joe

                          "dixie" <dixiec@dogmail .com> wrote in message
                          news:TcWhc.169$ Ah5.9387@nnrp1. ozemail.com.au. ..[color=blue]
                          > Hi Joe,
                          >
                          > I tried your idea, but I get an error 3464 - Data type mismatch in[/color]
                          criteria[color=blue]
                          > expression, which I presume is the WHERE [ID] =" &
                          > Me.lboBulkList. ItemData(0)
                          >
                          > I played around with variations, but couldn't get it to work.
                          >
                          > The ID field is a text field if this helps and is the first of two columns
                          > in the list box as well as being the bound field in the Row Source of the
                          > listbox.
                          >
                          > You are definitely right about the database design. It is an old one I[/color]
                          have[color=blue]
                          > inherited and I am at this stage just trying to add the ability to change
                          > the details of an excursion and produce a new form for all students
                          > containing those changes. Database design changes are on the agenda, but
                          > further down the track.
                          >
                          > This is what I have now.
                          >
                          > Private Sub btnEditTest2_Cl ick()
                          >
                          > Dim db As Database
                          > Dim rs As Recordset
                          > Dim strSQL As String
                          >
                          > Dim frm As Form
                          > Dim ctl As Control
                          > Dim varItm As Variant
                          >
                          > Set frm = Forms!frmExcurs ions
                          > Set ctl = frm!lboBulkList
                          >
                          > Dim i As Integer
                          > For i = 0 To Me.lboBulkList. ListCount - 1
                          >
                          > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" &
                          > Me.lboBulkList. ItemData(0)
                          >
                          > Set rs = CurrentDb.OpenR ecordset(strSQL ) <-- It is halting with[/color]
                          this[color=blue]
                          > line hilighted
                          > If rs.RecordCount > 0 Then
                          > rs!Date = frm!txtDate
                          > rs!ID_TCHR = frm!Teacher
                          > rs!Faculty = frm!cboFaculty
                          > rs!Course = frm!Course
                          > rs!ExcursionTyp e = frm!Reason
                          > rs!Details = frm!Comment
                          > rs!Signatory = frm!TxtSignator y
                          > rs!SignatoryAre a = frm!TxtSignator yArea
                          > rs!ExcursionNam e = frm!ExName
                          > rs!Cost = frm!ExCost
                          > rs!Destination = frm!ExcursionDe stination
                          > rs!DeparturePla ce = frm!DepartFrom
                          > rs!ReturningPla ce = frm!ReturnTo
                          > rs!DepartureTim e = frm!DepartTime
                          > rs!ReturningTim e = frm!ReturnTime
                          > rs!Representati ve = frm!Rep
                          > rs!Uniform = frm!Dress
                          > rs!Overnight = frm!Night
                          > rs!Commendation Required = frm!Commendatio n
                          > rs!Outdoors = frm!Outdoors
                          > rs!Travel = frm!TravelType
                          > rs!EntryDate = frm!txtEntryDat e
                          > rs!Edited = -1
                          > End If
                          > Next i
                          >
                          >
                          > End Sub
                          >
                          > Can you see what is wrong?
                          >
                          > dixie
                          >
                          >
                          > "Joe Black" <joeblack99@hot mail.com> wrote in message
                          > news:5yQhc.2415 $_s.68732@news. xtra.co.nz...[color=green]
                          > > Hi Dixie
                          > >
                          > > In your first version of the code you first select all items in the[/color]
                          > listbox[color=green]
                          > > and then iterate through all the selected items.
                          > > If you want to process all the list items, it is not necessary to select
                          > > them all first.
                          > >
                          > > Instead of "For Each varItm In ctl.ItemsSelect ed"
                          > > you can do something like:
                          > >
                          > > Dim i As Integer
                          > > For i = 0 To Me.lboBulkList. ListCount - 1
                          > >
                          > > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" &
                          > > Me.lboBulkList. ItemData(0)
                          > > Set rs = CurrentDb.OpenR ecordset(strSQL )
                          > > If rs.RecordCount > 0 Then
                          > > rs!Date = frm!txtDate
                          > > ...
                          > > end if
                          > > Next i
                          > >
                          > > You haven't allowed for the possibility that rs.RecordCount might be >[/color][/color]
                          1.[color=blue][color=green]
                          > >
                          > > I also wonder whether the design of your database could be improved. If[/color]
                          > the[color=green]
                          > > details of an Excursion are needed to be changed, you should only have[/color][/color]
                          to[color=blue][color=green]
                          > > change the details in one row of a table, not repeatedly change the same
                          > > details for many rows.
                          > >
                          > > Regards - Joe
                          > >
                          > > "dixie" <dixiec@dogmail .com> wrote in message
                          > > news:hlIhc.38$A h5.2978@nnrp1.o zemail.com.au.. .[color=darkred]
                          > > > Ok, tried that and strangely, I get exactly the same problem - that[/color][/color][/color]
                          is,[color=blue]
                          > it[color=green][color=darkred]
                          > > > updates the first record, but not the others. I did not understand[/color][/color][/color]
                          your[color=blue][color=green][color=darkred]
                          > > > statement about you will need a loop to update each record in the[/color][/color][/color]
                          set -[color=blue]
                          > is[color=green][color=darkred]
                          > > > this the bit I have missed.
                          > > >
                          > > > The general idea is that I have a list of details for an excursion[/color][/color][/color]
                          that[color=blue]
                          > a[color=green][color=darkred]
                          > > > group of students are going on. I print a form out for each one and I[/color]
                          > > print[color=darkred]
                          > > > a list of students attending. Now, I already have that bit working.[/color][/color]
                          > The[color=green][color=darkred]
                          > > > bit I am trying to get here is if there was a mistake in the details[/color][/color][/color]
                          or[color=blue][color=green]
                          > > more[color=darkred]
                          > > > information had come to hand, I wan't to be able to do a "bulk edit"[/color][/color][/color]
                          on[color=blue][color=green]
                          > > each[color=darkred]
                          > > > entry (one per student). The ID is a unique student ID and there[/color][/color][/color]
                          would[color=blue]
                          > be[color=green]
                          > > a[color=darkred]
                          > > > number of them (up to 100) in the list box, lboBulkList.
                          > > >
                          > > > Now, this is the fine detail and is currently what I interpreted your
                          > > > previous post into.
                          > > >
                          > > > Private Sub btnEditTest_Cli ck()
                          > > > Dim db As Database
                          > > > Dim rs As Recordset
                          > > > Dim strSQL As String
                          > > >
                          > > > Dim frm As Form
                          > > > Dim ctl As Control
                          > > > Dim varItm As Variant
                          > > >
                          > > > Set frm = Forms!frmExcurs ions
                          > > > Set ctl = frm!lboBulkList
                          > > >
                          > > > For Each varItm In ctl.ItemsSelect ed
                          > > > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
                          > > >
                          > > > Set rs = CurrentDb.OpenR ecordset(strSQL )
                          > > > If rs.RecordCount > 0 Then
                          > > > rs!Date = frm!txtDate
                          > > > rs!ID_TCHR = frm!Teacher
                          > > > rs!Faculty = frm!cboFaculty
                          > > > rs!Course = frm!Course
                          > > > rs!ExcursionTyp e = frm!Reason
                          > > > rs!Details = frm!Comment
                          > > > rs!Signatory = frm!TxtSignator y
                          > > > rs!SignatoryAre a = frm!TxtSignator yArea
                          > > > rs!ExcursionNam e = frm!ExName
                          > > > rs!Cost = frm!ExCost
                          > > > rs!Destination = frm!ExcursionDe stination
                          > > > rs!DeparturePla ce = frm!DepartFrom
                          > > > rs!ReturningPla ce = frm!ReturnTo
                          > > > rs!DepartureTim e = frm!DepartTime
                          > > > rs!ReturningTim e = frm!ReturnTime
                          > > > rs!Representati ve = frm!Rep
                          > > > rs!Uniform = frm!Dress
                          > > > rs!Overnight = frm!Night
                          > > > rs!Commendation Required = frm!Commendatio n
                          > > > rs!Outdoors = frm!Outdoors
                          > > > rs!Travel = frm!TravelType
                          > > > rs!EntryDate = frm!txtEntryDat e
                          > > > rs!Edited = -1
                          > > > End If
                          > > > Next varItm
                          > > > End sub
                          > > >
                          > > > "Pat" <noemail@ihates pam.bum> wrote in message
                          > > > news:IjHhc.1170 8$hR1.10135@fe2 .texas.rr.com.. .
                          > > > > Dixie,
                          > > > > Your original post and your latest reply contains a loop that looks[/color][/color][/color]
                          to[color=blue][color=green][color=darkred]
                          > > > have
                          > > > > originally been designed to loop through all records in a recordset[/color][/color]
                          > and[color=green][color=darkred]
                          > > > > update fields. From your description, you want to update only[/color][/color][/color]
                          records[color=blue][color=green][color=darkred]
                          > > > > listed in a listbox, for which you have their ID.
                          > > > >
                          > > > > If your listbox will likely contain a few items, it could create a[/color]
                          > > dynamic[color=darkred]
                          > > > > SLQ statment that selects only the record you want to edit. Then[/color][/color][/color]
                          you[color=blue][color=green]
                          > > can[color=darkred]
                          > > > > edit that record.
                          > > > >
                          > > > > Set frm = Forms!frmExcurs ions
                          > > > > Set ctl = frm! lboBulkList
                          > > > > For Each varItm In ctl.ItemsSelect ed
                          > > > > strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
                          > > > > Set rs = CurrentDb.OpenR ecordset(strSql )
                          > > > > If rs.RecordCount > 0 Then
                          > > > > rs.FieldNameToE dit = YourNewValue
                          > > > > End if
                          > > > > Next varItm
                          > > > >
                          > > > > If you have more than one record that will return in the recordset,[/color][/color]
                          > you[color=green][color=darkred]
                          > > > will
                          > > > > need a loop to handle update each record in the set.
                          > > > >
                          > > > > Hope this helps,
                          > > > > Pat
                          > > > >
                          > > > >
                          > > > >
                          > > > > "dixie" <dixiec@dogmail .com> wrote in message
                          > > > > news:gXFhc.17$A h5.1849@nnrp1.o zemail.com.au.. .
                          > > > > > OK Pat, I thought as much. You have confirmed what I believed was
                          > > > > > happening. My problem is that I don't know how to combine the[/color]
                          > > previous[color=darkred]
                          > > > > type
                          > > > > > of code that updates according to an ID in a list box and then[/color][/color][/color]
                          this[color=blue][color=green]
                          > > type[color=darkred]
                          > > > > of
                          > > > > > code
                          > > > > >
                          > > > > > With rs
                          > > > > >
                          > > > > > If rs.RecordCount > 0 Then
                          > > > > > .MoveFirst
                          > > > > > Do
                          > > > > > .Edit
                          > > > > > rs!Cost = 0
                          > > > > > .Update
                          > > > > > .MoveNext
                          > > > > > Loop Until .EOF
                          > > > > > End If
                          > > > > > .Close: Set rs = Nothing
                          > > > > >
                          > > > > > End With
                          > > > > >
                          > > > > > I know that I need to step through the records in the recordset[/color][/color][/color]
                          and[color=blue][color=green]
                          > > edit[color=darkred]
                          > > > > > each field according to my ID in the list box, but I can't seem to
                          > > > combine
                          > > > > > the two ideas to get to the solution I want - which is for each[/color][/color]
                          > person[color=green][color=darkred]
                          > > > > > selected to have their specific record edited according to a what[/color][/color][/color]
                          is[color=blue][color=green]
                          > > in[color=darkred]
                          > > > > > various controls on the form.
                          > > > > >
                          > > > > > dixie
                          > > > > >
                          > > > > > ----- Original Message -----
                          > > > > > From: "Pat" <noemail@ihates pam.bum>
                          > > > > > Newsgroups: comp.databases. ms-access
                          > > > > > Sent: Thursday, April 22, 2004 11:38 AM
                          > > > > > Subject: Re: rs.Edit instead of rs.AddNew
                          > > > > >
                          > > > > >
                          > > > > > > Dixie,
                          > > > > > > You are looping through your selected items, but you are not[/color][/color]
                          > looping[color=green][color=darkred]
                          > > > > > through
                          > > > > > > your recordset (although looping through the recordset is only[/color][/color]
                          > going[color=green][color=darkred]
                          > > > to
                          > > > > > work
                          > > > > > > if the records are in the _exact_ same order as your listbox).[/color][/color]
                          > rs(0)[color=green][color=darkred]
                          > > > is
                          > > > > > the
                          > > > > > > first record in the returned recordset. As you loop through the
                          > > > items,
                          > > > > > you
                          > > > > > > are editing the same record.
                          > > > > > >
                          > > > > > > Instead, you want to select each record that cooresponds to each
                          > > > > selected
                          > > > > > > listbox item and then update, or, pull all your records, find[/color][/color][/color]
                          the[color=blue][color=green]
                          > > one[color=darkred]
                          > > > > that
                          > > > > > > matches the listbox item in the loop and then update.
                          > > > > > >
                          > > > > > > Hope that gets you started.
                          > > > > > > Pat
                          > > > > > >
                          > > > > > >
                          > > > > > >
                          > > > > > > "dixie" <dixiec@dogmail .com> wrote in message
                          > > > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
                          > > > > > > > I have some code that adds new records into a table for each[/color][/color][/color]
                          ID[color=blue]
                          > in[color=green]
                          > > a[color=darkred]
                          > > > > > list
                          > > > > > > > box when a button on a form is clicked. This works fine. My
                          > > > problem
                          > > > > > now
                          > > > > > > is
                          > > > > > > > that I wish to be able to edit all the records for people[/color][/color][/color]
                          whose[color=blue]
                          > ID[color=green][color=darkred]
                          > > > is
                          > > > > in
                          > > > > > > the
                          > > > > > > > list box. I made minor changes to the code (mainly replacing
                          > > > > rs.AddNew
                          > > > > > > with
                          > > > > > > > rs.Edit)and it appears to be updating only the first record[/color][/color][/color]
                          and[color=blue][color=green]
                          > > then[color=darkred]
                          > > > > > > > overwriting that record with the next, etc until it runs out[/color][/color][/color]
                          of[color=blue][color=green]
                          > > ID's[color=darkred]
                          > > > > in
                          > > > > > > the
                          > > > > > > > list box. In other words, it is stepping through the ID's in[/color][/color]
                          > the[color=green][color=darkred]
                          > > > list
                          > > > > > > box,
                          > > > > > > > but not the records. Is there a trick to this? I have spent[/color][/color]
                          > many[color=green][color=darkred]
                          > > > > hours
                          > > > > > > > doing minor changes and still have the same problem.
                          > > > > > > >
                          > > > > > > > The code follows (I have reduced the number of fields I am[/color]
                          > > updating[color=darkred]
                          > > > to
                          > > > > > > keep
                          > > > > > > > the size of the message down).
                          > > > > > > >
                          > > > > > > > Dim intIndex As Integer
                          > > > > > > >
                          > > > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
                          > > > > > > >
                          > > > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
                          > > > > > > >
                          > > > > > > > Next intIndex
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > Dim db As Database
                          > > > > > > >
                          > > > > > > > Dim rs As Recordset
                          > > > > > > >
                          > > > > > > > Dim prm As Parameter
                          > > > > > > >
                          > > > > > > > Dim qdf As QueryDef
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > Set db = CurrentDb()
                          > > > > > > >
                          > > > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > For Each prm In qdf.Parameters
                          > > > > > > >
                          > > > > > > > prm.Value = Eval(prm.Name)
                          > > > > > > >
                          > > > > > > > Next prm
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > Dim frm As Form
                          > > > > > > >
                          > > > > > > > Dim ctl As Control
                          > > > > > > >
                          > > > > > > > Dim varItm As Variant
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > Set frm = Forms!frmExcurs ions
                          > > > > > > >
                          > > > > > > > Set ctl = frm! lboBulkList
                          > > > > > > >
                          > > > > > > > For Each varItm In ctl.ItemsSelect ed
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > rs.Edit
                          > > > > > > >
                          > > > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
                          > > > > > > >
                          > > > > > > > rs!Date = frm!txtDate
                          > > > > > > >
                          > > > > > > > rs!Faculty = frm!cboFaculty
                          > > > > > > >
                          > > > > > > > rs!Course = frm!Course
                          > > > > > > >
                          > > > > > > > rs!Cost = frm!ExCost
                          > > > > > > >
                          > > > > > > > rs.Update
                          > > > > > > >
                          > > > > > > > Next varItm
                          > > > > > > >
                          > > > > > > > rs.Close: Set rs = Nothing
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > dixie
                          > > > > > > >
                          > > > > > > >
                          > > > > > >
                          > > > > > >
                          > > > > >
                          > > > > > "Pat" <noemail@ihates pam.bum> wrote in message
                          > > > > > news:AmFhc.8861 $NR5.255@fe1.te xas.rr.com...
                          > > > > > > Dixie,
                          > > > > > > You are looping through your selected items, but you are not[/color][/color]
                          > looping[color=green][color=darkred]
                          > > > > > through
                          > > > > > > your recordset (although looping through the recordset is only[/color][/color]
                          > going[color=green][color=darkred]
                          > > > to
                          > > > > > work
                          > > > > > > if the records are in the _exact_ same order as your listbox).[/color][/color]
                          > rs(0)[color=green][color=darkred]
                          > > > is
                          > > > > > the
                          > > > > > > first record in the returned recordset. As you loop through the
                          > > > items,
                          > > > > > you
                          > > > > > > are editing the same record.
                          > > > > > >
                          > > > > > > Instead, you want to select each record that cooresponds to each
                          > > > > selected
                          > > > > > > listbox item and then update, or, pull all your records, find[/color][/color][/color]
                          the[color=blue][color=green]
                          > > one[color=darkred]
                          > > > > that
                          > > > > > > matches the listbox item in the loop and then update.
                          > > > > > >
                          > > > > > > Hope that gets you started.
                          > > > > > > Pat
                          > > > > > >
                          > > > > > >
                          > > > > > >
                          > > > > > > "dixie" <dixiec@dogmail .com> wrote in message
                          > > > > > > news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au ...
                          > > > > > > > I have some code that adds new records into a table for each[/color][/color][/color]
                          ID[color=blue]
                          > in[color=green]
                          > > a[color=darkred]
                          > > > > > list
                          > > > > > > > box when a button on a form is clicked. This works fine. My
                          > > > problem
                          > > > > > now
                          > > > > > > is
                          > > > > > > > that I wish to be able to edit all the records for people[/color][/color][/color]
                          whose[color=blue]
                          > ID[color=green][color=darkred]
                          > > > is
                          > > > > in
                          > > > > > > the
                          > > > > > > > list box. I made minor changes to the code (mainly replacing
                          > > > > rs.AddNew
                          > > > > > > with
                          > > > > > > > rs.Edit)and it appears to be updating only the first record[/color][/color][/color]
                          and[color=blue][color=green]
                          > > then[color=darkred]
                          > > > > > > > overwriting that record with the next, etc until it runs out[/color][/color][/color]
                          of[color=blue][color=green]
                          > > ID's[color=darkred]
                          > > > > in
                          > > > > > > the
                          > > > > > > > list box. In other words, it is stepping through the ID's in[/color][/color]
                          > the[color=green][color=darkred]
                          > > > list
                          > > > > > > box,
                          > > > > > > > but not the records. Is there a trick to this? I have spent[/color][/color]
                          > many[color=green][color=darkred]
                          > > > > hours
                          > > > > > > > doing minor changes and still have the same problem.
                          > > > > > > >
                          > > > > > > > The code follows (I have reduced the number of fields I am[/color]
                          > > updating[color=darkred]
                          > > > to
                          > > > > > > keep
                          > > > > > > > the size of the message down).
                          > > > > > > >
                          > > > > > > > Dim intIndex As Integer
                          > > > > > > >
                          > > > > > > > For intIndex = 0 To Me.lboBulkList. ListCount
                          > > > > > > >
                          > > > > > > > Me.lbo.BulkList .Selected(intIn dex) = True
                          > > > > > > >
                          > > > > > > > Next intIndex
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > Dim db As Database
                          > > > > > > >
                          > > > > > > > Dim rs As Recordset
                          > > > > > > >
                          > > > > > > > Dim prm As Parameter
                          > > > > > > >
                          > > > > > > > Dim qdf As QueryDef
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > Set db = CurrentDb()
                          > > > > > > >
                          > > > > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > For Each prm In qdf.Parameters
                          > > > > > > >
                          > > > > > > > prm.Value = Eval(prm.Name)
                          > > > > > > >
                          > > > > > > > Next prm
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > Dim frm As Form
                          > > > > > > >
                          > > > > > > > Dim ctl As Control
                          > > > > > > >
                          > > > > > > > Dim varItm As Variant
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > Set frm = Forms!frmExcurs ions
                          > > > > > > >
                          > > > > > > > Set ctl = frm! lboBulkList
                          > > > > > > >
                          > > > > > > > For Each varItm In ctl.ItemsSelect ed
                          > > > > > > >
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > rs.Edit
                          > > > > > > >
                          > > > > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
                          > > > > > > >
                          > > > > > > > rs!Date = frm!txtDate
                          > > > > > > >
                          > > > > > > > rs!Faculty = frm!cboFaculty
                          > > > > > > >
                          > > > > > > > rs!Course = frm!Course
                          > > > > > > >
                          > > > > > > > rs!Cost = frm!ExCost
                          > > > > > > >
                          > > > > > > > rs.Update
                          > > > > > > >
                          > > > > > > > Next varItm
                          > > > > > > >
                          > > > > > > > rs.Close: Set rs = Nothing
                          > > > > > > >
                          > > > > > > >
                          > > > > > > > dixie
                          > > > > > > >
                          > > > > > > >
                          > > > > > >
                          > > > > > >
                          > > > > >
                          > > > > >
                          > > > >
                          > > > >
                          > > >
                          > > >[/color]
                          > >
                          > >[/color]
                          >
                          >[/color]


                          Comment

                          • dixie

                            #14
                            Re: rs.Edit instead of rs.AddNew

                            Thanks Bob, that was the problem I think, I have text ID's, because some of
                            them have an alphnumeric combination in them. I have put back the rs.edit
                            and rs.update that I originally had, but had been removed while I was trying
                            other ideas.

                            dixie

                            "Bob Quintal" <bquintal@gener ation.net> wrote in message
                            news:a5a8dc5a79 0fa800603c33da1 8dc433c@news.te ranews.com...[color=blue]
                            > "dixie" <dixiec@dogmail .com> wrote in
                            > news:h5Xhc.177$ Ah5.9654@nnrp1. ozemail.com.au:
                            >[color=green]
                            > > Joe
                            > > Further to that last message, I refined my strSQL into a query
                            > > that picks up only those students in the current excursion. I
                            > > am now getting the error message - Runtime error 3061 Too few
                            > > parameters. Expected 1.
                            > >
                            > > It is still halting on the same line which is the line
                            > > Set rs = CurrentDb.OpenR ecordset(strSQL )
                            > >[/color]
                            > Since your ID 8is a text type, your sql should include some extra
                            > quotes[color=green][color=darkred]
                            > >> strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" &
                            > >> Me.lboBulkList. ItemData(0)[/color][/color]
                            >
                            > strSQL = "SELECT * FROM tblExcursions WHERE [ID] =""" &
                            > Me.lboBulkList. ItemData(0) & """"
                            >
                            > Second, after you perform your updates, you need to do rs.update
                            > to commit the changes.You also should have a rs.edit before
                            > making changes to the record.
                            >
                            > e.g[color=green][color=darkred]
                            > >> If rs.RecordCount > 0 Then[/color][/color]
                            > rs.edit[color=green][color=darkred]
                            > >> rs!Date = frm!txtDate
                            > >> rs!ID_TCHR = frm!Teacher[/color][/color]
                            > ...[color=green][color=darkred]
                            > >> rs!Edited = -1[/color][/color]
                            > rs.update[color=green][color=darkred]
                            > >> End If[/color][/color]
                            >
                            >[color=green]
                            > > I can see the ID of the first student if I hover over the
                            > > strSQL line.
                            > >
                            > > dixie
                            > >[color=darkred]
                            > >>
                            > >> Can you see what is wrong?
                            > >>
                            > >> dixie[/color][/color][/color]


                            Comment

                            • David W. Fenton

                              #15
                              Re: rs.Edit instead of rs.AddNew

                              "Pat" <noemail@ihates pam.bum> wrote in
                              news:IjHhc.1170 8$hR1.10135@fe2 .texas.rr.com:
                              [color=blue]
                              > Set frm = Forms!frmExcurs ions
                              > Set ctl = frm! lboBulkList
                              > For Each varItm In ctl.ItemsSelect ed
                              > strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
                              > Set rs = CurrentDb.OpenR ecordset(strSql )
                              > If rs.RecordCount > 0 Then
                              > rs.FieldNameToE dit = YourNewValue
                              > End if
                              > Next varItm[/color]

                              That's invalid syntax for referring to fields in recordset -- you
                              must use the !, not the dot.

                              --
                              David W. Fenton http://www.bway.net/~dfenton
                              dfenton at bway dot net http://www.bway.net/~dfassoc

                              Comment

                              Working...