Listbox with a checkbox? UI & Coding Suggestions?

[Grahammer]

I'm trying to build an Access form to select some Crews for an Area from a
list of Crews belonging to a Branch. Table layout is shown below.

What I'd like to use on this form is a single Listbox. The form would also
have two textboxes with one containing the Branch index key (SelectedBranch)
and the other containing the Area index key (SelectedArea). The Listbox
would contain a list of all the Crews for the Branch (ie SELECT CrewDesc
FROM Crews WHERE BranchKeyLink=SelectedBranch). In each row of the Listbox
there would also be a checkbox. When checked it would indicate that the Crew
is related to the chosen area.

Can someone lend a hand as how to code this? Is it possible to put a
checkbox into a listbox? Is there possibly a better way to provide the same
functionality with a different kind of interface?

Any suggestions are appreciated!

Table BRANCHES:
BranchKey - Autonumber
BranchDesc - C25

Table CREWS:
CrewKey - Autonumber
CrewDesc - C25
BranchKeyLink - Number (Linked to Branches.BranchKey)

Table AREAS:
AreaKey - Autonumber
AreaDesc - C25
BranchKeyLink - Number (Linked to Branches.BranchKey)

Table AREACREWLINK:
AreaKeyLink - Number (Linked to Areas.AreaKey)
CrewKeyLink - Number (Linked to Crews.CrewKey)

 

[Grahammer]

I'm trying to build an Access form to select some Crews for an Area from a
list of Crews belonging to a Branch. Table layout is shown below.

What I'd like to use on this form is a single Listbox. The form would also
have two textboxes with one containing the Branch index key (SelectedBranch)
and the other containing the Area index key (SelectedArea). The Listbox
would contain a list of all the Crews for the Branch (ie SELECT CrewDesc
FROM Crews WHERE BranchKeyLink=SelectedBranch). In each row of the Listbox
there would also be a checkbox. When checked it would indicate that the Crew
is related to the chosen area.

The SQL query I was hoping to use to populate my listbox would be something
like this:

SELECT (SELECT COUNT(1) FROM CrewAreaLink WHERE
AreaKeyLink=[txtArea].[value]) AS Chosen, Crews.CrewDesc,
Crews.BranchKeyLink FROM Crews WHERE
(((Crews.BranchKeyLink)=txtBranch.value));

txtArea is a textbox holding the index key of the selected Area.
txtBranch is a textbox holding the index key of the selected Branch.
The COUNT(1) should be 0 or 1 depending on if this Crew is selected for this
Area.

For some reason Chosen is always 0. I know that the specified AreaKeyLink
does exist in the CrewAreaLink so a few of the rows should return 1.

 

[Peter R. Fletcher]

I would do what (I think) you are trying to do by using a "Continuous
Form" subform in lieu of your ListBox. I can't think of a way of
having checkboxes as one column of a multi-column ListBox.

I'm trying to build an Access form to select some Crews for an Area from a
list of Crews belonging to a Branch. Table layout is shown below.

What I'd like to use on this form is a single Listbox. The form would also
have two textboxes with one containing the Branch index key (SelectedBranch)
and the other containing the Area index key (SelectedArea). The Listbox
would contain a list of all the Crews for the Branch (ie SELECT CrewDesc
FROM Crews WHERE BranchKeyLink=SelectedBranch). In each row of the Listbox
there would also be a checkbox. When checked it would indicate that the Crew
is related to the chosen area.

Can someone lend a hand as how to code this? Is it possible to put a
checkbox into a listbox? Is there possibly a better way to provide the same
functionality with a different kind of interface?

Any suggestions are appreciated!

Table BRANCHES:
BranchKey - Autonumber
BranchDesc - C25

Table CREWS:
CrewKey - Autonumber
CrewDesc - C25
BranchKeyLink - Number (Linked to Branches.BranchKey)

Table AREAS:
AreaKey - Autonumber
AreaDesc - C25
BranchKeyLink - Number (Linked to Branches.BranchKey)

Table AREACREWLINK:
AreaKeyLink - Number (Linked to Areas.AreaKey)
CrewKeyLink - Number (Linked to Crews.CrewKey)

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Grahammer]

I would do what (I think) you are trying to do by using a "Continuous
Form" subform in lieu of your ListBox. I can't think of a way of
having checkboxes as one column of a multi-column ListBox.

Thanks!

That's what I've figured. I've ended up reworking the interface to avoid the
checkbox.