Check box on continuous form

[Design by Sue]

I have a continuous form listing available parts. I need to be able to
select a part from the list using a check box (or something that will give a
true if selected). I tried an unbound check box, but when clicked, it
selects all of the records in the form instead of only the one record.

Am I missing a setting? or what can I do to create this selection
capability. (which I am then using code to complete the action I need)

Thanks
Sue

 

[Minton M]

I have a continuous form listing available parts. I need to be able to
select a part from the list using a check box (or something that will give a
true if selected). I tried an unbound check box, but when clicked, it
selects all of the records in the form instead of only the one record.

Am I missing a setting? or what can I do to create this selection
capability. (which I am then using code to complete the action I need)

Thanks
Sue

I think Stephen Lebans has something on his site that makes this work
but unfortunately it's one of the limitations of continuous forms and
it's hard to do without massive friggery. Can you replace the form
with a listbox that enables the user to multiselect items? If this is
an acceptable alternative it's seriously easier than trying to make it
work on a continuous form.

-- James

 

[Jeanette Cunningham]

Hi,
Yes, an unbound checkbox will always do this on a continuous form.
You could make the checkbox bound to a Yes/No field in the recordsource for
your form by adding the Yes/No field to one of the tables.
Or you could have a separate table with the Yes/No field and an ID that
relates it back to one of your tables.

Jeanette Cunningham

 

[Marshall Barton]

I have a continuous form listing available parts. I need to be able to
select a part from the list using a check box (or something that will give a
true if selected). I tried an unbound check box, but when clicked, it
selects all of the records in the form instead of only the one record.

Am I missing a setting? or what can I do to create this selection
capability. (which I am then using code to complete the action I need)

The check box needs to be bound to a field in a record
source table. You can use the parts table only if your
program can not be used by more than one person at a time.

If you ever want to allow for multiple users then you should
use a separate table that has records for each selected part
for each user.

If you are not going to try to preserve informtion about who
selected each part when the form is closed, then you
might(?) be able to use the clever technique in Albert's
Multi Select Example:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

 

[Design by Sue]

All three of you have touched on the situation. Here's the problem as best
as I can state it. I have a form with a subform, the subform is based on a
query combines the PartNumber and Suffix into one field (PartNumber-Suffix)
and included the fields PartNumber, Suffix, and Returned (which is the Yes/No
field in the table. The main form has a field in which the user selects a
line number. The subform is a continuous form and shows all
partnumber-suffixes that are currently on that line. The user is to check
the box if he wants to select the partnumber to return it to "stock". To
make this happen I have written sql code on AfterUpdate that assigns the
user ID and the selected line number to fields in the original table called
TempMechanic and TempLine. (The problem is here - see below) Then I will
create a "Return to Stock" button and code it to add the user ID to the
Mechanic field, put"stock" in the Line field, remove the info in the Temp
Mechanic and TempLine fields and clear the check box in the Returned field.

Now the problem I mentioned above. The sql seems to work, except that I get
an error message after the confirmation message that records are going to
change that tells that another user has changed the form. (if I click ok on
this message it changes the PartNumber, Suffix and Mechanic records to a
crazy combination of numbers and the only to clean it up is to "Repair and
Compact the Database" which removed the changed records - I know that sounds
crazy but it happens) If I click no to this 2nd message box, only the
correct changes were made, but the Returned check box (in the table) has not
bee checked. I think the conflict I am getting is that the form is updating
the Returned field after the sql is run and buggering the table.

So by my coding I am keeping track of the user incase 2 users are using the
program at the same time, but I can't make the check box work correctly.

Hope this makes sense! It is so hard to put so much in a few words.

Thanks
Sue

 

[Jeanette Cunningham]

Sue,
this will happen if you have 2 forms open at the same time that are editing
the same fields in the same table.
Your description is long and involved and I haven't read it carefully, but
this is something quick and easy to check.

Jeanette Cunningham

 

[Jeanette Cunningham]

Sue,
how did you decide to do the checkbox on the continuous form? Is it a field
in a table?
If so can you tell us which table and what the relationships are?

Jeanette Cunningham

 

[Design by Sue]

Sue,
how did you decide to do the checkbox on the continuous form? Is it a field
in a table?
If so can you tell us which table and what the relationships are?

Jeanette Cunningham

 

[Design by Sue]

Marshall the code you referred me to in the Multi Select Example looks
exactly like what I need, but I don't understand it. Can you please explain
it a bit for me?

Sue

 

[Marshall Barton]

That technique uses a private collection to keep track of
the selected records. The collection entries are a value
that uniquely identifies the record (primary key field?) so
you can refer to the selected records. If a record is not
selected then there is no item in the collection for that
record (if a previously selected record is deselected, its
item is removed from the collection).

To display the check box correctly, its control source is
set to a function that checks to see if the record's key is
in the collection and returns True or False accordingly.

When you want to do something with the selected records, you
need to write some code that loops through the collection
and does whatever to return the items to stock.

It's a pretty clever idea and like most everything, once you
understand it, it's fairly simple.

 

[Design by Sue]

Thank you so much for your response and good explination. I found someone to
work with me on this and it seems I can't use the code because I need to use
a query as the record source for the form. The query is to concatenate two
fields (which together are the primary keys) and use another field as a
criteria. We couldn't find a way to refer to this concatenation for the code.

This would have been perfect for what I need to do.

Thanks
Sue

That technique uses a private collection to keep track of
the selected records. The collection entries are a value
that uniquely identifies the record (primary key field?) so
you can refer to the selected records. If a record is not
selected then there is no item in the collection for that
record (if a previously selected record is deselected, its
item is removed from the collection).

To display the check box correctly, its control source is
set to a function that checks to see if the record's key is
in the collection and returns True or False accordingly.

When you want to do something with the selected records, you
need to write some code that loops through the collection
and does whatever to return the items to stock.

It's a pretty clever idea and like most everything, once you
understand it, it's fairly simple.
--
Marsh
MVP [MS Access]

Marshall the code you referred me to in the Multi Select Example looks
exactly like what I need, but I don't understand it. Can you please explain
it a bit for me?

 

[Marshall Barton]

Items in a collection can contain all kinds of things, it
can certainly contain a concatenated string. If needed you
can use a separator character in the concatenation and later
the Split function makes it easy to get the two separate
field values.

 

[Design by Sue]

Marshall - I have ended up using the MultiSelect code you wrote about and
have it working in my database - thank you for you help so far! I have this
code on a subform (ChoiceSubFrm) on a main form (LocationFrm) Now my problem
is that I need to empty the collection from the main form.


to better explain, (I hope) my main form has 3 dropdown lists for the user
to select from. When the Search button is clicked, the query results based
on these choices, gives a list displayed on a continuous form on the Sub
Form. I have used the MultiSelect code to allow the user to select records
in this form and given buttons to process the information as required. This
all works well, except when the user changes the criteria and clicks Search
again. I need to clear out the collection, clear the check boxes and
re-search on the new criteria entered. At this point, if the user
re-searched, the checks remain on the previously selected items.

What I am trying to do is Set ???? = Nothing. On the subform is is Set
colCheckBox - Nothing. How do I clear the collection from the main form?

I hope this makes sense.

Thanks
Sue

Items in a collection can contain all kinds of things, it
can certainly contain a concatenated string. If needed you
can use a separator character in the concatenation and later
the Split function makes it easy to get the two separate
field values.
--
Marsh
MVP [MS Access]

Thank you so much for your response and good explination. I found someone to
work with me on this and it seems I can't use the code because I need to use
a query as the record source for the form. The query is to concatenate two
fields (which together are the primary keys) and use another field as a
criteria. We couldn't find a way to refer to this concatenation for the code.

This would have been perfect for what I need to do.

 

[Design by Sue]

Marshall - I have ended up using the MultiSelect code you wrote about and
have it working in my database - thank you for you help so far! I have this
code on a subform (ChoiceSubFrm) on a main form (LocationFrm) Now my problem
is that I need to empty the collection from the main form.


to better explain, (I hope) my main form has 3 dropdown lists for the user
to select from. When the Search button is clicked, the query results based
on these choices, gives a list displayed on a continuous form on the Sub
Form. I have used the MultiSelect code to allow the user to select records
in this form and given buttons to process the information as required. This
all works well, except when the user changes the criteria and clicks Search
again. I need to clear out the collection, clear the check boxes and
re-search on the new criteria entered. At this point, if the user
re-searched, the checks remain on the previously selected items.

What I am trying to do is Set ???? = Nothing. On the subform is is Set
colCheckBox - Nothing. How do I clear the collection from the main form?

I hope this makes sense.

Thanks
Sue

Items in a collection can contain all kinds of things, it
can certainly contain a concatenated string. If needed you
can use a separator character in the concatenation and later
the Split function makes it easy to get the two separate
field values.
--
Marsh
MVP [MS Access]

Thank you so much for your response and good explination. I found someone to
work with me on this and it seems I can't use the code because I need to use
a query as the record source for the form. The query is to concatenate two
fields (which together are the primary keys) and use another field as a
criteria. We couldn't find a way to refer to this concatenation for the code.

This would have been perfect for what I need to do.

 

[Marshall Barton]

Marshall - I have ended up using the MultiSelect code you wrote about and
have it working in my database - thank you for you help so far! I have this
code on a subform (ChoiceSubFrm) on a main form (LocationFrm) Now my problem
is that I need to empty the collection from the main form.


to better explain, (I hope) my main form has 3 dropdown lists for the user
to select from. When the Search button is clicked, the query results based
on these choices, gives a list displayed on a continuous form on the Sub
Form. I have used the MultiSelect code to allow the user to select records
in this form and given buttons to process the information as required. This
all works well, except when the user changes the criteria and clicks Search
again. I need to clear out the collection, clear the check boxes and
re-search on the new criteria entered. At this point, if the user
re-searched, the checks remain on the previously selected items.

What I am trying to do is Set ???? = Nothing. On the subform is is Set
colCheckBox - Nothing. How do I clear the collection from the main form?

That depends on how you delared the collection. If you
declared the collection using:

Public colCheckBox As Collection

Then you could refer to it from the main form using:

Set Me.subformcontrolname.Form.colCheckBox = Nothing

BUT, setting it to Nothig will destruct the collection and
require you to recreate the collection before you could use
it for anything else (i.e. close and reopen the main form).

I think you really want to just remove all the entries in
the collection instead. I think a better way for the main
form to clear the entries is to leave the declaration:

Private colCheckBox As Collection

and add a sub procedure to the subform:

Public Sub ClearAll()
Dim k As Integer
For k = colCheckBox.Count - 1 Step -1
colCheckBox(k).Remove
Next k
End Sub

Then the main form can clear all the entries with a line
like:

Me.subformcontrolname.Form.ClearAll