rs.Edit instead of rs.AddNew

[dixie]

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

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

Dim intIndex As Integer

For intIndex = 0 To Me.lboBulkList. ListCount

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

Next intIndex

Dim db As Database

Dim rs As Recordset

Dim prm As Parameter

Dim qdf As QueryDef

Set db = CurrentDb()

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

For Each prm In qdf.Parameters

prm.Value = Eval(prm.Name)

Next prm

Set rs = qdf.OpenRecords et(dbOpenDynase t)

Dim frm As Form

Dim ctl As Control

Dim varItm As Variant

Set frm = Forms!frmExcurs ions

Set ctl = frm! lboBulkList

For Each varItm In ctl.ItemsSelect ed

rs.Edit

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

rs!Date = frm!txtDate

rs!Faculty = frm!cboFaculty

rs!Course = frm!Course

rs!Cost = frm!ExCost

rs.Update

Next varItm

rs.Close: Set rs = Nothing
dixie

[Trevor Best]

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

[dixie]

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.Execu te(strSQL, dbFailOnError) Where does it come from and what is its purpose?

dixie

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:Xn******** *************** ***********@24. 168.128.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(intIn dex) = 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(intInd ex) = True

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

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.OpenRecords et(dbOpenDynase t)
Set frm = Forms!frmExcurs ions
Set ctl = frm!lboBulkList
For Each varItm In ctl.ItemsSelect ed

The problem here is that you haven't navigated to the correct
record. What you want to do is:

rs.FindFirst "[ID]='" & ctl.ItemData(va rItm) & "'"

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(va rItm) 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.ItemsSelect ed
strIDList = strIDList & "', '" & ctl.ItemData(va rItm)
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.Execu te(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

[dixie]

Joe, what does the & ";" do at the end of your WHERE condition?

dixie

"Joe Black" <jo********@hot mail.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_Cl ick()

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

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

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

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

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

Set rs = CurrentDb.OpenR ecordset(strSQL ) <-- It is halting with

this

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

Can you see what is wrong?

dixie
"Joe Black" <jo********@hot mail.com> wrote in message
news:5y******** *********@news. xtra.co.nz...

Hi Dixie

In your first version of the code you first select all items in the

listbox

and then iterate through all the selected items.
If you want to process all the list items, it is not necessary to select them all first.

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

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

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

You haven't allowed for the possibility that rs.RecordCount might be
1.
I also wonder whether the design of your database could be improved. If the

details of an Excursion are needed to be changed, you should only have to change the details in one row of a table, not repeatedly change the

same details for many rows.

Regards - Joe

"dixie" <di****@dogmail .com> wrote in message
news:hl******** *******@nnrp1.o zemail.com.au.. .
> Ok, tried that and strangely, I get exactly the same problem - that is,

it

> updates the first record, but not the others. I did not understand

your > statement about you will need a loop to update each record in the set -

is

> this the bit I have missed.
>
> The general idea is that I have a list of details for an excursion

that

a

> group of students are going on. I print a form out for each one and I print
> a list of students attending. Now, I already have that bit working.

The

> bit I am trying to get here is if there was a mistake in the details

or more
> information had come to hand, I wan't to be able to do a "bulk edit" on each
> entry (one per student). The ID is a unique student ID and there would

be

a
> number of them (up to 100) in the list box, lboBulkList.
>
> Now, this is the fine detail and is currently what I interpreted your > previous post into.
>
> Private Sub btnEditTest_Cli ck()
> Dim db As Database
> Dim rs As Recordset
> Dim strSQL As String
>
> Dim frm As Form
> Dim ctl As Control
> Dim varItm As Variant
>
> Set frm = Forms!frmExcurs ions
> Set ctl = frm!lboBulkList
>
> For Each varItm In ctl.ItemsSelect ed
> strSQL = "SELECT * FROM tblExcursions WHERE [ID] =" & varItm
>
> Set rs = CurrentDb.OpenR ecordset(strSQL )
> If rs.RecordCount > 0 Then
> rs!Date = frm!txtDate
> rs!ID_TCHR = frm!Teacher
> rs!Faculty = frm!cboFaculty
> rs!Course = frm!Course
> rs!ExcursionTyp e = frm!Reason
> rs!Details = frm!Comment
> rs!Signatory = frm!TxtSignator y
> rs!SignatoryAre a = frm!TxtSignator yArea
> rs!ExcursionNam e = frm!ExName
> rs!Cost = frm!ExCost
> rs!Destination = frm!ExcursionDe stination
> rs!DeparturePla ce = frm!DepartFrom
> rs!ReturningPla ce = frm!ReturnTo
> rs!DepartureTim e = frm!DepartTime
> rs!ReturningTim e = frm!ReturnTime
> rs!Representati ve = frm!Rep
> rs!Uniform = frm!Dress
> rs!Overnight = frm!Night
> rs!Commendation Required = frm!Commendatio n
> rs!Outdoors = frm!Outdoors
> rs!Travel = frm!TravelType
> rs!EntryDate = frm!txtEntryDat e
> rs!Edited = -1
> End If
> Next varItm
> End sub
>
> "Pat" <no*****@ihates pam.bum> wrote in message
> news:Ij******** ***********@fe2 .texas.rr.com.. .
> > Dixie,
> > Your original post and your latest reply contains a loop that looks to > have
> > originally been designed to loop through all records in a

recordset and

> > update fields. From your description, you want to update only records > > listed in a listbox, for which you have their ID.
> >
> > If your listbox will likely contain a few items, it could create a
dynamic
> > SLQ statment that selects only the record you want to edit. Then you can
> > edit that record.
> >
> > Set frm = Forms!frmExcurs ions
> > Set ctl = frm! lboBulkList
> > For Each varItm In ctl.ItemsSelect ed
> > strSQL = "SELECT * FROM tblData WHERE [IDField] =" & varItm
> > Set rs = CurrentDb.OpenR ecordset(strSql )
> > If rs.RecordCount > 0 Then
> > rs.FieldNameToE dit = YourNewValue
> > End if
> > Next varItm
> >
> > If you have more than one record that will return in the

recordset, you

> will
> > need a loop to handle update each record in the set.
> >
> > Hope this helps,
> > Pat
> >
> >
> >
> > "dixie" <di****@dogmail .com> wrote in message
> > news:gX******** *******@nnrp1.o zemail.com.au.. .
> > > OK Pat, I thought as much. You have confirmed what I believed

was > > > happening. My problem is that I don't know how to combine the
previous
> > type
> > > of code that updates according to an ID in a list box and then

this type
> > of
> > > code
> > >
> > > With rs
> > >
> > > If rs.RecordCount > 0 Then
> > > .MoveFirst
> > > Do
> > > .Edit
> > > rs!Cost = 0
> > > .Update
> > > .MoveNext
> > > Loop Until .EOF
> > > End If
> > > .Close: Set rs = Nothing
> > >
> > > End With
> > >
> > > I know that I need to step through the records in the recordset and edit
> > > each field according to my ID in the list box, but I can't seem to > combine
> > > the two ideas to get to the solution I want - which is for each

person

> > > selected to have their specific record edited according to a what is

in
> > > various controls on the form.
> > >
> > > dixie
> > >
> > > ----- Original Message -----
> > > From: "Pat" <no*****@ihates pam.bum>
> > > Newsgroups: comp.databases. ms-access
> > > Sent: Thursday, April 22, 2004 11:38 AM
> > > Subject: Re: rs.Edit instead of rs.AddNew
> > >
> > >
> > > > Dixie,
> > > > You are looping through your selected items, but you are not

looping

> > > through
> > > > your recordset (although looping through the recordset is only

going

> to
> > > work
> > > > if the records are in the _exact_ same order as your listbox).

rs(0)

> is
> > > the
> > > > first record in the returned recordset. As you loop through

the > items,
> > > you
> > > > are editing the same record.
> > > >
> > > > Instead, you want to select each record that cooresponds to each > > selected
> > > > listbox item and then update, or, pull all your records, find

the one
> > that
> > > > matches the listbox item in the loop and then update.
> > > >
> > > > Hope that gets you started.
> > > > Pat
> > > >
> > > >
> > > >
> > > > "dixie" <di****@dogmail .com> wrote in message
> > > > news:VZ******** *********@nnrp1 .ozemail.com.au ...
> > > > > I have some code that adds new records into a table for each ID

in

a
> > > list
> > > > > box when a button on a form is clicked. This works fine. My > problem
> > > now
> > > > is
> > > > > that I wish to be able to edit all the records for people

whose

ID

> is
> > in
> > > > the
> > > > > list box. I made minor changes to the code (mainly replacing > > rs.AddNew
> > > > with
> > > > > rs.Edit)and it appears to be updating only the first record

and then
> > > > > overwriting that record with the next, etc until it runs out of ID's
> > in
> > > > the
> > > > > list box. In other words, it is stepping through the ID's in the

> list
> > > > box,
> > > > > but not the records. Is there a trick to this? I have

spent

many

> > hours
> > > > > doing minor changes and still have the same problem.
> > > > >
> > > > > The code follows (I have reduced the number of fields I am
updating
> to
> > > > keep
> > > > > the size of the message down).
> > > > >
> > > > > Dim intIndex As Integer
> > > > >
> > > > > For intIndex = 0 To Me.lboBulkList. ListCount
> > > > >
> > > > > Me.lbo.BulkList .Selected(intIn dex) = True
> > > > >
> > > > > Next intIndex
> > > > >
> > > > >
> > > > >
> > > > > Dim db As Database
> > > > >
> > > > > Dim rs As Recordset
> > > > >
> > > > > Dim prm As Parameter
> > > > >
> > > > > Dim qdf As QueryDef
> > > > >
> > > > >
> > > > >
> > > > > Set db = CurrentDb()
> > > > >
> > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
> > > > >
> > > > >
> > > > >
> > > > > For Each prm In qdf.Parameters
> > > > >
> > > > > prm.Value = Eval(prm.Name)
> > > > >
> > > > > Next prm
> > > > >
> > > > >
> > > > >
> > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
> > > > >
> > > > >
> > > > >
> > > > > Dim frm As Form
> > > > >
> > > > > Dim ctl As Control
> > > > >
> > > > > Dim varItm As Variant
> > > > >
> > > > >
> > > > >
> > > > > Set frm = Forms!frmExcurs ions
> > > > >
> > > > > Set ctl = frm! lboBulkList
> > > > >
> > > > > For Each varItm In ctl.ItemsSelect ed
> > > > >
> > > > >
> > > > >
> > > > > rs.Edit
> > > > >
> > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
> > > > >
> > > > > rs!Date = frm!txtDate
> > > > >
> > > > > rs!Faculty = frm!cboFaculty
> > > > >
> > > > > rs!Course = frm!Course
> > > > >
> > > > > rs!Cost = frm!ExCost
> > > > >
> > > > > rs.Update
> > > > >
> > > > > Next varItm
> > > > >
> > > > > rs.Close: Set rs = Nothing
> > > > >
> > > > >
> > > > > dixie
> > > > >
> > > > >
> > > >
> > > >
> > >
> > > "Pat" <no*****@ihates pam.bum> wrote in message
> > > news:Am******** ********@fe1.te xas.rr.com...
> > > > Dixie,
> > > > You are looping through your selected items, but you are not

looping

> > > through
> > > > your recordset (although looping through the recordset is only

going

> to
> > > work
> > > > if the records are in the _exact_ same order as your listbox).

rs(0)

> is
> > > the
> > > > first record in the returned recordset. As you loop through

the > items,
> > > you
> > > > are editing the same record.
> > > >
> > > > Instead, you want to select each record that cooresponds to each > > selected
> > > > listbox item and then update, or, pull all your records, find

the one
> > that
> > > > matches the listbox item in the loop and then update.
> > > >
> > > > Hope that gets you started.
> > > > Pat
> > > >
> > > >
> > > >
> > > > "dixie" <di****@dogmail .com> wrote in message
> > > > news:VZ******** *********@nnrp1 .ozemail.com.au ...
> > > > > I have some code that adds new records into a table for each ID

in

a
> > > list
> > > > > box when a button on a form is clicked. This works fine. My > problem
> > > now
> > > > is
> > > > > that I wish to be able to edit all the records for people

whose

ID

> is
> > in
> > > > the
> > > > > list box. I made minor changes to the code (mainly replacing > > rs.AddNew
> > > > with
> > > > > rs.Edit)and it appears to be updating only the first record

and then
> > > > > overwriting that record with the next, etc until it runs out of ID's
> > in
> > > > the
> > > > > list box. In other words, it is stepping through the ID's

in the

> list
> > > > box,
> > > > > but not the records. Is there a trick to this? I have

spent many

> > hours
> > > > > doing minor changes and still have the same problem.
> > > > >
> > > > > The code follows (I have reduced the number of fields I am
updating
> to
> > > > keep
> > > > > the size of the message down).
> > > > >
> > > > > Dim intIndex As Integer
> > > > >
> > > > > For intIndex = 0 To Me.lboBulkList. ListCount
> > > > >
> > > > > Me.lbo.BulkList .Selected(intIn dex) = True
> > > > >
> > > > > Next intIndex
> > > > >
> > > > >
> > > > >
> > > > > Dim db As Database
> > > > >
> > > > > Dim rs As Recordset
> > > > >
> > > > > Dim prm As Parameter
> > > > >
> > > > > Dim qdf As QueryDef
> > > > >
> > > > >
> > > > >
> > > > > Set db = CurrentDb()
> > > > >
> > > > > Set qdf = db.QueryDefs("q ryBulkEdit")
> > > > >
> > > > >
> > > > >
> > > > > For Each prm In qdf.Parameters
> > > > >
> > > > > prm.Value = Eval(prm.Name)
> > > > >
> > > > > Next prm
> > > > >
> > > > >
> > > > >
> > > > > Set rs = qdf.OpenRecords et(dbOpenDynase t)
> > > > >
> > > > >
> > > > >
> > > > > Dim frm As Form
> > > > >
> > > > > Dim ctl As Control
> > > > >
> > > > > Dim varItm As Variant
> > > > >
> > > > >
> > > > >
> > > > > Set frm = Forms!frmExcurs ions
> > > > >
> > > > > Set ctl = frm! lboBulkList
> > > > >
> > > > > For Each varItm In ctl.ItemsSelect ed
> > > > >
> > > > >
> > > > >
> > > > > rs.Edit
> > > > >
> > > > > rs(0) = Me. lboBulkList.Ite mData(varItm)
> > > > >
> > > > > rs!Date = frm!txtDate
> > > > >
> > > > > rs!Faculty = frm!cboFaculty
> > > > >
> > > > > rs!Course = frm!Course
> > > > >
> > > > > rs!Cost = frm!ExCost
> > > > >
> > > > > rs.Update
> > > > >
> > > > > Next varItm
> > > > >
> > > > > rs.Close: Set rs = Nothing
> > > > >
> > > > >
> > > > > dixie
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[Trevor Best]

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

[dixie]

Sorry! It was getting rather large - I'll keep an eye out in future.

dixie

"Trevor Best" <nospam@localho st> wrote in message
news:40******** *************** @auth.uk.news.e asynet.net...

It's a standard end of command for SQL but optional, so is snipping
posts apparently :-(

[David W. Fenton]

"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(va rItm)

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:

123456789012345 6
"', '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(va rItm)

to:

strIDList = strIDList & ", '" & ctl.ItemData(va rItm) & "'"

or, to make that clearer:

strIDList = strIDList & ", " & '" & ctl.ItemData(va rItm) & "'"

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.Execu te(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.Fa culty='" & frm!cmbFaculty & "' "
strSQL=strSQL & "tblStudents.Co urse='" & frm!Course & "' "
strSQL=strSQL & "tblStudents.Co st=" & 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