modifying Kallal's Multiselect database example

[Amanda]

I used Albert Kallal's Multi Select example to create a continuous form that
uses checkboxes to select specific records in an animal management database.
(I work at a wildcat sanctuary)

So I incorporated Kallal's code into my form, but I am doing the opposite
operation. In Kallal's program, the record ID is added to a collection every
time the checkbox for a record is clicked, and it is removed from the
collection when the checkbox is unclicked.

I want all the records checked to begin with, and then the user can uncheck
the records. I can change the checkbox so that it initially displays as
checked, but the collection of all the checked values is still empty, so if
you uncheck the box, you get an error- it can't remove a value that isn't
there.

The list of records is created from criteria that are selected from unbound
checkboxes in the header (basically the Search Criteria tutorial from Allen
Browne), so I have a strWhere variable with the criteria that creates the
continuous form part.

How can I populate the colCheckbox collection initially with all the record
ID values?

Kallal's example database is at
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

Thanks, Amanda

 

[Graham Mandeno]

Hi Amanda

The following code will add every record in your current recordset to the
collection:

Private Function SelectAll()
' empty the collection
Set colCheckBox = Nothing
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
colCheckBox.Add CLng(!ContactID), CStr(!ContactID)
.MoveNext
Loop
End With
Me.Check11.Requery
End Function

You will need to call this when the form first loads, and also whenever a
filter is applied.

The problem is that if you apply a filter using the user interface (the
Records menu or the toolbar or the right-click menu) then you need a way of
executing your code AFTER the filter has been applied. There is an
ApplyFilter event, but unfortunately this is fired BEFORE the filter is
applied.

However, after applying a filter or loading the form initially, one of the
first things that happens is that Form_Current is called for the first
record, so I've just tested this workaround and it seems to work OK.

Declare a module-level boolean variable:
Dim fCollectionInitialized As Boolean

In Form_ApplyFilter and BEFORE you apply a filter in any of your code, set
this variable to False.

In Form_Current, if the boolean is false then call SelectAll and set it to
True:

Private Sub Form_Current()
If Not fCollectionInitialized Then
SelectAll
fCollectionInitialized = True
End If
End Sub

 

[Amanda]

The good news is that the code you suggested is working well to populate the
collection, so when I press the button to show selected recrods- the string
has been properly populated to show all the records.

The bad news is that although the string is populated, and the boxes are
checked (I did this by setting the default property of the checkbox to True),
the following code does not work when the user attempts to uncheck the box:

Private Sub Command13_Click()
Debug.Print "contact = " & Me.ContactID

If IsChecked(Me.ContactID) = False Then
colCheckBox.Add CLng(Me.ContactID), CStr(Me.ContactID)
Else
colCheckBox.Remove (CStr(Me.ContactID))
End If
Me.Check11.Requery

End Sub

I've found this is because even though the box displays as checked, this
"if" statement still evaluates the Ischecked as false. Perhaps this is
because the default value is not set until after the Command13 button is
clicked and the function has run.

I've had no luck in setting the Me.Check11 to "true" in the code, because
isChecked[Animal ID] is its control source, and I'm at a loss how to
workaround. I'm not very familiar with what tying this code to the control
source is doing.

And thank you for the effort to evaluate the issue and respond- you
obviously had to download the example- and I appreciate the extra effort.

Thanks, Amanda

Hi Amanda

The following code will add every record in your current recordset to the
collection:

Private Function SelectAll()
' empty the collection
Set colCheckBox = Nothing
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
colCheckBox.Add CLng(!ContactID), CStr(!ContactID)
.MoveNext
Loop
End With
Me.Check11.Requery
End Function

You will need to call this when the form first loads, and also whenever a
filter is applied.

The problem is that if you apply a filter using the user interface (the
Records menu or the toolbar or the right-click menu) then you need a way of
executing your code AFTER the filter has been applied. There is an
ApplyFilter event, but unfortunately this is fired BEFORE the filter is
applied.

However, after applying a filter or loading the form initially, one of the
first things that happens is that Form_Current is called for the first
record, so I've just tested this workaround and it seems to work OK.

Declare a module-level boolean variable:
Dim fCollectionInitialized As Boolean

In Form_ApplyFilter and BEFORE you apply a filter in any of your code, set
this variable to False.

In Form_Current, if the boolean is false then call SelectAll and set it to
True:

Private Sub Form_Current()
If Not fCollectionInitialized Then
SelectAll
fCollectionInitialized = True
End If
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I used Albert Kallal's Multi Select example to create a continuous form
that
uses checkboxes to select specific records in an animal management
database.
(I work at a wildcat sanctuary)

So I incorporated Kallal's code into my form, but I am doing the opposite
operation. In Kallal's program, the record ID is added to a collection
every
time the checkbox for a record is clicked, and it is removed from the
collection when the checkbox is unclicked.

I want all the records checked to begin with, and then the user can
uncheck
the records. I can change the checkbox so that it initially displays as
checked, but the collection of all the checked values is still empty, so
if
you uncheck the box, you get an error- it can't remove a value that isn't
there.

The list of records is created from criteria that are selected from
unbound
checkboxes in the header (basically the Search Criteria tutorial from
Allen
Browne), so I have a strWhere variable with the criteria that creates the
continuous form part.

How can I populate the colCheckbox collection initially with all the
record
ID values?

Kallal's example database is at
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

Thanks, Amanda

 

[Graham Mandeno]

Hi Amanda

You should not need to set the default value for the textbox. In any case,
it should not make any difference, as the checkbox is bound to an
expression.

You say the control source of the checkbox is:
isChecked[Animal ID]

Did you really mean that? Because it should be:
=isChecked([Animal ID])

Also, can you please confirm that the SelectAll code is running by setting a
breakpoint on the first executable line:
Set colCheckBox = Nothing

Also, did you cut and paste the code below from your own app, or from the
sample? Because every instance of ContactID should be replaced by [Animal
ID].
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

The good news is that the code you suggested is working well to populate
the
collection, so when I press the button to show selected recrods- the
string
has been properly populated to show all the records.

The bad news is that although the string is populated, and the boxes are
checked (I did this by setting the default property of the checkbox to
True),
the following code does not work when the user attempts to uncheck the
box:

Private Sub Command13_Click()
Debug.Print "contact = " & Me.ContactID

If IsChecked(Me.ContactID) = False Then
colCheckBox.Add CLng(Me.ContactID), CStr(Me.ContactID)
Else
colCheckBox.Remove (CStr(Me.ContactID))
End If
Me.Check11.Requery

End Sub

I've found this is because even though the box displays as checked, this
"if" statement still evaluates the Ischecked as false. Perhaps this is
because the default value is not set until after the Command13 button is
clicked and the function has run.

I've had no luck in setting the Me.Check11 to "true" in the code, because
isChecked[Animal ID] is its control source, and I'm at a loss how to
workaround. I'm not very familiar with what tying this code to the
control
source is doing.

And thank you for the effort to evaluate the issue and respond- you
obviously had to download the example- and I appreciate the extra effort.

Thanks, Amanda

Hi Amanda

The following code will add every record in your current recordset to the
collection:

Private Function SelectAll()
' empty the collection
Set colCheckBox = Nothing
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
colCheckBox.Add CLng(!ContactID), CStr(!ContactID)
.MoveNext
Loop
End With
Me.Check11.Requery
End Function

You will need to call this when the form first loads, and also whenever a
filter is applied.

The problem is that if you apply a filter using the user interface (the
Records menu or the toolbar or the right-click menu) then you need a way
of
executing your code AFTER the filter has been applied. There is an
ApplyFilter event, but unfortunately this is fired BEFORE the filter is
applied.

However, after applying a filter or loading the form initially, one of
the
first things that happens is that Form_Current is called for the first
record, so I've just tested this workaround and it seems to work OK.

Declare a module-level boolean variable:
Dim fCollectionInitialized As Boolean

In Form_ApplyFilter and BEFORE you apply a filter in any of your code,
set
this variable to False.

In Form_Current, if the boolean is false then call SelectAll and set it
to
True:

Private Sub Form_Current()
If Not fCollectionInitialized Then
SelectAll
fCollectionInitialized = True
End If
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I used Albert Kallal's Multi Select example to create a continuous form
that
uses checkboxes to select specific records in an animal management
database.
(I work at a wildcat sanctuary)

So I incorporated Kallal's code into my form, but I am doing the
opposite
operation. In Kallal's program, the record ID is added to a collection
every
time the checkbox for a record is clicked, and it is removed from the
collection when the checkbox is unclicked.

I want all the records checked to begin with, and then the user can
uncheck
the records. I can change the checkbox so that it initially displays
as
checked, but the collection of all the checked values is still empty,
so
if
you uncheck the box, you get an error- it can't remove a value that
isn't
there.

The list of records is created from criteria that are selected from
unbound
checkboxes in the header (basically the Search Criteria tutorial from
Allen
Browne), so I have a strWhere variable with the criteria that creates
the
continuous form part.

How can I populate the colCheckbox collection initially with all the
record
ID values?

Kallal's example database is at
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

Thanks, Amanda