Toggling a List Box

J

Jen

I have a list box containing employee names; I want to toggle it back and
forth between showing all employees and showing just current employees.

Employees are stored in an Employees Table with FName, LName, Init (a
shortened max 8-character version of their name) and a yes/no field to
determine whether they're active or not.

The AllEmployeesFullName query pulls FName and LName into a calculated
FullName field.

The ActiveEmployees query pulls FName and LName into a calculated FullName
field, plus selects only employees whose status is active.

My list box is based on the ActiveEmployees query with the following
RowSource:
SELECT ActiveEmployees.Init, ActiveEmployees.FullName FROM ActiveEmployees
ORDER BY ActiveEmployees.FullName

The form also contains a command button with the following code:
Private Sub Command10_Click()
If Me!lstEmployees.RowSource = "SELECT ActiveEmployees.Init,
ActiveEmployees.FullName FROM ActiveEmployees ORDER BY
ActiveEmployees.FullName;" Then
Me!lstEmployees.RowSource = "SELECT AllEmployeesFullName.Init,
AllEmployeesFullName.FullName FROM AllEmployeesFullName ORDER BY
AllEmployeesFullName.FullName;"
ElseIf Me!lstEmployees.RowSource = "SELECT AllEmployeesFullName.Init,
AllEmployeesFullName.FullName FROM AllEmployeesFullName ORDER BY
AllEmployeesFullName.FullName;" Then
Me!lstEmployees.RowSource = "SELECT ActiveEmployees.Init,
ActiveEmployees.FullName FROM ActiveEmployees ORDER BY
ActiveEmployees.FullName;"
End If
Me!lstEmployees.Requery
End Sub

This was a simple attempt to get the toggling to work but when I click the
button, nothing happens. I have tried both the code strings as the row
source, and they both do what they're supposed to do (i.e., list either all
employees or only active employees), but the button won't toggle between them.

What am I missing here?

Thanks in advance,
Jen
 
M

Mr B

Jen,

You can place a Group control on your form with three option buttons. One
for All employees, one for Active Employees and one for Inactive Employees.
Then in the "After Update" event of the Option Group control, use code to
reassign the Row Source of your list box using a Select Case statement.

You can just redefine the Sql statement for the row source.
Place the code below in the After Update event of the Option Group control.
Example: (air code)

Dim strSql as String

select me.NameOfGroupControl
Case 1 'show all employees
strSql = "SELECT Employees.Init, " _
& "[Employees]![FName] & " " & [Employees]![LName] " _
& "AS FullName FROM Employees " _
& "ORDER BY [Employees]![FName] & " " & [Employees]![LName];"

Case 2 'show only Active Employees
strSql = "SELECT Employees.Init, " _
& "[Employees]![FName] & " " & [Employees]![LName] " _
& "AS FullName FROM Employees " _
& "WHERE (((Employees.Active)=-1)) " _
& "ORDER BY [Employees]![FName] & " " & [Employees]![LName];"

Case 3 'show only Inactive Employees
strSql = "SELECT Employees.Init, " _
& "[Employees]![FName] & " " & [Employees]![LName] " _
& "AS FullName FROM Employees " _
& "WHERE (((Employees.Active)=0)) " _
& "ORDER BY [Employees]![FName] & " " & [Employees]![LName];"

End Select

With me.NameYourOfListbox
.Rowsource = strSql
.Requery
End With

Just change the "NameOfGroupControl" to the name of the Group Contorl that
you create and change the "NameYourOfListbox" to the actual name of your list
box.
 
J

Jen

Thanks Mr B - this worked like a charm, with a few minor changes (I used the
SQL statements I originally had in my example, but using the Select Case
statement and an option group as you suggested).

Jen

Mr B said:
Jen,

You can place a Group control on your form with three option buttons. One
for All employees, one for Active Employees and one for Inactive Employees.
Then in the "After Update" event of the Option Group control, use code to
reassign the Row Source of your list box using a Select Case statement.

You can just redefine the Sql statement for the row source.
Place the code below in the After Update event of the Option Group control.
Example: (air code)

Dim strSql as String

select me.NameOfGroupControl
Case 1 'show all employees
strSql = "SELECT Employees.Init, " _
& "[Employees]![FName] & " " & [Employees]![LName] " _
& "AS FullName FROM Employees " _
& "ORDER BY [Employees]![FName] & " " & [Employees]![LName];"

Case 2 'show only Active Employees
strSql = "SELECT Employees.Init, " _
& "[Employees]![FName] & " " & [Employees]![LName] " _
& "AS FullName FROM Employees " _
& "WHERE (((Employees.Active)=-1)) " _
& "ORDER BY [Employees]![FName] & " " & [Employees]![LName];"

Case 3 'show only Inactive Employees
strSql = "SELECT Employees.Init, " _
& "[Employees]![FName] & " " & [Employees]![LName] " _
& "AS FullName FROM Employees " _
& "WHERE (((Employees.Active)=0)) " _
& "ORDER BY [Employees]![FName] & " " & [Employees]![LName];"

End Select

With me.NameYourOfListbox
.Rowsource = strSql
.Requery
End With

Just change the "NameOfGroupControl" to the name of the Group Contorl that
you create and change the "NameYourOfListbox" to the actual name of your list
box.
--
HTH

Mr B
draccess at askdoctoraccess.com


Jen said:
I have a list box containing employee names; I want to toggle it back and
forth between showing all employees and showing just current employees.

Employees are stored in an Employees Table with FName, LName, Init (a
shortened max 8-character version of their name) and a yes/no field to
determine whether they're active or not.

The AllEmployeesFullName query pulls FName and LName into a calculated
FullName field.

The ActiveEmployees query pulls FName and LName into a calculated FullName
field, plus selects only employees whose status is active.

My list box is based on the ActiveEmployees query with the following
RowSource:
SELECT ActiveEmployees.Init, ActiveEmployees.FullName FROM ActiveEmployees
ORDER BY ActiveEmployees.FullName

The form also contains a command button with the following code:
Private Sub Command10_Click()
If Me!lstEmployees.RowSource = "SELECT ActiveEmployees.Init,
ActiveEmployees.FullName FROM ActiveEmployees ORDER BY
ActiveEmployees.FullName;" Then
Me!lstEmployees.RowSource = "SELECT AllEmployeesFullName.Init,
AllEmployeesFullName.FullName FROM AllEmployeesFullName ORDER BY
AllEmployeesFullName.FullName;"
ElseIf Me!lstEmployees.RowSource = "SELECT AllEmployeesFullName.Init,
AllEmployeesFullName.FullName FROM AllEmployeesFullName ORDER BY
AllEmployeesFullName.FullName;" Then
Me!lstEmployees.RowSource = "SELECT ActiveEmployees.Init,
ActiveEmployees.FullName FROM ActiveEmployees ORDER BY
ActiveEmployees.FullName;"
End If
Me!lstEmployees.Requery
End Sub

This was a simple attempt to get the toggling to work but when I click the
button, nothing happens. I have tried both the code strings as the row
source, and they both do what they're supposed to do (i.e., list either all
employees or only active employees), but the button won't toggle between them.

What am I missing here?

Thanks in advance,
Jen
Click to expand...
Click to expand...
 
M

Mr B

Your are quite welcome.

I'm glad it worked for you.

Mr B
email if needed to:
draccess at askdoctoraccess dot com


Jen said:
Thanks Mr B - this worked like a charm, with a few minor changes (I used the
SQL statements I originally had in my example, but using the Select Case
statement and an option group as you suggested).

Jen

Mr B said:
Jen,

You can place a Group control on your form with three option buttons. One
for All employees, one for Active Employees and one for Inactive Employees.
Then in the "After Update" event of the Option Group control, use code to
reassign the Row Source of your list box using a Select Case statement.

You can just redefine the Sql statement for the row source.
Place the code below in the After Update event of the Option Group control.
Example: (air code)

Dim strSql as String

select me.NameOfGroupControl
Case 1 'show all employees
strSql = "SELECT Employees.Init, " _
& "[Employees]![FName] & " " & [Employees]![LName] " _
& "AS FullName FROM Employees " _
& "ORDER BY [Employees]![FName] & " " & [Employees]![LName];"

Case 2 'show only Active Employees
strSql = "SELECT Employees.Init, " _
& "[Employees]![FName] & " " & [Employees]![LName] " _
& "AS FullName FROM Employees " _
& "WHERE (((Employees.Active)=-1)) " _
& "ORDER BY [Employees]![FName] & " " & [Employees]![LName];"

Case 3 'show only Inactive Employees
strSql = "SELECT Employees.Init, " _
& "[Employees]![FName] & " " & [Employees]![LName] " _
& "AS FullName FROM Employees " _
& "WHERE (((Employees.Active)=0)) " _
& "ORDER BY [Employees]![FName] & " " & [Employees]![LName];"

End Select

With me.NameYourOfListbox
.Rowsource = strSql
.Requery
End With

Just change the "NameOfGroupControl" to the name of the Group Contorl that
you create and change the "NameYourOfListbox" to the actual name of your list
box.
--
HTH

Mr B
draccess at askdoctoraccess.com


Jen said:
I have a list box containing employee names; I want to toggle it back and
forth between showing all employees and showing just current employees.

Employees are stored in an Employees Table with FName, LName, Init (a
shortened max 8-character version of their name) and a yes/no field to
determine whether they're active or not.

The AllEmployeesFullName query pulls FName and LName into a calculated
FullName field.

The ActiveEmployees query pulls FName and LName into a calculated FullName
field, plus selects only employees whose status is active.

My list box is based on the ActiveEmployees query with the following
RowSource:
SELECT ActiveEmployees.Init, ActiveEmployees.FullName FROM ActiveEmployees
ORDER BY ActiveEmployees.FullName

The form also contains a command button with the following code:
Private Sub Command10_Click()
If Me!lstEmployees.RowSource = "SELECT ActiveEmployees.Init,
ActiveEmployees.FullName FROM ActiveEmployees ORDER BY
ActiveEmployees.FullName;" Then
Me!lstEmployees.RowSource = "SELECT AllEmployeesFullName.Init,
AllEmployeesFullName.FullName FROM AllEmployeesFullName ORDER BY
AllEmployeesFullName.FullName;"
ElseIf Me!lstEmployees.RowSource = "SELECT AllEmployeesFullName.Init,
AllEmployeesFullName.FullName FROM AllEmployeesFullName ORDER BY
AllEmployeesFullName.FullName;" Then
Me!lstEmployees.RowSource = "SELECT ActiveEmployees.Init,
ActiveEmployees.FullName FROM ActiveEmployees ORDER BY
ActiveEmployees.FullName;"
End If
Me!lstEmployees.Requery
End Sub

This was a simple attempt to get the toggling to work but when I click the
button, nothing happens. I have tried both the code strings as the row
source, and they both do what they're supposed to do (i.e., list either all
employees or only active employees), but the button won't toggle between them.

What am I missing here?

Thanks in advance,
Jen
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

Creating a Search using a combo box and then creating a report. 4
Option Button to Restrict Qry 7
multi value field in where clause 1
Full Name in reverse order in the combo box when browsing 3
How do I create a small array from a larger one? 3
Open a report to specific data 2
combo box source 4
Populate a listbox from a Combobox 5

Top