Check box on continuous form
Design by Sue
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 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
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
"Design by Sue" <Desig...@discussions.microsoft.com> wrote in message
news:C32B1781-8557-4766...@microsoft.com...
Marshall Barton
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
--
Marsh
MVP [MS Access]
Design by Sue
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
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
"Design by Sue" <Desig...@discussions.microsoft.com> wrote in message
news:21FB02AF-D511-4C9B...@microsoft.com...
Jeanette Cunningham
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" <Desig...@discussions.microsoft.com> wrote in message
news:21FB02AF-D511-4C9B...@microsoft.com...
Design by Sue
Design by Sue
exactly like what I need, but I don't understand it. Can you please explain
it a bit for me?
Sue
Marshall Barton
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]
Design by Sue wrote:
>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?
>
>
Design by Sue
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
Marshall Barton
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]
Design by Sue wrote:
>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" wrote:
>
>> 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
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
Design by Sue
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
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