FindRecord ApplyFilter macro query combo box ?? 1

[wvmbark]

I have a form 'frmEmpDataEntry' that's used to input and update employee information.

On the form there are 6 command buttons that use the ApplyFilter macro to filter by employee type [EmpType] (5 employee types and ShowAll).

I am also using an unbound combo box [EmpSelect] to select a particular employee. It's row source is a query 'qryEmployees' that returns Employees' names. On the After Update event is a macro 'FindEmployee' that uses the GoToControl (the control name is [EmpName]) and the FindRecord (find what is =[EmpSelect]) actions.

Both work great except when a filter is applied, the EmpSelect still shows all employees. How can I set this up so that when a filter is applied, only employees within the the filtered type shows up??

Thanks!!

Mike

 

[TheAceMan1]

How are ya wvmbark . . .

You could do this with a [blue]query as the recordsource[/blue] for the form and [blue]criteria supplied by two comboboxes[/blue] (one for type, one for employee name). For get the filter mess . . . I never use filters for just the reason you've posted.

Post the [blue]recordsource[/blue] of the form (if its a table, post the table fields info), and rowsource of employee name combo.

See Ya! . . . . . .

 

[wvmbark]

Hey AceMan!

I guess I could go the route of 2 combo boxes... I just liked the idea of having the command buttons on the form where the user could just click to get that group of employees. You said 'post the recordsource of the form'. Is this what you meant?

SELECT Employees.*
FROM Employees
ORDER BY Employees.LastName, Employees.FirstName;

It's a query called qryEmpInfoDataEntry. There's about 50 fields, but the ones that may or may not be relavent are [GEMS ID] (which is the employee number and is PK), [EmpType] (which could be Analyst, Admin, Controller, Supervisor, Manager, etc.), and [LastName] and [FirstName].

This is also the recordsource for the combo box. It has the macro 'FindEmployee' described above in the AfterUpdate event.

Let me know if this is what you were asking for, or if additional info is needed.

Thanks for your assistance!!!

 

[TheAceMan1]

wvmbark . . .

Info is just fine. Started the code which brought up an operational question.

Do you want your [blue]buttons and the combobox to work independently or together?[/blue]

That is, if you select the Admin button you view all admin's, and if you select an employee from the combo you view that employee . . . regardless of type.

See Ya! . . . . . .

 

[wvmbark]

AceMan-

I want them to work together. They work independently now, which is the problem.

Currently, if I select the Admin button I get Admin records... but when I use the drop-down to select a different Admin employee, I get a list of all employees.

What I'm hoping for is the combobox values to be based on the filter that is selected. So if the Admin button is selected, only the Admin employees are in the combo list.

Mike

 

[TheAceMan1]

wvmbark . . .

Havn't forgotton ya . . . christmas shopping makin me crazy!

I'll complete this later today!

. . . and BTW . . . Merry Christmas!

See Ya! . . . . . .

 

[TheAceMan1]

wvmbark . . .

Almost finished but operations still bothering me.

Would it not be better if the names that appear in the combobox were synchronized with the button selection?

That is . . . if you select the button for [blue]Admin[/blue], only admins appear in the combobox.

Select [blue]All[/blue] and they all appear . . .

Your thoughts? . . .

See Ya! . . . . . .

 

[TheAceMan1]

Never mind . . . you've already stated you desire this.

See Ya! . . . . . .

 

[TheAceMan1]

OK wvmbark . . .

Filtering of [blue]employee types[/blue] is done directly thru SQL (changing the [blue]RecordSource[/blue] of the form). The [blue]RowSource[/blue] of the combobox is handled the same way, sychcronizing names in the list to the type selected. On selection, the [blue]combobox performs a lookup[/blue] and brings the record in view. This allows you to view the employee and still be able to parse thru the rest of the employees in the current Type (using navigation buttons). So . . . lets rock-n-roll . . .
[ol][li]Open frmEmpDataEntry in [blue]design view[/blue]. The [blue]RecordSource[/blue] property of the form should be:

[blue]SELECT Employees.* FROM Employees ORDER BY [LastName], [FirstName];[/blue]

[/li]
[li]For the [blue]RowSource[/blue] property of the combobox EmpSelect, copy/paste the following:

[blue]SELECT [GemsID], [LastName] & " " & [firstname] AS FullName FROM Employees ORDER BY [LastName], [FirstName];[/blue]

Set the following properties for the combobox:
[ol a][li]Column Count [blue]2[/blue][/li]
[li]Column Widths [blue]0";2"[/blue][/li]
[li]List Width [blue]2"[/blue][/li]
[li]Bound Column [blue]1[/blue][/li][/ol][/li]
[li]In the [blue]code module[/blue] for the form, copy/paste the following routine:

[blue]Public Sub EmpFilter(Optional Cri)
   Dim frmSQL As String, cbxSQL As String, Criteria As String
   
   frmSQL = "SELECT Employees.* " & _
            "FROM Employees "
   cbxSQL = "SELECT GemsID, " & _
                   "[lastName] & ' ' & [firstname] AS FullName " & _
                   "FROM Employees "

   If Not IsMissing(Cri) Then
      If Screen.ActiveControl.ControlType = acCommandButton Then
         Criteria = "Where ([EmpType] = '" & Cri & "') "
      Else
         Criteria = "[GemsID] = " & Cri
         Me.Recordset.FindFirst Criteria
         Exit Sub
      End If
      
   End If
   
   frmSQL = frmSQL & Criteria & "ORDER BY LastName, FirstName; "
   Me.RecordSource = frmSQL
   
   cbxSQL = cbxSQL & Criteria & "ORDER BY LastName, FirstName;"
   Me!EmpSelect = Null
   Me!EmpSelect.RowSource = cbxSQL

End Sub[/blue]

This is the main routine, called by your type buttons and combobox. Basically it substitutes appropriate criteria in the SQLs of the [blue]RecordSource[/blue] of the form & [blue]RowSource[/blue] of the combobox.[/li]
[li]In the click event of the [blue]type buttons[/blue] except 'All', copy/paste the following line of code and [blue]substitute the proper type for TypeName[/blue]:

[blue]   '[green][b]TypeName[/b] = Admin for Admin Command button[/green]
   Call EmpFilters("[purple][b]TypeName[/b][/purple]")[/blue]

[/li]
[li]For the [blue]All[/blue] command button its simply:

[blue]   Call EmpFilters[/blue]

[/li]
[li]In the [blue]AfterUpdate[/blue] event of the combobox, copy/paste the following:

[blue]      Call EmpFilters(Me!EmpSelect)[/blue]

[/li][/ol]
[purple]Thats it! . . . Let me know if youhave any problems . . .[/purple]

BTW . . . [blue]Happy Holidays![/blue]

See Ya! . . . . . .

 

[TheAceMan1]

Woops . . . noticed an error! . . . for the main routine:

[blue][purple]Change:[/purple]
   Public Sub EmpFilter(Optional Cri)
[purple]To:[/purple]
   Public Sub EmpFilter[purple][b]s[/b][/purple](Optional Cri)[/blue]

See Ya! . . . . . .