Filter a List Box using toggle buttons

  • Thread starter Thread starter Jamie Harbour via AccessMonster.com
  • Start date Start date
J

Jamie Harbour via AccessMonster.com

Hello

I have list of people who hold a certain role, the list is a query in a list
box, i would like to be able to create a list of people in the list box based
on their roles, ie a list of people who are captains and admirals, i wanted
to do this using toggle buttons so when you click on and off the button id
adds and removes the people with those roles from the list,

if anybody has any idea then please help me,

Thanks

Jamie
 
Your toggle buttons will be in an Option Group. Use the After Update evento
of the Option Group to alter your list box Row Source:

Select Case Me.opgRoles
Case 1
Me.lstPeople.RowSource = "Select tblPeople.Name From tblPeople _
Where tblPeople.Role = 'Admiral';"
Case 2
Me.lstPeople.RowSource = "Select tblPeople.Name From tblPeople _
Where tblPeople.Role = 'Captain';"
Case 3
Me.lstPeople.RowSource = "Select tblPeople.Name From tblPeople _
Where tblPeople.Role = 'Swabby';"
End Select

Me.lstPeople.Requery
 
Because people can hold multiple roles mt table is set out with each role a
column and a YES in the name that corrresponds with the field, will this
still function or not?

Thanks
Your toggle buttons will be in an Option Group. Use the After Update evento
of the Option Group to alter your list box Row Source:

Select Case Me.opgRoles
Case 1
Me.lstPeople.RowSource = "Select tblPeople.Name From tblPeople _
Where tblPeople.Role = 'Admiral';"
Case 2
Me.lstPeople.RowSource = "Select tblPeople.Name From tblPeople _
Where tblPeople.Role = 'Captain';"
Case 3
Me.lstPeople.RowSource = "Select tblPeople.Name From tblPeople _
Where tblPeople.Role = 'Swabby';"
End Select

Me.lstPeople.Requery

Hello
Click to expand...
[quoted text clipped - 9 lines]
Jamie
Click to expand...
Click to expand...
 
Presumably you have 3 tables:
- tblClient: one record for each person, with ClientID primary key.
- tblRole: one record for each role, with RoleID primary key.
- tblClientRole: one record for each valid combination, with fields:
ClientID (foreign key to tblClient.ClientID), and
RoleID (foreign key to tblRole.RoleID).

Now you want to set the RowSource of your list box, based on the state of
the various toggle buttons.

In the end, the RowSource will be a string that looks like this:
SELECT DISTINCT tblClient.ClientID, tblClient.Surname, tblClient.FirstName
FROM tblClient INNER JOIN tblClientRole ON tblClient.ClientID =
tblClientRole.ClientID
WHERE tblClientRole.RoleID IN (2, 8, 12, 99)
ORDER BY tblClient.Surname, tblClient.FirstName;

The IN (...) will be determined by which toggle buttons are depressed. The
example below assumes you have toggle buttons named tgl1, tgl2, tgl3, etc.
When the button is depressed, its value is True. The code responds to the
updated state of the button by building the SQL string based on the
depressed buttons. If no buttons are depressed, no people are returned. The
name of the list box is assumed to be List1.

Private Sub tgl1_AfterUpate
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT DISTINCT tblClient.ClientID, tblClient.Surname,
tblClient.FirstName FROM tblClient INNER JOIN tblClientRole ON
tblClient.ClientID = tblClientRole.ClientID WHERE "
Const strcTail = " ORDER BY tblClient.Surname, tblClient.FirstName;"
Const strcSep = ", "

If Me.tgl1.Value Then
strSql = strSql & "1" & strcSep
End If
If Me.tgl2.Value Then
strSql = strSql & "2" & strcSep
End If
'etc for other toggle buttons.

lngLen = Len(strSql) - Len(strcSep) 'Without trailing comma and
space.
If lngLen > 0 Then
strSql = strcStub & "(tblClientRoleID IN (" & Left$(strSql, lngLen)
& "))" & strcTail
Else
strSql = strcStub & "(False)" & strcTail
End If

'Debug.Print strSql
Me.List1.RowSource = strSql
End Sub

Private Sub tgl2_AfterUpdate
Call tgl1_AfterUpate
End Sub

Private Sub tgl3_AfterUpdate
Call tgl1_AfterUpate
End Sub

The other toggle buttons will also Call tgl1_AfterUpate.

That's aircode, but hopefully you can apply to your situation.
 
With some modification in the Case statement, yes. For example:

Me.lstPeople.RowSource = "Select tblPeople.Name From tblPeople _
Where tblPeople.Admiral = True;"


Jamie Harbour via AccessMonster.com said:
Because people can hold multiple roles mt table is set out with each role a
column and a YES in the name that corrresponds with the field, will this
still function or not?

Thanks
Your toggle buttons will be in an Option Group. Use the After Update evento
of the Option Group to alter your list box Row Source:

Select Case Me.opgRoles
Case 1
Me.lstPeople.RowSource = "Select tblPeople.Name From tblPeople _
Where tblPeople.Role = 'Admiral';"
Case 2
Me.lstPeople.RowSource = "Select tblPeople.Name From tblPeople _
Where tblPeople.Role = 'Captain';"
Case 3
Me.lstPeople.RowSource = "Select tblPeople.Name From tblPeople _
Where tblPeople.Role = 'Swabby';"
End Select

Me.lstPeople.Requery

Hello
Click to expand...
[quoted text clipped - 9 lines]
Jamie
Click to expand...
Click to expand...
Click to expand...
 
The thing is my table is not on record for every role is is one record for
every person and each role has column in the table and there is a yes in the
column if they perform that role,

Allen said:
Presumably you have 3 tables:
- tblClient: one record for each person, with ClientID primary key.
- tblRole: one record for each role, with RoleID primary key.
- tblClientRole: one record for each valid combination, with fields:
ClientID (foreign key to tblClient.ClientID), and
RoleID (foreign key to tblRole.RoleID).

Now you want to set the RowSource of your list box, based on the state of
the various toggle buttons.

In the end, the RowSource will be a string that looks like this:
SELECT DISTINCT tblClient.ClientID, tblClient.Surname, tblClient.FirstName
FROM tblClient INNER JOIN tblClientRole ON tblClient.ClientID =
tblClientRole.ClientID
WHERE tblClientRole.RoleID IN (2, 8, 12, 99)
ORDER BY tblClient.Surname, tblClient.FirstName;

The IN (...) will be determined by which toggle buttons are depressed. The
example below assumes you have toggle buttons named tgl1, tgl2, tgl3, etc.
When the button is depressed, its value is True. The code responds to the
updated state of the button by building the SQL string based on the
depressed buttons. If no buttons are depressed, no people are returned. The
name of the list box is assumed to be List1.

Private Sub tgl1_AfterUpate
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT DISTINCT tblClient.ClientID, tblClient.Surname,
tblClient.FirstName FROM tblClient INNER JOIN tblClientRole ON
tblClient.ClientID = tblClientRole.ClientID WHERE "
Const strcTail = " ORDER BY tblClient.Surname, tblClient.FirstName;"
Const strcSep = ", "

If Me.tgl1.Value Then
strSql = strSql & "1" & strcSep
End If
If Me.tgl2.Value Then
strSql = strSql & "2" & strcSep
End If
'etc for other toggle buttons.

lngLen = Len(strSql) - Len(strcSep) 'Without trailing comma and
space.
If lngLen > 0 Then
strSql = strcStub & "(tblClientRoleID IN (" & Left$(strSql, lngLen)
& "))" & strcTail
Else
strSql = strcStub & "(False)" & strcTail
End If

'Debug.Print strSql
Me.List1.RowSource = strSql
End Sub

Private Sub tgl2_AfterUpdate
Call tgl1_AfterUpate
End Sub

Private Sub tgl3_AfterUpdate
Call tgl1_AfterUpate
End Sub

The other toggle buttons will also Call tgl1_AfterUpate.

That's aircode, but hopefully you can apply to your situation.
Hello
Click to expand...
[quoted text clipped - 6 lines]
to do this using toggle buttons so when you click on and off the button id
adds and removes the people with those roles from the list
Click to expand...
Click to expand...
 
Jamie, you can still build the SQL statement, using OR between the different
values.

To get an example of the target string, mock up a query. In query design
type True on different lines of the Criteria under the various fields. Then
switch to SQL View (View menu) to see what you need.

(The tick boxes instead of related records is way less flexible.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jamie Harbour via AccessMonster.com said:
The thing is my table is not on record for every role is is one record for
every person and each role has column in the table and there is a yes in
the
column if they perform that role,

Allen said:
Presumably you have 3 tables:
- tblClient: one record for each person, with ClientID primary key.
- tblRole: one record for each role, with RoleID primary key.
- tblClientRole: one record for each valid combination, with fields:
ClientID (foreign key to tblClient.ClientID), and
RoleID (foreign key to tblRole.RoleID).

Now you want to set the RowSource of your list box, based on the state of
the various toggle buttons.

In the end, the RowSource will be a string that looks like this:
SELECT DISTINCT tblClient.ClientID, tblClient.Surname, tblClient.FirstName
FROM tblClient INNER JOIN tblClientRole ON tblClient.ClientID =
tblClientRole.ClientID
WHERE tblClientRole.RoleID IN (2, 8, 12, 99)
ORDER BY tblClient.Surname, tblClient.FirstName;

The IN (...) will be determined by which toggle buttons are depressed. The
example below assumes you have toggle buttons named tgl1, tgl2, tgl3, etc.
When the button is depressed, its value is True. The code responds to the
updated state of the button by building the SQL string based on the
depressed buttons. If no buttons are depressed, no people are returned.
The
name of the list box is assumed to be List1.

Private Sub tgl1_AfterUpate
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT DISTINCT tblClient.ClientID,
tblClient.Surname,
tblClient.FirstName FROM tblClient INNER JOIN tblClientRole ON
tblClient.ClientID = tblClientRole.ClientID WHERE "
Const strcTail = " ORDER BY tblClient.Surname, tblClient.FirstName;"
Const strcSep = ", "

If Me.tgl1.Value Then
strSql = strSql & "1" & strcSep
End If
If Me.tgl2.Value Then
strSql = strSql & "2" & strcSep
End If
'etc for other toggle buttons.

lngLen = Len(strSql) - Len(strcSep) 'Without trailing comma and
space.
If lngLen > 0 Then
strSql = strcStub & "(tblClientRoleID IN (" & Left$(strSql,
lngLen)
& "))" & strcTail
Else
strSql = strcStub & "(False)" & strcTail
End If

'Debug.Print strSql
Me.List1.RowSource = strSql
End Sub

Private Sub tgl2_AfterUpdate
Call tgl1_AfterUpate
End Sub

Private Sub tgl3_AfterUpdate
Call tgl1_AfterUpate
End Sub

The other toggle buttons will also Call tgl1_AfterUpate.

That's aircode, but hopefully you can apply to your situation.
Hello
Click to expand...
[quoted text clipped - 6 lines]
to do this using toggle buttons so when you click on and off the button
id
adds and removes the people with those roles from the list
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Toggling a List Box 3
List Box Position 1
Counting multiple memberships 1
PC Build Advice 5
VBA code to select website drop down opion 0
Using toggle button to turn filter on and off 1
Use a string as criteria to run query 4
Inserting cells (shift cells right) using macro 0

Back
Top