A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Use Combo Box in forms to filter Records in a subform



 
 
Thread Tools Display Modes
  #1  
Old May 25th, 2004, 07:26 PM
Rod Burchby
external usenet poster
 
Posts: n/a
Default Use Combo Box in forms to filter Records in a subform

Is is possible to have combo boxes in a form that are used as "selection criteria" for a list of records that appear in a subform (in a datasheet view). For example: In the sub form there is list of records in a datasheet view. Once of the fields in each record is "dept". If I have a "dept" combo box in the main form and select a value from the combo box, it then would only list the records in the subform with the specified department. I would like to have a couple combo boxes in the main form and any combination of use of these combo boxes would "filter" the list of records in the subform.

Is this possible???
Thanks,
Rod
  #2  
Old May 25th, 2004, 08:19 PM
Kevin Sprinkel
external usenet poster
 
Posts: n/a
Default Use Combo Box in forms to filter Records in a subform

I've never done this quite the way you describe, although
in concept it's similar to presenting a form to filter a
query or report. The general strategy is to use the
selections in the combo box(es) to build an SQL string,
and then set the subform's row source to this string.

The way I build such a string is to create a general
procedure that loops through each form control, adding
another piece of the SQL string with each relevant control
(you'll want to ignore labels, and other controls that
aren't to be used to filter the records. Then at the end
of the procedure, set the Record Source property of the
subform, and requery. Call this procedure in the
AfterUpdate event of each filtering control.

The following snippet loops through a report-filtering
form, writing the SQL string to a hidden form control
named txtFilterString:

Me!txtFilterString = Null

For Each ctl In Me.Controls
' Use the Controls collection of the current form
If ctl.ControlType = acComboBox

' Here I'd named each control the name of the
' field it represents plus the "cbo" prefix,
' conveniently generating the latter from the
' former using LTrim. You could also build it
' explicitly for each control.

If (Nz(ctl.Value) 0 And Nz(ctl.Value) "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) - 3)) _
& "]=" & ctl.Value & " AND "
End If
End If

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, _
Len(Me!txtFilterString) - 5)


HTH
Kevin Sprinkel

-----Original Message-----
Is is possible to have combo boxes in a form that are

used as "selection criteria" for a list of records that
appear in a subform (in a datasheet view). For example:
In the sub form there is list of records in a datasheet
view. Once of the fields in each record is "dept". If I
have a "dept" combo box in the main form and select a
value from the combo box, it then would only list the
records in the subform with the specified department. I
would like to have a couple combo boxes in the main form
and any combination of use of these combo boxes
would "filter" the list of records in the subform.

Is this possible???
Thanks,
Rod
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.