Getting an "All" choice to return all records in a list box

G

Guest

I have a form where users can enter three variables using three combo boxes
and the resulting records are shown in list box on the form. It works fine.
However, i would like
to be able to choose "All" in one, two or all three combos. It is not a
problem for me to add the "All" in each combo.

The general declarations for the form are:
Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT ProjectID, Projectsymbol, Title, StatusDate,
Budget " & _
"FROM qryParam3 WHERE Region = '"
Private Const strSQL2 = "' AND Status = '"
Private Const strSQL3 = "' AND SubjectName = '"
Private Const strSQL4 = "' Order BY StatusDate DESC;"
Private strSQL As String

The FillList code is:

rivate Sub FillList()

strSQL = strSQL1 & Me!cboRegion.Value & _
strSQL2 & Me!cboStatus.Value = "" & strSQL3 & Me!cboSubject.Value &
strSQL4


Me!lstProjects.RowSource = strSQL
Me!lstProjects.Requery
Me!lbllist.Caption = "Projects from " & _
Me!cboRegion.Value & " that are " & _
Me!cboStatus.Value & " and where the subject is " & _
Me!cboSubject.Value
If Me!lstProjects.ListCount = 0 Then
Me!lbllist.Caption = "No " & Me!lbllist.Caption
Me!lblLineProjects.Caption = strMsg4
End If
End Sub

An example for for the AfterUpdate for one of the combos is:
Private Sub cboRegion_AfterUpdate()
If Me!cboStatus.Value <> "" Then
Call FillList
Else
Me!lbllist.Caption = strMsg1
End If
With Me!lstLineProjects
.RowSource = ""
.Requery
End With
Me!lblLineProjects.Caption = strMsg5
End Sub

What do I have to change to get an "All" choice in one,two or three combos
to work?
Thanks in advance.

Niels
 
S

SteveS

NielsE said:
I have a form where users can enter three variables using three combo boxes
and the resulting records are shown in list box on the form. It works fine.
However, i would like
to be able to choose "All" in one, two or all three combos. It is not a
problem for me to add the "All" in each combo.

The general declarations for the form are:
Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT ProjectID, Projectsymbol, Title, StatusDate,
Budget " & _
"FROM qryParam3 WHERE Region = '"
Private Const strSQL2 = "' AND Status = '"
Private Const strSQL3 = "' AND SubjectName = '"
Private Const strSQL4 = "' Order BY StatusDate DESC;"
Private strSQL As String

The FillList code is:

rivate Sub FillList()

strSQL = strSQL1 & Me!cboRegion.Value & _
strSQL2 & Me!cboStatus.Value = "" & strSQL3 & Me!cboSubject.Value &
strSQL4


Me!lstProjects.RowSource = strSQL
Me!lstProjects.Requery
Me!lbllist.Caption = "Projects from " & _
Me!cboRegion.Value & " that are " & _
Me!cboStatus.Value & " and where the subject is " & _
Me!cboSubject.Value
If Me!lstProjects.ListCount = 0 Then
Me!lbllist.Caption = "No " & Me!lbllist.Caption
Me!lblLineProjects.Caption = strMsg4
End If
End Sub

An example for for the AfterUpdate for one of the combos is:
Private Sub cboRegion_AfterUpdate()
If Me!cboStatus.Value <> "" Then
Call FillList
Else
Me!lbllist.Caption = strMsg1
End If
With Me!lstLineProjects
.RowSource = ""
.Requery
End With
Me!lblLineProjects.Caption = strMsg5
End Sub

What do I have to change to get an "All" choice in one,two or three combos
to work?
Thanks in advance.

Niels
Click to expand...


What is the RowSource (SQL) of each of the three combo boxes?

Me!cboRegion
Me!cboStatus
Me!cboSubject
 
G

Guest

The row souces are the following:

SELECT tblREGION.REGION FROM tblREGION ORDER BY [REGION];
SELECT tblSTATUS.STATUS FROM tblSTATUS ORDER BY [STATUS];
SELECT tblSUBJECT.SubjectName FROM tblSUBJECT ORDER BY [SubjectName];

REGION, STATUS and SubjectName are also primary key fields in their
respective tables.

Niels
 
S

SteveS

NielsE said:
The row souces are the following:

SELECT tblREGION.REGION FROM tblREGION ORDER BY [REGION];
SELECT tblSTATUS.STATUS FROM tblSTATUS ORDER BY [STATUS];
SELECT tblSUBJECT.SubjectName FROM tblSUBJECT ORDER BY [SubjectName];

REGION, STATUS and SubjectName are also primary key fields in their
respective tables.

Niels
Click to expand...

Hi Niels,

Here is one way to do what you want.


Change the row source of the 3 combo boxes to:

(should be one line for each combo box)

SELECT tblREGION.REGION FROM tblREGION Union SELECT "**ALL**" as Bogus from
tblREGION ORDER BY tblREGION.REGION;

SELECT tblSTATUS.Status FROM tblSTATUS Union SELECT "**ALL**" as Bogus from
tblstatus ORDER BY tblSTATUS.Status;

SELECT tblSUBJECT.SubjectName FROM tblSUBJECT Union SELECT "**ALL**" as Bogus
from tblSUBJECT ORDER BY tblSUBJECT.SubjectName;

I would also set the "Limit to List" to TRUE for each of the 3 combo boxes.

Next change the code for FillList() to:
(watch for line wrap)

'*** beg code ***
Private Sub FillList()

Dim strSQL As String
Dim strWhere As String
Dim strCaption As String

strCaption = "Projects from "
strWhere = ""
strSQL = "SELECT ProjectID, Projectsymbol, Title, StatusDate, Budget FROM
qryParam3 "

'create the where string
If Nz(Me.cboRegion, "**ALL**") <> "**ALL**" Then
strWhere = "Region = '" & Me.cboRegion & "' "
End If

If Nz(Me.cboStatus, "**ALL**") <> "**ALL**" Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "Status = '" & Me.cboStatus & "' "
End If

If Nz(Me.cboSubject, "**ALL**") <> "**ALL**" Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "SubjectName = '" & Me.cboSubject & "' "
End If

' Add WHERE if any of the combo boxes are not **ALL**
If Len(strWhere) > 0 Then
strWhere = " Where " & strWhere
End If

' now create the SQL string
strSQL = strSQL & strWhere & " Order BY StatusDate DESC;"


Me!lstProjects.RowSource = strSQL
Me!lstProjects.Requery

' create the caption string "on the fly"
If Nz(Me.cboRegion, "**ALL**") = "**ALL**" Then
strCaption = strCaption & " All Regions that are "
Else
strCaption = strCaption & Me!cboRegion & " that are "
End If

If Nz(Me.cboStatus, "**ALL**") = "**ALL**" Then
strCaption = strCaption & " All Statuses and where the subject is "
Else
strCaption = strCaption & Me!cboStatus & " and where the subject is "
End If

If Nz(Me.cboSubject, "**ALL**") = "**ALL**" Then
strCaption = strCaption & " All Subjects"
Else
strCaption = strCaption & Me!cboSubject
End If

Me!lbllist.Caption = strCaption
'Me!lbllist.Caption = "Projects from " & _
Me!cboRegion & " that are " & _
Me!cboStatus & " and where the subject is " & _
Me!cboSubject


If Me!lstProjects.ListCount = 0 Then
Me!lbllist.Caption = "No " & Me!lbllist.Caption
Me!lblLineProjects.Caption = strMsg4
End If
End Sub
'*** end code ***

The NZ() function changes a NULL to "**ALL**" so if someone deletes the entry
in a combo box, the code will always see "**ALL**" instead of NULL.


I'm not sure what to recommend for the combo box "AfterUpdate" event code.
Since nulls are converted to "**ALL**", the line

Me!lbllist.Caption = strMsg1

will never run.


HTH
 
G

Guest

Thanks Steve. I got rid of the lable business as well as the drill down list.
It works great.
Niels

SteveS said:
NielsE said:
The row souces are the following:

SELECT tblREGION.REGION FROM tblREGION ORDER BY [REGION];
SELECT tblSTATUS.STATUS FROM tblSTATUS ORDER BY [STATUS];
SELECT tblSUBJECT.SubjectName FROM tblSUBJECT ORDER BY [SubjectName];

REGION, STATUS and SubjectName are also primary key fields in their
respective tables.

Niels
Click to expand...

Hi Niels,

Here is one way to do what you want.


Change the row source of the 3 combo boxes to:

(should be one line for each combo box)

SELECT tblREGION.REGION FROM tblREGION Union SELECT "**ALL**" as Bogus from
tblREGION ORDER BY tblREGION.REGION;

SELECT tblSTATUS.Status FROM tblSTATUS Union SELECT "**ALL**" as Bogus from
tblstatus ORDER BY tblSTATUS.Status;

SELECT tblSUBJECT.SubjectName FROM tblSUBJECT Union SELECT "**ALL**" as Bogus
from tblSUBJECT ORDER BY tblSUBJECT.SubjectName;

I would also set the "Limit to List" to TRUE for each of the 3 combo boxes.

Next change the code for FillList() to:
(watch for line wrap)

'*** beg code ***
Private Sub FillList()

Dim strSQL As String
Dim strWhere As String
Dim strCaption As String

strCaption = "Projects from "
strWhere = ""
strSQL = "SELECT ProjectID, Projectsymbol, Title, StatusDate, Budget FROM
qryParam3 "

'create the where string
If Nz(Me.cboRegion, "**ALL**") <> "**ALL**" Then
strWhere = "Region = '" & Me.cboRegion & "' "
End If

If Nz(Me.cboStatus, "**ALL**") <> "**ALL**" Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "Status = '" & Me.cboStatus & "' "
End If

If Nz(Me.cboSubject, "**ALL**") <> "**ALL**" Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "SubjectName = '" & Me.cboSubject & "' "
End If

' Add WHERE if any of the combo boxes are not **ALL**
If Len(strWhere) > 0 Then
strWhere = " Where " & strWhere
End If

' now create the SQL string
strSQL = strSQL & strWhere & " Order BY StatusDate DESC;"


Me!lstProjects.RowSource = strSQL
Me!lstProjects.Requery

' create the caption string "on the fly"
If Nz(Me.cboRegion, "**ALL**") = "**ALL**" Then
strCaption = strCaption & " All Regions that are "
Else
strCaption = strCaption & Me!cboRegion & " that are "
End If

If Nz(Me.cboStatus, "**ALL**") = "**ALL**" Then
strCaption = strCaption & " All Statuses and where the subject is "
Else
strCaption = strCaption & Me!cboStatus & " and where the subject is "
End If

If Nz(Me.cboSubject, "**ALL**") = "**ALL**" Then
strCaption = strCaption & " All Subjects"
Else
strCaption = strCaption & Me!cboSubject
End If

Me!lbllist.Caption = strCaption
'Me!lbllist.Caption = "Projects from " & _
Me!cboRegion & " that are " & _
Me!cboStatus & " and where the subject is " & _
Me!cboSubject


If Me!lstProjects.ListCount = 0 Then
Me!lbllist.Caption = "No " & Me!lbllist.Caption
Me!lblLineProjects.Caption = strMsg4
End If
End Sub
'*** end code ***

The NZ() function changes a NULL to "**ALL**" so if someone deletes the entry
in a combo box, the code will always see "**ALL**" instead of NULL.


I'm not sure what to recommend for the combo box "AfterUpdate" event code.
Since nulls are converted to "**ALL**", the line

Me!lbllist.Caption = strMsg1

will never run.


HTH
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

Access 2007, Passing parameters over to a query 4
Filling a list box from a Combo Box Query 1
Using MultiSelect List Box to pull records of selected IDs from a report 4
Adding <All> as option in a multiselect list box 16
Filter On a list box, But being able to view all records in 2nd li 13

Top