Filtering combo box on subform

[dalmatian]

Hi

This is my first post on english language, so if i made some mistakes, sorry.
I have a main form with few command buttons and subform-hole.
Each command button open in subform-hole acctualy same subform with different filter.
For filtering subforms I used code:

Private Sub Command123_Click()
Me.subEmployeesHole.SourceObject = "subDepartment123"
With Me.subEmployeesHole.Form
.Filter = "Department = '123'"
.FilterOn = True
End With
End Sub

On that way when I press button "123" I have on subform only employes of 123 department (In MainTable which is source for all subforms exist column with departments name). But, on each subform I have a combo box (Search_Surname) for quick finding employees on just selected department. Row source for combo box is like:

SELECT MainTable.Name_and_FamilyName
FROM MainTable
WHERE (((MainTable.Department)="123"))
ORDER BY MainTable.Name_and_FamilyName;.
And AfterUpdate Event Procedure on Combo box is :

Private Sub Search_Surname_AfterUpdate()
Call SearchSurnameF
End Sub

SearchSurnameF is:

Function SearchSurnameF()
On Error GoTo SearchSurnameF_Err
DoCmd.SearchForRecord , "", acFirst, "[Name_and_FamilyName] = " & "'" & Screen.ActiveControl & "'"
SearchSurnameF_Exit:
Exit Function
SearchSurnameF_Err:
MsgBox Error$
Resume SearchSurnameF_Exit
End Function


Problem is that I don't know how to reduce number of subforms. In present
moment I have 15 exactly the same subforms which is difference only in Search_surname combo box, and number of subforms still growing.
Is there any way to put some additional code on command buttons on main form and eliminate all but one subforms?
I'm not very familiar with VBA, so anyone can help, please?

 

[CBrighton]

Why do you have multiple subforms, is it a subform for each department?

If so then instead of that what you want to do is have 1 subform with the rowsource referencing a textbox on the main form:

Private Sub Command123_Click()
Me.subEmployeesHole.SourceObject = "[B]subDepartmentDetails[/B]"
With Me.subEmployeesHole.Form
.Filter = "Department = [B]'" & txtSearchBox & "'"[/B]
.FilterOn = True
End With
End Sub

^Changes to your code highlighted in bold, single subform required so I changed the subform name & filter refers to a textbox where the user types in the department. The textbox would work better as a combobox which has the rowsource set to something like:

SELECT DISTINCT Department FROM tblTableName

That way the user could select from a list of all existing departments.

 

[dalmatian]

Thanks for replay.
When I started to work on my database, I mainly used tables only (like advanced spreadsheet), and after few years I finally decide to start using queries and forms.
I created query from three tables, and based on that query created one form with 30 or more textboxes, 3 comboboxes, two subforms (from additional two tables) and unbound object frame for displaying pictures from hard drive.
This form covered all departments, but I didn't have a way to quickly jump on particular record (employee), so, after searching on net I found solution through combo box described in previous post (Search_Surname) and all code behind it (except: sql query is simpliest than now).
After that I trying to reduced time for scrolling to particular employee, and I copied the form as many times as there are departments and on each form I applied different query for filtering employees on that department. Also I applied same query on combo box (Search_surname).
Finally, I'm not satisfied with many forms which are identical in design but based on different queries, I created MainForm (from previous post) and command buttons for displaying (in subform-hole) previous forms (now subforms). From subforms I remove different queries because queries is now replaced by filter behind command buttons, and only problem which is not adapted is combo box (Search_Surname).
If I remove this combobox from subforms, everything is ok, I need only one subform, departments filtering works fine through command buttons, but... In that case I don't have a possibility to quickly jump through employees.
Or if I remove just different queries from comboboxes, I also need only one subform but combobox list will always show all employees (which I don't want).

 

[CBrighton]

You can tweak the above code to update the comboboxes rowsource to ensure it is always showing whichever department was searched for last too:

Private Sub Command123_Click()
Me.subEmployeesHole.SourceObject = "subDepartmentDetails"
With Me.subEmployeesHole.Form
  .Filter = "Department = '" & txtSearchBox & "'"
  .FilterOn = True
End With
[B]With Me.subEmployeesHole.form.cboEmployeeSearch[/B]
[B]  .RowSource = .RowSource & " WHERE Department = '" & txtSearchBox & "'"[/B]
[B]End With
[/B]End Sub

The above code assumes the row source is SQL rather than a query name and it assumes it has no WHERE clause already. If it has a WHERE clause then replace the WHERE in the above code with AND.

 

[dalmatian]

I tried, but without success.
All the time I receiving different error messages, and finally I received a message "Run-time error '424' Object required" and point me at:
.RowSource = .RowSource & " WHERE Department = '" & txtSearchBox & "'"
I'm not really familiar with VBA. All code what I included in my database I found on net and just adapted for my db. Any suggestions for next step?

 

[CBrighton]

Try tweaking the line above.

Currently you have "Me.subEmployeesHole.form.cboEmployeeSearch" which I copied from your earlier code.

I would usually write that like this: "me!subEmployeesHole.Form!cboEmployeeSearch".

Don't know if it will make a difference, but give it a go (it's replacing some periods with exclamation marks, all except the one before "Form").

 

[dalmatian]

Thanks for replay.
No success. I added the example of my database (rather impoverished).
I left only two separate departments (subforms), and third (subform) which covered all departments. Database is in 2000-2003 file format. I work with access 2010, but still uses the old file format.

View attachment Example1.zip

 

[CBrighton]

Zip file "invalid or corrupted" according to Windows XP.

 

[dalmatian]

Again, I compressed and uploaded. I also work with WinXP. I replaced previous file with new one.

 

[dalmatian]

I tried various combinations and by accident I found one that works.
Me.subEmployeesHole.Form.Search_Surname.RowSource = " SELECT Company.Name_Surname, Company.Department FROM Company WHERE (((Company.Department)=""Department1"")) ORDER BY Company.Name_Surname;"

This expression is exactly what I wanted.

Thanks for help.