Check Selected Records True

[Guest]

Check Selected Records

I need to check a box “SpecialPrint†in records that I have selected using
the record selectors. I have 7000+ records in a table and need to check the
records that I want to print, edit, and more. After I check the records I
run them through several query’s and do general work with the records, after
I am done I run a query to uncheck all records. Someone gave me this (see
below), it checks the last set of filtered records and it works fine but I
don’t always want all the records from the last filter. My last filter may
produce 415 records but I may only want to check 400. So, I need to check
the highlighted records. Currently, I use the below procedure and manually
uncheck the last 15 records.

I would say I am above beginner and below expert so if you can respond try
to shoot for my level so I understand what to do.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

If (Len(Me.Filter) > 0) Then
CurrentDb().Execute "UPDATE " & Me.RecordSource & " SET SpecialPrint = "
& True & " WHERE " & Me.Filter, dbFailOnError
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End If

Exit Sub

 

[Joseph Meehan]

Check Selected Records

Note: True = -1 Not True or Yes.

 

[Guest]

I need to mass check the records that I have selected.

 

[Guest]

Joseph:

With respect, that's not a good idea. One should always use the Boolean TRUE
not its implementation as -1 . Its what a former colleague of mine used to
call "being unduly chummy with the implementation", which is not regarded as
good programming practice.

I should point out that I speak here as a reformed sinner (always the most
ardent proselytisers!) as I would do this sort of thing myself in my early
days before being duly chastised by my elders and betters<G>.

Ken Sheridan
Stafford, England

 

[Joseph Meehan]

I need to mass check the records that I have selected.

Sorry I though you were already that far along. You can use an update
query.

 

[Guest]

Bryan:

I can't think of any way you'd do this by means of the record selectors
(somebody may know different!), but you should be able to do it using an
unbound form with a multi-select list box which lists all row from the table.
As its RowSource use a query which returns the necessary columns for you to
identify the records and sorts them as appropriate. As well as the list box
add a button to the form with the following in its Click event procedure:

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Dim varItem As Variant
Dim strIDList As String
Dim strSQL As String
Dim ctrl As Control

Set ctrl = Me.lstYourListBox

If ctrl.ItemsSelected.Count > 0 Then

For Each varItem In ctrl.ItemsSelected
strIDList = strIDList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strIDList = Mid(strIDList, 2)

strSQL = "UPDATE YourTable " & _
"SET SpecialPrint = TRUE " & _
"WHERE YourID IN(" & strIDList & ")"

cmd.CommandText = strSQL
cmd.Execute

Else
MsgBox "No Records Selected", vbInformation, "Warning"
End If

I've assumed that the table's name is YourTable, its primary key is YourID,
which is a numeric data type not text, and that YourID is the first column in
the list box's RowSource. The list box's BoundColumn property should be left
as 1. Its ColumnCount property should be the number of columns in the
RowSource query. The ColumnWidths property should be adjusted as necessary
to get the best fit, e.g. if it has 3 columns you might have 1cm;2cm;2cm (or
equivalent in inches).

You can set a list box's MultiSelect property to Simple (in which you just
click each item) or Extended (in which you use Ctrl+Click and /or Shift+Click
to select ranges of items in the usual way).

If the lD list built by the code is too long for the SQL statement to work
post back and I'll give you a small amendment to the code to get around this.

Ken Sheridan
Stafford, England

 

[Guest]

Ken,

I will try it later thanks for the post. Got to take care of a few things
first.