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(intIndex) = 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("qryBulkEdit")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set frm = Forms!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
rs.Edit
rs(0) = Me. lboBulkList.ItemData(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  10340 
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(intIndex) = 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("qryBulkEdit")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set frm = Forms!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
rs.Edit
rs(0) = Me. lboBulkList.ItemData(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(intIndex) = 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("qryBulkEdit")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set frm = Forms!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
rs.Edit
rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.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(intIndex) = 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("qryBulkEdit")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set frm = Forms!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
rs.Edit
rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.bum> wrote in message
news:Am****************@fe1.texas.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(intIndex) = 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("qryBulkEdit")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set frm = Forms!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
rs.Edit
rs(0) = Me. lboBulkList.ItemData(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!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
rs.FieldNameToEdit = 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.ozemail.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*****@ihatespam.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(intIndex) = 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("qryBulkEdit")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set frm = Forms!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
rs.Edit
rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.bum> wrote in message
news:Am****************@fe1.texas.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(intIndex) = 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("qryBulkEdit")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set frm = Forms!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
rs.Edit
rs(0) = Me. lboBulkList.ItemData(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_Click()
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!frmExcursions
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs!Date = frm!txtDate
rs!ID_TCHR = frm!Teacher
rs!Faculty = frm!cboFaculty
rs!Course = frm!Course
rs!ExcursionType = frm!Reason
rs!Details = frm!Comment
rs!Signatory = frm!TxtSignatory
rs!SignatoryArea = frm!TxtSignatoryArea
rs!ExcursionName = frm!ExName
rs!Cost = frm!ExCost
rs!Destination = frm!ExcursionDestination
rs!DeparturePlace = frm!DepartFrom
rs!ReturningPlace = frm!ReturnTo
rs!DepartureTime = frm!DepartTime
rs!ReturningTime = frm!ReturnTime
rs!Representative = frm!Rep
rs!Uniform = frm!Dress
rs!Overnight = frm!Night
rs!CommendationRequired = frm!Commendation
rs!Outdoors = frm!Outdoors
rs!Travel = frm!TravelType
rs!EntryDate = frm!txtEntryDate
rs!Edited = -1
End If
Next varItm
End sub
"Pat" <no*****@ihatespam.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!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
rs.FieldNameToEdit = 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.ozemail.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*****@ihatespam.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(intIndex) = 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("qryBulkEdit")
>
>
>
> For Each prm In qdf.Parameters
>
> prm.Value = Eval(prm.Name)
>
> Next prm
>
>
>
> Set rs = qdf.OpenRecordset(dbOpenDynaset)
>
>
>
> Dim frm As Form
>
> Dim ctl As Control
>
> Dim varItm As Variant
>
>
>
> Set frm = Forms!frmExcursions
>
> Set ctl = frm! lboBulkList
>
> For Each varItm In ctl.ItemsSelected
>
>
>
> rs.Edit
>
> rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.bum> wrote in message
news:Am****************@fe1.texas.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(intIndex) = 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("qryBulkEdit")
>
>
>
> For Each prm In qdf.Parameters
>
> prm.Value = Eval(prm.Name)
>
> Next prm
>
>
>
> Set rs = qdf.OpenRecordset(dbOpenDynaset)
>
>
>
> Dim frm As Form
>
> Dim ctl As Control
>
> Dim varItm As Variant
>
>
>
> Set frm = Forms!frmExcursions
>
> Set ctl = frm! lboBulkList
>
> For Each varItm In ctl.ItemsSelected
>
>
>
> rs.Edit
>
> rs(0) = Me. lboBulkList.ItemData(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.ItemsSelected
strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
Set rs = CurrentDb.OpenRecordset(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.ozemail.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_Click()
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!frmExcursions
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs!Date = frm!txtDate
rs!ID_TCHR = frm!Teacher
rs!Faculty = frm!cboFaculty
rs!Course = frm!Course
rs!ExcursionType = frm!Reason
rs!Details = frm!Comment
rs!Signatory = frm!TxtSignatory
rs!SignatoryArea = frm!TxtSignatoryArea
rs!ExcursionName = frm!ExName
rs!Cost = frm!ExCost
rs!Destination = frm!ExcursionDestination
rs!DeparturePlace = frm!DepartFrom
rs!ReturningPlace = frm!ReturnTo
rs!DepartureTime = frm!DepartTime
rs!ReturningTime = frm!ReturnTime
rs!Representative = frm!Rep
rs!Uniform = frm!Dress
rs!Overnight = frm!Night
rs!CommendationRequired = frm!Commendation
rs!Outdoors = frm!Outdoors
rs!Travel = frm!TravelType
rs!EntryDate = frm!txtEntryDate
rs!Edited = -1
End If
Next varItm
End sub
"Pat" <no*****@ihatespam.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!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
rs.FieldNameToEdit = 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.ozemail.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*****@ihatespam.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(intIndex) = 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("qryBulkEdit")
> >
> >
> >
> > For Each prm In qdf.Parameters
> >
> > prm.Value = Eval(prm.Name)
> >
> > Next prm
> >
> >
> >
> > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> >
> >
> >
> > Dim frm As Form
> >
> > Dim ctl As Control
> >
> > Dim varItm As Variant
> >
> >
> >
> > Set frm = Forms!frmExcursions
> >
> > Set ctl = frm! lboBulkList
> >
> > For Each varItm In ctl.ItemsSelected
> >
> >
> >
> > rs.Edit
> >
> > rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.bum> wrote in message
news:Am****************@fe1.texas.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(intIndex) = 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("qryBulkEdit")
> >
> >
> >
> > For Each prm In qdf.Parameters
> >
> > prm.Value = Eval(prm.Name)
> >
> > Next prm
> >
> >
> >
> > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> >
> >
> >
> > Dim frm As Form
> >
> > Dim ctl As Control
> >
> > Dim varItm As Variant
> >
> >
> >
> > Set frm = Forms!frmExcursions
> >
> > Set ctl = frm! lboBulkList
> >
> > For Each varItm In ctl.ItemsSelected
> >
> >
> >
> > rs.Edit
> >
> > rs(0) = Me. lboBulkList.ItemData(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.ItemsSelected"
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.OpenRecordset(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.ozemail.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_Click()
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!frmExcursions
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs!Date = frm!txtDate
rs!ID_TCHR = frm!Teacher
rs!Faculty = frm!cboFaculty
rs!Course = frm!Course
rs!ExcursionType = frm!Reason
rs!Details = frm!Comment
rs!Signatory = frm!TxtSignatory
rs!SignatoryArea = frm!TxtSignatoryArea
rs!ExcursionName = frm!ExName
rs!Cost = frm!ExCost
rs!Destination = frm!ExcursionDestination
rs!DeparturePlace = frm!DepartFrom
rs!ReturningPlace = frm!ReturnTo
rs!DepartureTime = frm!DepartTime
rs!ReturningTime = frm!ReturnTime
rs!Representative = frm!Rep
rs!Uniform = frm!Dress
rs!Overnight = frm!Night
rs!CommendationRequired = frm!Commendation
rs!Outdoors = frm!Outdoors
rs!Travel = frm!TravelType
rs!EntryDate = frm!txtEntryDate
rs!Edited = -1
End If
Next varItm
End sub
"Pat" <no*****@ihatespam.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!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
rs.FieldNameToEdit = 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.ozemail.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*****@ihatespam.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(intIndex) = 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("qryBulkEdit")
> >
> >
> >
> > For Each prm In qdf.Parameters
> >
> > prm.Value = Eval(prm.Name)
> >
> > Next prm
> >
> >
> >
> > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> >
> >
> >
> > Dim frm As Form
> >
> > Dim ctl As Control
> >
> > Dim varItm As Variant
> >
> >
> >
> > Set frm = Forms!frmExcursions
> >
> > Set ctl = frm! lboBulkList
> >
> > For Each varItm In ctl.ItemsSelected
> >
> >
> >
> > rs.Edit
> >
> > rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.bum> wrote in message
news:Am****************@fe1.texas.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(intIndex) = 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("qryBulkEdit")
> >
> >
> >
> > For Each prm In qdf.Parameters
> >
> > prm.Value = Eval(prm.Name)
> >
> > Next prm
> >
> >
> >
> > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> >
> >
> >
> > Dim frm As Form
> >
> > Dim ctl As Control
> >
> > Dim varItm As Variant
> >
> >
> >
> > Set frm = Forms!frmExcursions
> >
> > Set ctl = frm! lboBulkList
> >
> > For Each varItm In ctl.ItemsSelected
> >
> >
> >
> > rs.Edit
> >
> > rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.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.ItemsSelected
strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
Set rs = CurrentDb.OpenRecordset(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.ozemail.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_Click()
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!frmExcursions
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs!Date = frm!txtDate
rs!ID_TCHR = frm!Teacher
rs!Faculty = frm!cboFaculty
rs!Course = frm!Course
rs!ExcursionType = frm!Reason
rs!Details = frm!Comment
rs!Signatory = frm!TxtSignatory
rs!SignatoryArea = frm!TxtSignatoryArea
rs!ExcursionName = frm!ExName
rs!Cost = frm!ExCost
rs!Destination = frm!ExcursionDestination
rs!DeparturePlace = frm!DepartFrom
rs!ReturningPlace = frm!ReturnTo
rs!DepartureTime = frm!DepartTime
rs!ReturningTime = frm!ReturnTime
rs!Representative = frm!Rep
rs!Uniform = frm!Dress
rs!Overnight = frm!Night
rs!CommendationRequired = frm!Commendation
rs!Outdoors = frm!Outdoors
rs!Travel = frm!TravelType
rs!EntryDate = frm!txtEntryDate
rs!Edited = -1
End If
Next varItm
End sub
"Pat" <no*****@ihatespam.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!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
rs.FieldNameToEdit = 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.ozemail.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*****@ihatespam.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(intIndex) = 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("qryBulkEdit")
> > >
> > >
> > >
> > > For Each prm In qdf.Parameters
> > >
> > > prm.Value = Eval(prm.Name)
> > >
> > > Next prm
> > >
> > >
> > >
> > > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> > >
> > >
> > >
> > > Dim frm As Form
> > >
> > > Dim ctl As Control
> > >
> > > Dim varItm As Variant
> > >
> > >
> > >
> > > Set frm = Forms!frmExcursions
> > >
> > > Set ctl = frm! lboBulkList
> > >
> > > For Each varItm In ctl.ItemsSelected
> > >
> > >
> > >
> > > rs.Edit
> > >
> > > rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.bum> wrote in message
> news:Am****************@fe1.texas.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(intIndex) = 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("qryBulkEdit")
> > >
> > >
> > >
> > > For Each prm In qdf.Parameters
> > >
> > > prm.Value = Eval(prm.Name)
> > >
> > > Next prm
> > >
> > >
> > >
> > > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> > >
> > >
> > >
> > > Dim frm As Form
> > >
> > > Dim ctl As Control
> > >
> > > Dim varItm As Variant
> > >
> > >
> > >
> > > Set frm = Forms!frmExcursions
> > >
> > > Set ctl = frm! lboBulkList
> > >
> > > For Each varItm In ctl.ItemsSelected
> > >
> > >
> > >
> > > rs.Edit
> > >
> > > rs(0) = Me. lboBulkList.ItemData(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_Click()
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!frmExcursions
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.OpenRecordset(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!ExcursionType = frm!Reason
rs!Details = frm!Comment
rs!Signatory = frm!TxtSignatory
rs!SignatoryArea = frm!TxtSignatoryArea
rs!ExcursionName = frm!ExName
rs!Cost = frm!ExCost
rs!Destination = frm!ExcursionDestination
rs!DeparturePlace = frm!DepartFrom
rs!ReturningPlace = frm!ReturnTo
rs!DepartureTime = frm!DepartTime
rs!ReturningTime = frm!ReturnTime
rs!Representative = frm!Rep
rs!Uniform = frm!Dress
rs!Overnight = frm!Night
rs!CommendationRequired = frm!Commendation
rs!Outdoors = frm!Outdoors
rs!Travel = frm!TravelType
rs!EntryDate = frm!txtEntryDate
rs!Edited = -1
End If
Next i
End Sub
Can you see what is wrong?
dixie
"Joe Black" <jo********@hotmail.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.ItemsSelected"
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.OpenRecordset(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.ozemail.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_Click()
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!frmExcursions
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs!Date = frm!txtDate
rs!ID_TCHR = frm!Teacher
rs!Faculty = frm!cboFaculty
rs!Course = frm!Course
rs!ExcursionType = frm!Reason
rs!Details = frm!Comment
rs!Signatory = frm!TxtSignatory
rs!SignatoryArea = frm!TxtSignatoryArea
rs!ExcursionName = frm!ExName
rs!Cost = frm!ExCost
rs!Destination = frm!ExcursionDestination
rs!DeparturePlace = frm!DepartFrom
rs!ReturningPlace = frm!ReturnTo
rs!DepartureTime = frm!DepartTime
rs!ReturningTime = frm!ReturnTime
rs!Representative = frm!Rep
rs!Uniform = frm!Dress
rs!Overnight = frm!Night
rs!CommendationRequired = frm!Commendation
rs!Outdoors = frm!Outdoors
rs!Travel = frm!TravelType
rs!EntryDate = frm!txtEntryDate
rs!Edited = -1
End If
Next varItm
End sub
"Pat" <no*****@ihatespam.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!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
rs.FieldNameToEdit = 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.ozemail.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*****@ihatespam.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(intIndex) = 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("qryBulkEdit")
> > >
> > >
> > >
> > > For Each prm In qdf.Parameters
> > >
> > > prm.Value = Eval(prm.Name)
> > >
> > > Next prm
> > >
> > >
> > >
> > > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> > >
> > >
> > >
> > > Dim frm As Form
> > >
> > > Dim ctl As Control
> > >
> > > Dim varItm As Variant
> > >
> > >
> > >
> > > Set frm = Forms!frmExcursions
> > >
> > > Set ctl = frm! lboBulkList
> > >
> > > For Each varItm In ctl.ItemsSelected
> > >
> > >
> > >
> > > rs.Edit
> > >
> > > rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.bum> wrote in message
> news:Am****************@fe1.texas.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(intIndex) = 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("qryBulkEdit")
> > >
> > >
> > >
> > > For Each prm In qdf.Parameters
> > >
> > > prm.Value = Eval(prm.Name)
> > >
> > > Next prm
> > >
> > >
> > >
> > > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> > >
> > >
> > >
> > > Dim frm As Form
> > >
> > > Dim ctl As Control
> > >
> > > Dim varItm As Variant
> > >
> > >
> > >
> > > Set frm = Forms!frmExcursions
> > >
> > > Set ctl = frm! lboBulkList
> > >
> > > For Each varItm In ctl.ItemsSelected
> > >
> > >
> > >
> > > rs.Edit
> > >
> > > rs(0) = Me. lboBulkList.ItemData(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
Further to that last message, I refined my strSQL into a query that picks up
only those students in the current excursion. I am now getting the error
message - Runtime error 3061 Too few parameters. Expected 1.
It is still halting on the same line which is the line
Set rs = CurrentDb.OpenRecordset(strSQL)
I can see the ID of the first student if I hover over the strSQL line.
dixie
"dixie" <di****@dogmail.com> wrote in message
news:Tc****************@nnrp1.ozemail.com.au... 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_Click()
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!frmExcursions
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.OpenRecordset(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!ExcursionType = frm!Reason
rs!Details = frm!Comment
rs!Signatory = frm!TxtSignatory
rs!SignatoryArea = frm!TxtSignatoryArea
rs!ExcursionName = frm!ExName
rs!Cost = frm!ExCost
rs!Destination = frm!ExcursionDestination
rs!DeparturePlace = frm!DepartFrom
rs!ReturningPlace = frm!ReturnTo
rs!DepartureTime = frm!DepartTime
rs!ReturningTime = frm!ReturnTime
rs!Representative = frm!Rep
rs!Uniform = frm!Dress
rs!Overnight = frm!Night
rs!CommendationRequired = frm!Commendation
rs!Outdoors = frm!Outdoors
rs!Travel = frm!TravelType
rs!EntryDate = frm!txtEntryDate
rs!Edited = -1
End If
Next i
End Sub
Can you see what is wrong?
dixie
"Joe Black" <jo********@hotmail.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.ItemsSelected"
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.OpenRecordset(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.ozemail.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_Click()
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!frmExcursions
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs!Date = frm!txtDate
rs!ID_TCHR = frm!Teacher
rs!Faculty = frm!cboFaculty
rs!Course = frm!Course
rs!ExcursionType = frm!Reason
rs!Details = frm!Comment
rs!Signatory = frm!TxtSignatory
rs!SignatoryArea = frm!TxtSignatoryArea
rs!ExcursionName = frm!ExName
rs!Cost = frm!ExCost
rs!Destination = frm!ExcursionDestination
rs!DeparturePlace = frm!DepartFrom
rs!ReturningPlace = frm!ReturnTo
rs!DepartureTime = frm!DepartTime
rs!ReturningTime = frm!ReturnTime
rs!Representative = frm!Rep
rs!Uniform = frm!Dress
rs!Overnight = frm!Night
rs!CommendationRequired = frm!Commendation
rs!Outdoors = frm!Outdoors
rs!Travel = frm!TravelType
rs!EntryDate = frm!txtEntryDate
rs!Edited = -1
End If
Next varItm
End sub
"Pat" <no*****@ihatespam.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!frmExcursions
> Set ctl = frm! lboBulkList
> For Each varItm In ctl.ItemsSelected
> strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
> Set rs = CurrentDb.OpenRecordset(strSql)
> If rs.RecordCount > 0 Then
> rs.FieldNameToEdit = 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.ozemail.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*****@ihatespam.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(intIndex) = 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("qryBulkEdit")
> > > >
> > > >
> > > >
> > > > For Each prm In qdf.Parameters
> > > >
> > > > prm.Value = Eval(prm.Name)
> > > >
> > > > Next prm
> > > >
> > > >
> > > >
> > > > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> > > >
> > > >
> > > >
> > > > Dim frm As Form
> > > >
> > > > Dim ctl As Control
> > > >
> > > > Dim varItm As Variant
> > > >
> > > >
> > > >
> > > > Set frm = Forms!frmExcursions
> > > >
> > > > Set ctl = frm! lboBulkList
> > > >
> > > > For Each varItm In ctl.ItemsSelected
> > > >
> > > >
> > > >
> > > > rs.Edit
> > > >
> > > > rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.bum> wrote in message
> > news:Am****************@fe1.texas.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(intIndex) = 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("qryBulkEdit")
> > > >
> > > >
> > > >
> > > > For Each prm In qdf.Parameters
> > > >
> > > > prm.Value = Eval(prm.Name)
> > > >
> > > > Next prm
> > > >
> > > >
> > > >
> > > > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> > > >
> > > >
> > > >
> > > > Dim frm As Form
> > > >
> > > > Dim ctl As Control
> > > >
> > > > Dim varItm As Variant
> > > >
> > > >
> > > >
> > > > Set frm = Forms!frmExcursions
> > > >
> > > > Set ctl = frm! lboBulkList
> > > >
> > > > For Each varItm In ctl.ItemsSelected
> > > >
> > > >
> > > >
> > > > rs.Edit
> > > >
> > > > rs(0) = Me. lboBulkList.ItemData(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" <di****@dogmail.com> wrote in
news:h5****************@nnrp1.ozemail.com.au: Joe
Further to that last message, I refined my strSQL into a query
that picks up only those students in the current excursion. I
am now getting the error message - Runtime error 3061 Too few
parameters. Expected 1.
It is still halting on the same line which is the line
Set rs = CurrentDb.OpenRecordset(strSQL)
Since your ID 8is a text type, your sql should include some extra
quotes strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" &
Me.lboBulkList.ItemData(0)
strSQL = "SELECT * FROM tblExcursions WHERE [ID] =""" &
Me.lboBulkList.ItemData(0) & """"
Second, after you perform your updates, you need to do rs.update
to commit the changes.You also should have a rs.edit before
making changes to the record.
e.g If rs.RecordCount > 0 Then
rs.edit rs!Date = frm!txtDate
rs!ID_TCHR = frm!Teacher
... rs!Edited = -1
rs.update End If
I can see the ID of the first student if I hover over the
strSQL line.
dixie
Can you see what is wrong?
dixie
Hi dixie
I didn't expect the ID field to be text.
Text needs to be enclosed in speech marks.
Try changing to:
WHERE [ID] = " & Chr(34) & Me.lboBulkList.ItemData(0) & Chr(34) & ";"
Regards - Joe
"dixie" <di****@dogmail.com> wrote in message
news:Tc****************@nnrp1.ozemail.com.au... 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_Click()
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!frmExcursions
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.OpenRecordset(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!ExcursionType = frm!Reason
rs!Details = frm!Comment
rs!Signatory = frm!TxtSignatory
rs!SignatoryArea = frm!TxtSignatoryArea
rs!ExcursionName = frm!ExName
rs!Cost = frm!ExCost
rs!Destination = frm!ExcursionDestination
rs!DeparturePlace = frm!DepartFrom
rs!ReturningPlace = frm!ReturnTo
rs!DepartureTime = frm!DepartTime
rs!ReturningTime = frm!ReturnTime
rs!Representative = frm!Rep
rs!Uniform = frm!Dress
rs!Overnight = frm!Night
rs!CommendationRequired = frm!Commendation
rs!Outdoors = frm!Outdoors
rs!Travel = frm!TravelType
rs!EntryDate = frm!txtEntryDate
rs!Edited = -1
End If
Next i
End Sub
Can you see what is wrong?
dixie
"Joe Black" <jo********@hotmail.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.ItemsSelected"
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.OpenRecordset(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.ozemail.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_Click()
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!frmExcursions
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs!Date = frm!txtDate
rs!ID_TCHR = frm!Teacher
rs!Faculty = frm!cboFaculty
rs!Course = frm!Course
rs!ExcursionType = frm!Reason
rs!Details = frm!Comment
rs!Signatory = frm!TxtSignatory
rs!SignatoryArea = frm!TxtSignatoryArea
rs!ExcursionName = frm!ExName
rs!Cost = frm!ExCost
rs!Destination = frm!ExcursionDestination
rs!DeparturePlace = frm!DepartFrom
rs!ReturningPlace = frm!ReturnTo
rs!DepartureTime = frm!DepartTime
rs!ReturningTime = frm!ReturnTime
rs!Representative = frm!Rep
rs!Uniform = frm!Dress
rs!Overnight = frm!Night
rs!CommendationRequired = frm!Commendation
rs!Outdoors = frm!Outdoors
rs!Travel = frm!TravelType
rs!EntryDate = frm!txtEntryDate
rs!Edited = -1
End If
Next varItm
End sub
"Pat" <no*****@ihatespam.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!frmExcursions
> Set ctl = frm! lboBulkList
> For Each varItm In ctl.ItemsSelected
> strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
> Set rs = CurrentDb.OpenRecordset(strSql)
> If rs.RecordCount > 0 Then
> rs.FieldNameToEdit = 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.ozemail.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*****@ihatespam.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(intIndex) = 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("qryBulkEdit")
> > > >
> > > >
> > > >
> > > > For Each prm In qdf.Parameters
> > > >
> > > > prm.Value = Eval(prm.Name)
> > > >
> > > > Next prm
> > > >
> > > >
> > > >
> > > > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> > > >
> > > >
> > > >
> > > > Dim frm As Form
> > > >
> > > > Dim ctl As Control
> > > >
> > > > Dim varItm As Variant
> > > >
> > > >
> > > >
> > > > Set frm = Forms!frmExcursions
> > > >
> > > > Set ctl = frm! lboBulkList
> > > >
> > > > For Each varItm In ctl.ItemsSelected
> > > >
> > > >
> > > >
> > > > rs.Edit
> > > >
> > > > rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.bum> wrote in message
> > news:Am****************@fe1.texas.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(intIndex) = 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("qryBulkEdit")
> > > >
> > > >
> > > >
> > > > For Each prm In qdf.Parameters
> > > >
> > > > prm.Value = Eval(prm.Name)
> > > >
> > > > Next prm
> > > >
> > > >
> > > >
> > > > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> > > >
> > > >
> > > >
> > > > Dim frm As Form
> > > >
> > > > Dim ctl As Control
> > > >
> > > > Dim varItm As Variant
> > > >
> > > >
> > > >
> > > > Set frm = Forms!frmExcursions
> > > >
> > > > Set ctl = frm! lboBulkList
> > > >
> > > > For Each varItm In ctl.ItemsSelected
> > > >
> > > >
> > > >
> > > > rs.Edit
> > > >
> > > > rs(0) = Me. lboBulkList.ItemData(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
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Thanks Bob, that was the problem I think, I have text ID's, because some of
them have an alphnumeric combination in them. I have put back the rs.edit
and rs.update that I originally had, but had been removed while I was trying
other ideas.
dixie
"Bob Quintal" <bq******@generation.net> wrote in message
news:a5******************************@news.teranew s.com... "dixie" <di****@dogmail.com> wrote in
news:h5****************@nnrp1.ozemail.com.au:
Joe
Further to that last message, I refined my strSQL into a query
that picks up only those students in the current excursion. I
am now getting the error message - Runtime error 3061 Too few
parameters. Expected 1.
It is still halting on the same line which is the line
Set rs = CurrentDb.OpenRecordset(strSQL)
Since your ID 8is a text type, your sql should include some extra
quotes strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" &
Me.lboBulkList.ItemData(0)
strSQL = "SELECT * FROM tblExcursions WHERE [ID] =""" &
Me.lboBulkList.ItemData(0) & """"
Second, after you perform your updates, you need to do rs.update
to commit the changes.You also should have a rs.edit before
making changes to the record.
e.g If rs.RecordCount > 0 Then rs.edit rs!Date = frm!txtDate
rs!ID_TCHR = frm!Teacher ... rs!Edited = -1 rs.update End If
I can see the ID of the first student if I hover over the
strSQL line.
dixie
Can you see what is wrong?
dixie
"Pat" <no*****@ihatespam.bum> wrote in
news:Ij*******************@fe2.texas.rr.com: Set frm = Forms!frmExcursions
Set ctl = frm! lboBulkList
For Each varItm In ctl.ItemsSelected
strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
Set rs = CurrentDb.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
rs.FieldNameToEdit = YourNewValue
End if
Next varItm
That's invalid syntax for referring to fields in recordset -- you
must use the !, not the dot.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
"dixie" <di****@dogmail.com> wrote in
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).
Declare your variables all in one place, at the top of the
subroutine, instead of defining them as needed. Doing the latter
makes it harder to read the code.
Dim intIndex As Integer
Dim db As Database
Dim rs As Recordset
Dim prm As Parameter
Dim qdf As QueryDef
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
For intIndex = 0 To Me.lboBulkList.ListCount
Me.lbo.BulkList.Selected(intIndex) = True
Next intIndex
I never use the . operator for controls (and I assume that's a typo
with the period in the middle of its name), so I'd do this instead:
Me!lboBulkList.Selected(intIndex) = True
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryBulkEdit")
Why not use a non-parameter query and skip all the QueryDef stuff
and simply open a recordset using SQL with an appropriate WHERE
clause?
The only real justification for using parameters is performance (or
maybe because the back end is not Jet).
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Are the parameters references to the controls on your form? If not,
I don't see how they are getting filled out here.
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Set frm = Forms!frmExcursions
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
The problem here is that you haven't navigated to the correct
record. What you want to do is:
rs.FindFirst "[ID]='" & ctl.ItemData(varItm) & "'"
But if it's a multi-column listbox, you may need to specify the
column (I always have to look this up when I'm using listboxes).
Then you need to see if a match was found:
If Not rs.NoMatch Then
[edit your fields]
End If
All the editing should be inside this If structure, because
otherwise, no navigation from the previous record will have taken
place. Of course, you probably want an error handler for this, too,
as it's a condition that oughtn't really occur.
rs.Edit
rs(0) = Me.lboBulkList.ItemData(varItm)
???
Does this refer to the first record, or the first field?
Secondly, why not use ctl.ItemData(varItm) instead of retyping the
control name? There's not much point in using a Control variable if
you're only going to use it once.
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
You could also do all of this with a single SQL update statement.
How?
By creating a WHERE clause that would be something like:
WHERE ID IN ([list of IDs constructed from your listbox])
You already know how to loop through your listbox's .ItemSelected
collection, so it would be something like this:
Dim strIDList As String
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
strIDList = strIDList & "', '" & ctl.ItemData(varItm)
Next varItm
strIDList = "'" & Mid(strIDList,4) & "'"
Then construct your SQL:
Dim strSQL As String
strSQL="UPDATE tblStudents As S SET S.Date=#" & frm!txtDate & "# "
strSQL=strSQL & "S.Faculty='" & frm!cmbFaculty & "' "
strSQL=strSQL & "S.Course='" & frm!Course & "' "
strSQL=strSQL & "S.Cost=" & frm!Cost
strSQL=strSQL & " WHERE S.ID IN (" & strIDList & ");"
CurrentDB.Execute(strSQL, dbFailOnError)
No need to open recordsets or querydefs, and it will be much faster,
as it will do a SQL update. This will also hold locks on the
table/records for a much shorter period of time.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
That was a great reply. This is why I read this newsgroup and participate
when I can.
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86... "dixie" <di****@dogmail.com> wrote in
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).
Declare your variables all in one place, at the top of the
subroutine, instead of defining them as needed. Doing the latter
makes it harder to read the code.
Dim intIndex As Integer
Dim db As Database
Dim rs As Recordset
Dim prm As Parameter
Dim qdf As QueryDef
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
For intIndex = 0 To Me.lboBulkList.ListCount
Me.lbo.BulkList.Selected(intIndex) = True
Next intIndex
I never use the . operator for controls (and I assume that's a typo
with the period in the middle of its name), so I'd do this instead:
Me!lboBulkList.Selected(intIndex) = True
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryBulkEdit")
Why not use a non-parameter query and skip all the QueryDef stuff
and simply open a recordset using SQL with an appropriate WHERE
clause?
The only real justification for using parameters is performance (or
maybe because the back end is not Jet).
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Are the parameters references to the controls on your form? If not,
I don't see how they are getting filled out here.
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Set frm = Forms!frmExcursions
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
The problem here is that you haven't navigated to the correct
record. What you want to do is:
rs.FindFirst "[ID]='" & ctl.ItemData(varItm) & "'"
But if it's a multi-column listbox, you may need to specify the
column (I always have to look this up when I'm using listboxes).
Then you need to see if a match was found:
If Not rs.NoMatch Then
[edit your fields]
End If
All the editing should be inside this If structure, because
otherwise, no navigation from the previous record will have taken
place. Of course, you probably want an error handler for this, too,
as it's a condition that oughtn't really occur.
rs.Edit
rs(0) = Me.lboBulkList.ItemData(varItm)
???
Does this refer to the first record, or the first field?
Secondly, why not use ctl.ItemData(varItm) instead of retyping the
control name? There's not much point in using a Control variable if
you're only going to use it once.
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
You could also do all of this with a single SQL update statement.
How?
By creating a WHERE clause that would be something like:
WHERE ID IN ([list of IDs constructed from your listbox])
You already know how to loop through your listbox's .ItemSelected
collection, so it would be something like this:
Dim strIDList As String
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
strIDList = strIDList & "', '" & ctl.ItemData(varItm)
Next varItm
strIDList = "'" & Mid(strIDList,4) & "'"
Then construct your SQL:
Dim strSQL As String
strSQL="UPDATE tblStudents As S SET S.Date=#" & frm!txtDate & "# "
strSQL=strSQL & "S.Faculty='" & frm!cmbFaculty & "' "
strSQL=strSQL & "S.Course='" & frm!Course & "' "
strSQL=strSQL & "S.Cost=" & frm!Cost
strSQL=strSQL & " WHERE S.ID IN (" & strIDList & ");"
CurrentDB.Execute(strSQL, dbFailOnError)
No need to open recordsets or querydefs, and it will be much faster,
as it will do a SQL update. This will also hold locks on the
table/records for a much shorter period of time.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
snip... Dim strSQL As String
strSQL="UPDATE tblStudents As S SET S.Date=#" & frm!txtDate & "# "
strSQL=strSQL & "S.Faculty='" & frm!cmbFaculty & "' "
strSQL=strSQL & "S.Course='" & frm!Course & "' "
strSQL=strSQL & "S.Cost=" & frm!Cost
strSQL=strSQL & " WHERE S.ID IN (" & strIDList & ");"
CurrentDB.Execute(strSQL, dbFailOnError)
Putting everything into a SQL string can sometimes cause problems
e.g what if Faculty or Course has an apostrophe in it.
rs!Faculty = frm!cboFaculty might be slower but its safer.
Also, I don't think you should use parentheses with the Execute method.
Regards - Joe
Joe Black wrote: snip...
Dim strSQL As String
strSQL="UPDATE tblStudents As S SET S.Date=#" & frm!txtDate & "# "
strSQL=strSQL & "S.Faculty='" & frm!cmbFaculty & "' "
strSQL=strSQL & "S.Course='" & frm!Course & "' "
strSQL=strSQL & "S.Cost=" & frm!Cost
strSQL=strSQL & " WHERE S.ID IN (" & strIDList & ");"
CurrentDB.Execute(strSQL, dbFailOnError)
Putting everything into a SQL string can sometimes cause problems
e.g what if Faculty or Course has an apostrophe in it.
rs!Faculty = frm!cboFaculty might be slower but its safer.
Also, I don't think you should use parentheses with the Execute method.
Regards - Joe
strSQL=strSQL & "S.Faculty='" & Replace(frm!cmbFaculty,"'","''") & "' "
--
Error reading sig - A)bort R)etry I)nfluence with large hammer
"Joe Black" <jo********@hotmail.com> wrote in
news:74******************@news.xtra.co.nz: snip...
Dim strSQL As String
strSQL="UPDATE tblStudents As S SET S.Date=#" & frm!txtDate &
"# " strSQL=strSQL & "S.Faculty='" & frm!cmbFaculty & "' "
strSQL=strSQL & "S.Course='" & frm!Course & "' "
strSQL=strSQL & "S.Cost=" & frm!Cost
strSQL=strSQL & " WHERE S.ID IN (" & strIDList & ");"
CurrentDB.Execute(strSQL, dbFailOnError)
Putting everything into a SQL string can sometimes cause problems
e.g what if Faculty or Course has an apostrophe in it.
rs!Faculty = frm!cboFaculty might be slower but its safer.
It's a good point, but I wasn't actually proposing a finished
solution! I was just explaining the method for doing it more
efficiently.
Editing the recordset is "safer" only if you don't bother to address
the apostrophes/quotes problem. One such method is in Trevor's reply
to your post.
Also, I don't think you should use parentheses with the Execute
method.
If I did:
Call CurrentDB.Execute(strSQL, dbFailOnError)
the () would be required.
But you're right, as I typed it, it was incorrect. Of course, the
VBA IDE would tell you that if you tried it.
One other thing in my code. I suggested this for looping to
construct the IN () list:
For Each varItm In ctl.ItemsSelected
strIDList = strIDList & "', '" & ctl.ItemData(varItm)
Next varItm
strIDList = "'" & Mid(strIDList,4) & "'"
That last line should be one of these two alternatives, not what I
originally typed:
strIDList = Mid(strIDList,4) & "'"
strIDList = "'" & Mid(strIDList,5) & "'"
What I typed would get you a list like this:
''ABC', 'DEF', 'GHI'
Also, the loop could have been changed:
For Each varItm In ctl.ItemsSelected
strIDList = strIDList & ", '" & ctl.ItemData(varItm) & "'"
Next varItm
strIDList = Mid(strIDList,3)
I might even be tempted to write the concatenation as this:
strIDList = strIDList & ", " & "'" & ctl.ItemData(varItm) & "'"
to make it clearer to myself that my Mid() should start at character
3.
I credit Trevor Best for the Mid() trick with string concatenation.
Previous to his suggesting it, I'd always done this:
If Len(strIDList)=0 Then
strIDList = "'" & ctl.ItemData(varItm) "'"
Else
strIDList = strIDList & ", '" & ctl.ItemData(varItm) & "'"
End If
He also suggested the use of the + operator for propagating Nulls
when concatenating. Take "LastName, FirstName", for instance. I used
to do this:
Dim strName As String
strName = Me!LastName
If Len(strName) = 0 Then
strName = Me!FirstName
ElseIf Not IsNull(Me!FirstName) Then
strName = strName & ", " & Me!FirstName
End If
And in queries:
LastName & IIf(Not IsNull(LastName),
IIf(Not IsNull(FirstName),", ")) & FirstName
(that's using the IIf() version that doesn't require both arguments,
BTW)
With Trevor's suggestion, I now do this:
strName = Mid(("12" + Me!LastName) & (", " + Me!FirstName),3)
And in queries:
Mid(("12" + LastName) & (", " + FirstName),3)
Since the + concatenation operator propagates Nulls (i.e., if either
side of the + is Null, the expression returns Null), if LastName is
Null, you're concatenating Null & ", FirstName", which would give
you ", FirstName." The Mid() lops off the leading ", ".
Now, this won't work when *both* are Null, but, well, that's
something you can test for, since records about people with neither
last nor first names are not terribly useful!
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
David W. Fenton wrote: I credit Trevor Best for the Mid() trick with string concatenation.
I wish my bank gave me as much credit :-)
Now, this won't work when *both* are Null, but, well, that's
something you can test for, since records about people with neither
last nor first names are not terribly useful!
Heh, you'd be surprised what people try to save in their forms (not you
personally David, you probably know all this already), e.g. professional
procurement people that try to send orders out with no supplier, po
number, date[1], or any frikkin items with prices on, etc.
[1] At least that's one thing I can do automatically for them, bless
their little cotton socks.
--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Thanks David, there is a lot of good stuff in here for me to digest. I may
have to get back into this thread to ask you some questions as I don't
pretend to fully follow it all.
I'll start with what is happening in the line strIDList = "'" & Mid(strIDList,4) & "'"
Secondly, I am not sure about the S in the SQL lines strSQL="UPDATE tblStudents As S SET S.Date=#" & frm!txtDate & "# "
strSQL=strSQL & "S.Faculty='" & frm!cmbFaculty & "' "
strSQL=strSQL & "S.Course='" & frm!Course & "' "
strSQL=strSQL & "S.Cost=" & frm!Cost
strSQL=strSQL & " WHERE S.ID IN (" & strIDList & ");"
CurrentDB.Execute(strSQL, dbFailOnError)
Where does it come from and what is its purpose?
dixie
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86... "dixie" <di****@dogmail.com> wrote in
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).
Declare your variables all in one place, at the top of the
subroutine, instead of defining them as needed. Doing the latter
makes it harder to read the code.
Dim intIndex As Integer
Dim db As Database
Dim rs As Recordset
Dim prm As Parameter
Dim qdf As QueryDef
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
For intIndex = 0 To Me.lboBulkList.ListCount
Me.lbo.BulkList.Selected(intIndex) = True
Next intIndex
I never use the . operator for controls (and I assume that's a typo
with the period in the middle of its name), so I'd do this instead:
Me!lboBulkList.Selected(intIndex) = True
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryBulkEdit")
Why not use a non-parameter query and skip all the QueryDef stuff
and simply open a recordset using SQL with an appropriate WHERE
clause?
The only real justification for using parameters is performance (or
maybe because the back end is not Jet).
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Are the parameters references to the controls on your form? If not,
I don't see how they are getting filled out here.
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Set frm = Forms!frmExcursions
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
The problem here is that you haven't navigated to the correct
record. What you want to do is:
rs.FindFirst "[ID]='" & ctl.ItemData(varItm) & "'"
But if it's a multi-column listbox, you may need to specify the
column (I always have to look this up when I'm using listboxes).
Then you need to see if a match was found:
If Not rs.NoMatch Then
[edit your fields]
End If
All the editing should be inside this If structure, because
otherwise, no navigation from the previous record will have taken
place. Of course, you probably want an error handler for this, too,
as it's a condition that oughtn't really occur.
rs.Edit
rs(0) = Me.lboBulkList.ItemData(varItm)
???
Does this refer to the first record, or the first field?
Secondly, why not use ctl.ItemData(varItm) instead of retyping the
control name? There's not much point in using a Control variable if
you're only going to use it once.
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
You could also do all of this with a single SQL update statement.
How?
By creating a WHERE clause that would be something like:
WHERE ID IN ([list of IDs constructed from your listbox])
You already know how to loop through your listbox's .ItemSelected
collection, so it would be something like this:
Dim strIDList As String
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelected
strIDList = strIDList & "', '" & ctl.ItemData(varItm)
Next varItm
strIDList = "'" & Mid(strIDList,4) & "'"
Then construct your SQL:
Dim strSQL As String
strSQL="UPDATE tblStudents As S SET S.Date=#" & frm!txtDate & "# "
strSQL=strSQL & "S.Faculty='" & frm!cmbFaculty & "' "
strSQL=strSQL & "S.Course='" & frm!Course & "' "
strSQL=strSQL & "S.Cost=" & frm!Cost
strSQL=strSQL & " WHERE S.ID IN (" & strIDList & ");"
CurrentDB.Execute(strSQL, dbFailOnError)
No need to open recordsets or querydefs, and it will be much faster,
as it will do a SQL update. This will also hold locks on the
table/records for a much shorter period of time.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Joe, what does the & ";" do at the end of your WHERE condition?
dixie
"Joe Black" <jo********@hotmail.com> wrote in message
news:3H*****************@news.xtra.co.nz... Hi dixie
I didn't expect the ID field to be text.
Text needs to be enclosed in speech marks.
Try changing to:
WHERE [ID] = " & Chr(34) & Me.lboBulkList.ItemData(0) & Chr(34) & ";"
Regards - Joe
"dixie" <di****@dogmail.com> wrote in message
news:Tc****************@nnrp1.ozemail.com.au... 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_Click()
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!frmExcursions
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.OpenRecordset(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!ExcursionType = frm!Reason
rs!Details = frm!Comment
rs!Signatory = frm!TxtSignatory
rs!SignatoryArea = frm!TxtSignatoryArea
rs!ExcursionName = frm!ExName
rs!Cost = frm!ExCost
rs!Destination = frm!ExcursionDestination
rs!DeparturePlace = frm!DepartFrom
rs!ReturningPlace = frm!ReturnTo
rs!DepartureTime = frm!DepartTime
rs!ReturningTime = frm!ReturnTime
rs!Representative = frm!Rep
rs!Uniform = frm!Dress
rs!Overnight = frm!Night
rs!CommendationRequired = frm!Commendation
rs!Outdoors = frm!Outdoors
rs!Travel = frm!TravelType
rs!EntryDate = frm!txtEntryDate
rs!Edited = -1
End If
Next i
End Sub
Can you see what is wrong?
dixie
"Joe Black" <jo********@hotmail.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.ItemsSelected"
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.OpenRecordset(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.ozemail.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_Click()
> 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!frmExcursions
> Set ctl = frm!lboBulkList
>
> For Each varItm In ctl.ItemsSelected
> strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
>
> Set rs = CurrentDb.OpenRecordset(strSQL)
> If rs.RecordCount > 0 Then
> rs!Date = frm!txtDate
> rs!ID_TCHR = frm!Teacher
> rs!Faculty = frm!cboFaculty
> rs!Course = frm!Course
> rs!ExcursionType = frm!Reason
> rs!Details = frm!Comment
> rs!Signatory = frm!TxtSignatory
> rs!SignatoryArea = frm!TxtSignatoryArea
> rs!ExcursionName = frm!ExName
> rs!Cost = frm!ExCost
> rs!Destination = frm!ExcursionDestination
> rs!DeparturePlace = frm!DepartFrom
> rs!ReturningPlace = frm!ReturnTo
> rs!DepartureTime = frm!DepartTime
> rs!ReturningTime = frm!ReturnTime
> rs!Representative = frm!Rep
> rs!Uniform = frm!Dress
> rs!Overnight = frm!Night
> rs!CommendationRequired = frm!Commendation
> rs!Outdoors = frm!Outdoors
> rs!Travel = frm!TravelType
> rs!EntryDate = frm!txtEntryDate
> rs!Edited = -1
> End If
> Next varItm
> End sub
>
> "Pat" <no*****@ihatespam.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!frmExcursions
> > Set ctl = frm! lboBulkList
> > For Each varItm In ctl.ItemsSelected
> > strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
> > Set rs = CurrentDb.OpenRecordset(strSql)
> > If rs.RecordCount > 0 Then
> > rs.FieldNameToEdit = 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.ozemail.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*****@ihatespam.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(intIndex) = 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("qryBulkEdit")
> > > > >
> > > > >
> > > > >
> > > > > For Each prm In qdf.Parameters
> > > > >
> > > > > prm.Value = Eval(prm.Name)
> > > > >
> > > > > Next prm
> > > > >
> > > > >
> > > > >
> > > > > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> > > > >
> > > > >
> > > > >
> > > > > Dim frm As Form
> > > > >
> > > > > Dim ctl As Control
> > > > >
> > > > > Dim varItm As Variant
> > > > >
> > > > >
> > > > >
> > > > > Set frm = Forms!frmExcursions
> > > > >
> > > > > Set ctl = frm! lboBulkList
> > > > >
> > > > > For Each varItm In ctl.ItemsSelected
> > > > >
> > > > >
> > > > >
> > > > > rs.Edit
> > > > >
> > > > > rs(0) = Me. lboBulkList.ItemData(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*****@ihatespam.bum> wrote in message
> > > news:Am****************@fe1.texas.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(intIndex) = 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("qryBulkEdit")
> > > > >
> > > > >
> > > > >
> > > > > For Each prm In qdf.Parameters
> > > > >
> > > > > prm.Value = Eval(prm.Name)
> > > > >
> > > > > Next prm
> > > > >
> > > > >
> > > > >
> > > > > Set rs = qdf.OpenRecordset(dbOpenDynaset)
> > > > >
> > > > >
> > > > >
> > > > > Dim frm As Form
> > > > >
> > > > > Dim ctl As Control
> > > > >
> > > > > Dim varItm As Variant
> > > > >
> > > > >
> > > > >
> > > > > Set frm = Forms!frmExcursions
> > > > >
> > > > > Set ctl = frm! lboBulkList
> > > > >
> > > > > For Each varItm In ctl.ItemsSelected
> > > > >
> > > > >
> > > > >
> > > > > rs.Edit
> > > > >
> > > > > rs(0) = Me. lboBulkList.ItemData(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 wrote: Joe, what does the & ";" do at the end of your WHERE condition?
It's a standard end of command for SQL but optional, so is snipping
posts apparently :-(
--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Sorry! It was getting rather large - I'll keep an eye out in future.
dixie
"Trevor Best" <nospam@localhost> wrote in message
news:40***********************@auth.uk.news.easyne t.net... It's a standard end of command for SQL but optional, so is snipping
posts apparently :-(
"dixie" <di****@dogmail.com> wrote in
news:fA****************@nnrp1.ozemail.com.au: Thanks David, there is a lot of good stuff in here for me to
digest. I may have to get back into this thread to ask you some
questions as I don't pretend to fully follow it all.
I'll start with what is happening in the line strIDList = "'" & Mid(strIDList,4) & "'"
Walk the loop and check the value of strIDList after this line:
strIDList = strIDList & "', '" & ctl.ItemData(varItm)
Assume there are three IDs.
iteration item strIDList
1 "AB" "', 'AB"
2 "CD" "', 'AB, 'CD"
3 "EF" "', 'AB, 'CD', EF"
So, before you get to the line you're questioning, the value in
strIDList is:
"', 'AB, 'CD', EF"
Now, I posted elsewhere that this was actually erroneous. What I
typed:
strIDList = "'" & Mid(strIDList,4) & "'"
will start at character 4 of strIDList, and return that:
1234567890123456
"', 'AB, 'CD', EF"
That would return"
"'AB, 'CD', EF"
Then my erroneous code would add an opening "'" and closing "'" to
give you:
"''AB, 'CD', EF'"
which is, of course, wrong!
To correct it, you could change that line to:
strIDList = "'" & Mid(strIDList,5) & "'"
Or, better:
strIDList = Mid(strIDList,4) & "'"
And better still, change the line in the loop from :
strIDList = strIDList & "', '" & ctl.ItemData(varItm)
to:
strIDList = strIDList & ", '" & ctl.ItemData(varItm) & "'"
or, to make that clearer:
strIDList = strIDList & ", " & '" & ctl.ItemData(varItm) & "'"
and then change the Mid() line to:
strIDList = Mid(strIDList,3)
It's an old trick, as I explained, to avoid having to check the
length of the string variable you're concatenating into to see
whether or not you need to add the ", " to separate items.
Secondly, I am not sure about the S in the SQL lines strSQL="UPDATE tblStudents As S SET S.Date=#" & frm!txtDate &
"# " strSQL=strSQL & "S.Faculty='" & frm!cmbFaculty & "' "
strSQL=strSQL & "S.Course='" & frm!Course & "' "
strSQL=strSQL & "S.Cost=" & frm!Cost
strSQL=strSQL & " WHERE S.ID IN (" & strIDList & ");"
CurrentDB.Execute(strSQL, dbFailOnError)
Where does it come from and what is its purpose?
It's an alias for the table name (see "UPDATE tblStudents As S") to
make the SQL easier to read.
strSQL="UPDATE tblStudents SET S.Date=#" & frm!txtDate & "# "
strSQL=strSQL & "tblStudents.Faculty='" & frm!cmbFaculty & "' "
strSQL=strSQL & "tblStudents.Course='" & frm!Course & "' "
strSQL=strSQL & "tblStudents.Cost=" & frm!Cost
strSQL=strSQL & " WHERE tblStudents.ID IN (" & strIDList & ");"
is the same thing, just takes longer to type.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc 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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
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...
| |
