Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms / February 2008

Tip: Looking for answers? Try searching our database.

Displaying all data when using a combo box

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jon M. - 29 Feb 2008 17:34 GMT
Okay I have a form, based on one table.  My form has two combo boxes on it,
one searches for an employee by building, the other by department.  The
results are displayed in a subform beneath the combo boxes.  This seems to
work fine.  However until you enter a selection into each combo box no
records are displayed.  How can I set each combo box to work independently
and/or together to get the desired results?  Is there a way I could add
something like an "all" field to both?  I would also like the subform to
display all records when the form is open until a selection is made.  As
always any help is greatly appreciated!
Signature

Jon M.

Klatuu - 29 Feb 2008 19:58 GMT
Easy enough to do.
First, take any criteria out of your form's record source query so all rows
will be presented.

Now here is some sample code that will cause it to filter like you want it.

Private Function SetFilters()
Dim strFilter As String 'Used by Form Filtering procedures
 
   With Me
   
'Build The Filter String
       If .cboPriority <> "(All)" Then
           strFilter = "[InitPriority] = " & .cboPriority
       End If
       
       If .cboOrigDate <> "(All)" Then
           strFilter = AddAnd(strFilter)
           strFilter = strFilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
               .cboOrigDate & """"
       End If
       
       If .cboCurrDate <> "(All)" Then
           strFilter = AddAnd(strFilter)
           strFilter = strFilter & "[CurrentReleaseTarget] = " & .cboCurrDate
       End If
       
       If .cboInitStatus <> 0 Then
           strFilter = AddAnd(strFilter)
           strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
       End If
       
       If .cboInitType <> 0 Then
           strFilter = AddAnd(strFilter)
           strFilter = strFilter & "[InitType] = " & .cboInitType
       End If
               
       If Not IsNull(.txtDescrSearch) Then
           strFilter = AddAnd(strFilter)
           strFilter = strFilter & "[InitShortDescr] Like ""*" &
Me.txtDescrSearch & "*"""
       End If
       
       .subInitiative.Form.Filter = strFilter
       .subInitiative.Form.FilterOn = True
       
   End With 'Me

******************
The AddAnd code that puts the And in the fitler string:

Private Function AddAnd(strFilterString) As String
  On Error GoTo AddAnd_Error

   If Len(strFilterString) > 0 Then
       AddAnd = strFilterString & " AND "
   Else
       AddAnd = strFilterString
   End If

AddAnd_Exit:

  Exit Function
  On Error GoTo 0

AddAnd_Error:

   MsgBox "Error " & Err.Number & " (" & Err.Description & _
       ") in procedure AddAnd of VBA Document Form_frmStartForm"
   GoTo AddAnd_Exit

End Function

***********
Now, to get the (All) in your combo box.

This example uses only one column when you are using a text value to do the
lookup:
SELECT "(All)" As Dummy FROM dbo_Initiative UNION SELECT DISTINCT
InitPriority FROM dbo_Initiative WHERE InitPriority IS NOT NULL;

This example is where you are using a numeric key for the lookup, but it is
hidden and you show a text description to present to the user:

SELECT 0 As ID, "(All)" As Dummy FROM dbo_Initiative UNION SELECT StatusID,
StatusDescr FROM dbo_ProjectStatus;

Then for each combo, put the following directly in the After Update property
in the properties dialog:
=SetFilters()

Now, I also have a command button called Clear Filters that remove the
filtering.

Private Sub cmdClearFilters_Click()
   With Me
       .cboPriority = "(All)"
       .cboOrigDate = "(All)"
       .cboCurrDate = "(All)"
       .cboInitStatus = 0
       .cboInitType = 0
       .cboCenter = DLookup("[DefaultCenter]", "tblClientVersion")
       .txtDescrSearch = Null
       .subInitiative.Form.FilterOn = False
       .subInitiative.Form.Requery
   End With

End Sub

And last, in the Form Activate event

   Call cmdClearFilters_Click

That is so the (All) values will show and no filtering will be done when the
form opens.

Signature

Dave Hargis, Microsoft Access MVP

> Okay I have a form, based on one table.  My form has two combo boxes on it,
> one searches for an employee by building, the other by department.  The
[quoted text clipped - 5 lines]
> display all records when the form is open until a selection is made.  As
> always any help is greatly appreciated!
Jon M. - 29 Feb 2008 20:07 GMT
You sir are good, thank you very much!
Signature

Jon M.

> Easy enough to do.
> First, take any criteria out of your form's record source query so all rows
[quoted text clipped - 122 lines]
> > display all records when the form is open until a selection is made.  As
> > always any help is greatly appreciated!
Klatuu - 29 Feb 2008 20:10 GMT
Thanks, Jon.
If you have any problems getting to work for you, please post back.
Signature

Dave Hargis, Microsoft Access MVP

> You sir are good, thank you very much!
>
[quoted text clipped - 124 lines]
> > > display all records when the form is open until a selection is made.  As
> > > always any help is greatly appreciated!
Jon M. - 29 Feb 2008 21:29 GMT
I do have a question on this, I am up to adding my (All) to my two combo
boxes, I am using a text value in both boxes so I am using your fisrt
example.  Is the example code to be entered?  Or is it to be entered in the
properties of each box?  If it is code what should I use as my begining?
Private Sub? Private Function?  As you can tell I'm a bit of a novice with
the coding.
Signature

Jon M.

> Thanks, Jon.
> If you have any problems getting to work for you, please post back.
[quoted text clipped - 127 lines]
> > > > display all records when the form is open until a selection is made.  As
> > > > always any help is greatly appreciated!
Klatuu - 29 Feb 2008 21:31 GMT
The code you are talking about is a Union Query.  It goes in the row source
property of the combo box.
Signature

Dave Hargis, Microsoft Access MVP

> I do have a question on this, I am up to adding my (All) to my two combo
> boxes, I am using a text value in both boxes so I am using your fisrt
[quoted text clipped - 134 lines]
> > > > > display all records when the form is open until a selection is made.  As
> > > > > always any help is greatly appreciated!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.