rs.Edit instead of rs.AddNew

[dixie]

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]

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" <di****@dogmail .com> wrote in message
news:VZ******** *********@nnrp1 .ozemail.com.au ...

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

[Pat]

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" <di****@dogmail .com> wrote in message
news:VZ******** *********@nnrp1 .ozemail.com.au ...

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

[dixie]

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" <no*****@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 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" <di****@dogmail .com> wrote in message
news:VZ******** *********@nnrp1 .ozemail.com.au ...

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

"Pat" <no*****@ihates pam.bum> wrote in message
news:Am******** ********@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 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" <di****@dogmail .com> wrote in message
news:VZ******** *********@nnrp1 .ozemail.com.au ...

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

[Pat]

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" <di****@dogmail .com> wrote in message
news:gX******** *******@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 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" <no*****@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 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" <di****@dogmail .com> wrote in message
news:VZ******** *********@nnrp1 .ozemail.com.au ...

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

"Pat" <no*****@ihates pam.bum> wrote in message
news:Am******** ********@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 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" <di****@dogmail .com> wrote in message
news:VZ******** *********@nnrp1 .ozemail.com.au ...

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

[dixie]

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" <no*****@ihates pam.bum> wrote in message
news:Ij******** ***********@fe2 .texas.rr.com.. .

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" <di****@dogmail .com> wrote in message
news:gX******** *******@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 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" <no*****@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 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" <di****@dogmail .com> wrote in message
news:VZ******** *********@nnrp1 .ozemail.com.au ...
> 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
>
>

"Pat" <no*****@ihates pam.bum> wrote in message
news:Am******** ********@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 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" <di****@dogmail .com> wrote in message
news:VZ******** *********@nnrp1 .ozemail.com.au ...
> 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
>
>

[Pat]

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" <di****@dogmail .com> wrote in message
news:hl******** *******@nnrp1.o zemail.com.au.. .

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" <no*****@ihates pam.bum> wrote in message
news:Ij******** ***********@fe2 .texas.rr.com.. .

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" <di****@dogmail .com> wrote in message
news:gX******** *******@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 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" <no*****@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 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" <di****@dogmail .com> wrote in message
> news:VZ******** *********@nnrp1 .ozemail.com.au ...
> > 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
> >
> >
>
>

"Pat" <no*****@ihates pam.bum> wrote in message
news:Am******** ********@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 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" <di****@dogmail .com> wrote in message
> news:VZ******** *********@nnrp1 .ozemail.com.au ...
> > 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
> >
> >
>
>

[Joe Black]

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" <di****@dogmail .com> wrote in message
news:hl******** *******@nnrp1.o zemail.com.au.. .

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" <no*****@ihates pam.bum> wrote in message
news:Ij******** ***********@fe2 .texas.rr.com.. .

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" <di****@dogmail .com> wrote in message
news:gX******** *******@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 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" <no*****@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 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" <di****@dogmail .com> wrote in message
> news:VZ******** *********@nnrp1 .ozemail.com.au ...
> > 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
> >
> >
>
>

"Pat" <no*****@ihates pam.bum> wrote in message
news:Am******** ********@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 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" <di****@dogmail .com> wrote in message
> news:VZ******** *********@nnrp1 .ozemail.com.au ...
> > 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
> >
> >
>
>

[dixie]

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" <no*****@ihates pam.bum> wrote in message
news:RS******** **********@fe1. texas.rr.com...

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" <di****@dogmail .com> wrote in message
news:hl******** *******@nnrp1.o zemail.com.au.. .

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" <no*****@ihates pam.bum> wrote in message
news:Ij******** ***********@fe2 .texas.rr.com.. .

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" <di****@dogmail .com> wrote in message
news:gX******** *******@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 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" <no*****@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 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" <di****@dogmail .com> wrote in message
> > news:VZ******** *********@nnrp1 .ozemail.com.au ...
> > > 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
> > >
> > >
> >
> >
>
> "Pat" <no*****@ihates pam.bum> wrote in message
> news:Am******** ********@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 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" <di****@dogmail .com> wrote in message
> > news:VZ******** *********@nnrp1 .ozemail.com.au ...
> > > 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
> > >
> > >
> >
> >
>
>

[dixie]

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" <jo********@hot mail.com> wrote in message
news:5y******** *********@news. xtra.co.nz...

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" <di****@dogmail .com> wrote in message
news:hl******** *******@nnrp1.o zemail.com.au.. .

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" <no*****@ihates pam.bum> wrote in message
news:Ij******** ***********@fe2 .texas.rr.com.. .

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" <di****@dogmail .com> wrote in message
news:gX******** *******@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 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" <no*****@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 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" <di****@dogmail .com> wrote in message
> > news:VZ******** *********@nnrp1 .ozemail.com.au ...
> > > 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
> > >
> > >
> >
> >
>
> "Pat" <no*****@ihates pam.bum> wrote in message
> news:Am******** ********@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 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" <di****@dogmail .com> wrote in message
> > news:VZ******** *********@nnrp1 .ozemail.com.au ...
> > > 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
> > >
> > >
> >
> >
>
>