needing help with loop issues

[John]

Hello. I have a form that has a listbox on it. Also on the form are
3 textboxes as well as an update button. The code below is on the
Update button click event. What is needed is the listbox will list
out records from a table (BuiltList). The listbox has a property of
extended. The user is able to select (highlite) the records needed
from the listbox. The user puts values in the textboxes that they
want to update in the selected records. There are only 3 fields per
each selected record the user will update. The fields in the table to
update are;
StandardWarrantyLength
MaxWarrantyLength
Reason
The txtboxes match the field name but with txt in front. As shown in
the code.

When I do a breakpoint in the code below, the 3 rst! lines do show the
txt value. But, this errors out and it always goes back to
the .Update line.

Dim DB As DAO.Database
Dim rst As DAO.Recordset

Set DB = CurrentDb
Set rst = DB.OpenRecordset("BuiltList")

For Each itm In
Me.lstMultipleDeviceWarrantyUpdateList.ItemsSelected
rst.AddNew
rst!StandardWarrantyLength = Me.txtContractWarrantyLength
rst!MaxWarrantyLength = Me.txtMaxWarrantyLength
rst!Reason = Me.txtReason
rst.Update

Next

rst.Close
Set rst = Nothing
Set DB = Nothing

I am not that proficient in vba for access and I am struggling with
this and ask for help in solving this matter.

Thanks.
John

 

[John]

Hello.  I have a form that has a listbox on it.  Also on the form are
3 textboxes as well as an update button.  The code below is on the
Update button click event.  What is needed is the listbox will list
out records from a table (BuiltList).  The listbox has a property of
extended.  The user is able to select (highlite) the records needed
from the listbox.  The user puts values in the textboxes that they
want to update in the selected records.  There are only 3 fields per
each selected record the user will update.  The fields in the table to
update are;
StandardWarrantyLength
MaxWarrantyLength
Reason
The txtboxes match the field name but with txt in front.  As shown in
the code.

When I do a breakpoint in the code below, the 3 rst! lines do show the
txt value.  But, this errors out and it always goes back to
the .Update line.

Dim DB As DAO.Database
Dim rst As DAO.Recordset

Set DB = CurrentDb
Set rst = DB.OpenRecordset("BuiltList")

    For Each itm In
Me.lstMultipleDeviceWarrantyUpdateList.ItemsSelected
        rst.AddNew
        rst!StandardWarrantyLength = Me.txtContractWarrantyLength
        rst!MaxWarrantyLength = Me.txtMaxWarrantyLength
        rst!Reason = Me.txtReason
        rst.Update

    Next

rst.Close
Set rst = Nothing
Set DB = Nothing

I am not that proficient in vba for access and I am struggling with
this and ask for help in solving this matter.

Thanks.
John

You can disregard this post. I went back to the original method I had
and finally got it to work. Posting here should anyone wish to try
it.

Dim intCount As Variant

For Each intCount In
Me.lstMultipleDeviceWarrantyUpdateList.ItemsSelected
If
Me.lstMultipleDeviceWarrantyUpdateList.Selected(intCount) = True Then
sql = "UPDATE BuiltList " & _
"SET StandardWarrantyLength = " &
txtContractWarrantyLength & ", " & _
"MaxWarrantyLength = " &
txtMaxWarrantyLength & ", " & _
"Reason = '" & txtReason & "' " & _
"WHERE PartNumber = '" & Forms!
xMultipleDeviceWarrantyInformationUpdate!
lstMultipleDeviceWarrantyUpdateList.column(0, intCount) & "' AND
LotNumber = '" & Forms!xMultipleDeviceWarrantyInformationUpdate!
lstMultipleDeviceWarrantyUpdateList.column(1, intCount) & "' "

CurrentDb.Execute sql, dbFailOnError

End If

Next intCount '[Forms]![CustomerSerialNumbers]![cboFindPart]