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 25 1800
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
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
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,
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
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,
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 > > > > > >
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 > > > > > >
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 > > > > > > > > > > > >
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 > > > > > > > > > > > >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: dixie |
last post by:
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...
|
by: MissiMaths |
last post by:
I am creating a reference table of all the information used to draw a
sample. I start by first clearing the table in form1 using a delete
query.
Then after the user selects certain options, i create a string
"optionString" and put it into my table refTab in the appropriate
field.
DoCmd.OpenQuery "ClearRefTable"
Dim MyDB As Database
|
by: bhcob1 |
last post by:
Hey guys,
'Update or CancelUpdate without AddNew or Edit'
On my database i keep occasionly get this error when i try and edit a field, it is not everytime. It will be working fine and then this error appears. I will be editing records and then a random one will get the error.
A bit of background on my form, this will seem a bit lengthy but here is my code.
The form has a navigation list which the user can select a record to view.
An...
|
by: mady1380 |
last post by:
Hi All
I am new with Windows form. i am working on c#.net 2003. i have no idea how am i suppose to Edit, AddNew or Delete Record in DataGrid. I am passing a Dataset to my Grid. I want to modify,addnew and delete records in Grid. I know it must be very simple and easy, but i have no idea i have been searching and i am not able to find any proper solution. Any help would be really appreciated..Plz help me out guys.
Madhu
|
by: David |
last post by:
Dear All
I'm trying to use a multi-select listbox as criteria to either add new
record or edit the current record of the destiantation table with each
row selected in the box. This works fine for adding a new record but
once records exist in the destination table, the routine seems to
overwrite the first highlighted record in the destination table with
the last highlighted record record, therefore giving two duplicate
records. I've...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |