Using one form to view different filters of the same table

[usmcgrunt]

I am trying to use one form to view two different categories of the same information. In database speak I mean that all of the information is coming from the same table but I use a field in the table to classify the record as either active or inactive (yes or no control).

Here are the categories:
Lessons that are active
Lessons that are inactive

These two categories can be further filtered by all lesson that are inactive or active or a subset of lessons within a specific division that are active or inactive.



When the user opens the form he/she is presented with a dialog form with a combobox and an option group control. The user select the appropriate division from the combobox and then selects either Active or Inactive from the option group control. I have added an <All> row in the combobox so that the user has the option to view all the records that are active or inactive. Now when the user clicks the go button on the dialog form, I want the main form to filter the records that it displays based on the combobox and option group control from the previous form. In addition, I have a listbox on the main form that displays the Lesson Number and Lesson Title of all the records in the filtered division. The user can quickly select a lesson from the listbox to view detailed information about the lesson.

I can get this to work perfectly if all I have on the dialog form is a combobox to select a division. However, when I add the option group control all the filters fail.

Details of the fail's:
1. Listbox is empty but details of the first record in the table display in the fields on the form.
2. Listbox is limited to the filters from the dialog form but the first record in the table, which is not part of the filter, displays in the rest of the form fields.
3. All records display when inactive is selected in the option group control.

I am trying to find a way to do this without having to create another form and query with different filter values.

Here is my current code by object that relates to this issue. Again this code works but I want to expand on it to accomplish what I stated above.

1. frmDivSelect: Dialog form that opens when the user opens the mainform

 Private Sub gotoLessonCard_Click()
 
     Dim strForm As String
     Dim strWhere As String
 
     strForm = "frmLessonCard"
     strWhere = "[qryLessonCardDataFilter]![LDivision] = " & Me!cboDivSelect & ""
 
     If Me.cboDivSelect.Value = 0 Then
          Me.cboDivSelect.Value = Null
          DoCmd.OpenForm strForm, acNormal
          Me.Visible = False
     Else
          DoCmd.OpenForm, strForm, acNormal, , strWhere
          Me.Visible = False
     End If

2. frmLessonCard: Mainform receiving the filter criteria from frmDivSelect; code that is behind the listbox.

 Private Sub lblLessonList_AfterUpdate()
 
     DoCmd.SearchForRecord , "", acFirst, "[LNumber] = " & "'" & Screen.ActiveControl & "'"

3. qryLessonCardDataFilter: The underlying record source for frmLessonCard.

Field: Inactive
Table: tblLCData
Criteria: 0
or: 0
 
Field: LDivision
Table: tblLCData
Criteria: [Forms]![frmDivSelect]![cboDivSelect]
 
Field: [Forms]![frmDivSelect]![cboDivSelect]
Show: unchecked
or: Is Null

Yes, this is along post, but if you have made it to this sentence I hope that you will be kind enough to add some water to my knowledge bucket and not throw another log on my fire.

Thanks, Sean

"We, the willing, led by the unknowing, are doing the impossible for the ungrateful. We have now done so much for so long with so little, we are now capable of doing anything with nothing." - Unknown

[alansidman]

Does this tutorial help you?

http://www.datapigtechnologies.com/f...earchform.html

Alan