Re: rs.Edit instead of rs.AddNew
"dixie" <dixiec@dogmail .com> wrote in
news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au :
[color=blue]
> I have some code that adds new records into a table for each ID in
> a list box when a button on a form is clicked. This works fine.
> My problem now 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).[/color]
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.
[color=blue]
> 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[/color]
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
[color=blue]
> Set db = CurrentDb()
> Set qdf = db.QueryDefs("q ryBulkEdit")[/color]
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).
[color=blue]
> For Each prm In qdf.Parameters
> prm.Value = Eval(prm.Name)
> Next prm[/color]
Are the parameters references to the controls on your form? If not,
I don't see how they are getting filled out here.
[color=blue]
> Set rs = qdf.OpenRecords et(dbOpenDynase t)
> Set frm = Forms!frmExcurs ions
> Set ctl = frm!lboBulkList
> For Each varItm In ctl.ItemsSelect ed[/color]
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.
[color=blue]
> rs.Edit
> rs(0) = Me.lboBulkList. ItemData(varItm )[/color]
???
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.
[color=blue]
> 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[/color]
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" <dixiec@dogmail .com> wrote in
news:VZChc.193$ Rk4.10033@nnrp1 .ozemail.com.au :
[color=blue]
> I have some code that adds new records into a table for each ID in
> a list box when a button on a form is clicked. This works fine.
> My problem now 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).[/color]
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.
[color=blue]
> 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[/color]
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
[color=blue]
> Set db = CurrentDb()
> Set qdf = db.QueryDefs("q ryBulkEdit")[/color]
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).
[color=blue]
> For Each prm In qdf.Parameters
> prm.Value = Eval(prm.Name)
> Next prm[/color]
Are the parameters references to the controls on your form? If not,
I don't see how they are getting filled out here.
[color=blue]
> Set rs = qdf.OpenRecords et(dbOpenDynase t)
> Set frm = Forms!frmExcurs ions
> Set ctl = frm!lboBulkList
> For Each varItm In ctl.ItemsSelect ed[/color]
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.
[color=blue]
> rs.Edit
> rs(0) = Me.lboBulkList. ItemData(varItm )[/color]
???
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.
[color=blue]
> 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[/color]
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
Comment