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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|