Setting value to multiple records included in a report

[crisostomofred]

I have a list box where I can select more than 1 record and a Email command button that opens up a report with just those records selected from the list box. After sending this report using the SendObject command, I want to programmatically set the value of a field called "Notified" to "Yes" or "-1" on those records included in the report. (By setting this value on those records, they will be excluded from the list box). I have no problem setting the value if I use a continuous form to select the records to email. I prefer using a list box with multiple selection.

Any help will be appreciated.

Fred

[boblarson]

You can create an update query to update that field for the criteria of those records with the selection of the list box and then call it:

Dim strRecordsToUpdate As String
Dim varX As Variant

strRecordsToUpdate = "In ("

For Each varX In Me.YourListBox.ItemsSelected
   strRecordsToUpdate = strRecordsToUpdate & Me.YourListBox.ItemData(varX).Value & ", "
Next varX

strRecordsToUpdate = Left(strRecordsToUpdate, Len(strRecordsToUpdate) -2

strRecordsToUpdate = strRecordsToUpdate & ")"

strSQL = "UPDATE YourTableNameHere SET YourTableNameHere.Notified = -1 WHERE YourTableNameHere.YourPrimaryKeyFieldHere " & strRecordsToUpdate & ";"

CurrentDb.Execute strSQL, dbFailOnError

Me.YourListBoxName.Requery

that is "air code" meaning untested but it should be close enough to get you where you want.

Bob Larson
Access MVP

[crisostomofred]

Thanks Bob. I will give this a shot. Looking at the code, it should work with some minor tweaking. I will post my final code once I get it working. Thanks again.

Fred