Help filtering form based on the data from another table

[gojets1721]

I've got a Complaints table and an Employees table. Both tables share a 'ComplaintNumber' field.

In the Complaints form, I was hoping to use a input box to allow the user to search an employees name (from the Employees table) and it filters down to matching ComplaintNumber(s) based on that employee's name.

Here's what I've got so far. The input box opens up but doesn't do anything. No error; it just doesn't filter anything.

Private Sub btnSearchEmployee_Click()
On Error GoTo btnSearchEmployee_Click_Err

    Dim S As String
    Dim rst As DAO.Recordset
   
    S = InputBox("Enter Employee Name", "Employee Name Search")
    If S = "" Then Exit Sub
   
    strSQL = "Select * from tblEmployees where EmployeeName LIKE ""*" & S & "*"""
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If rst.EOF = False Then
    Me.Recordset.FindFirst "ComplaintNumber = " & rst!ComplaintNumber
    End If
   
btnSearchEmployee_Click_Exit:
    Exit Sub

btnSearchEmployee_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
   
    Resume btnSearchEmployee_Click_Exit
 
End Sub

 

[theDBguy]

Lots of ways to do this. I might try using DLookup() to find the complaint number and just filter the form with it.

 

[arnelgp]

you try this:

...
...
strSQL = "Select * from tblEmployees where EmployeeName LIKE ""*" & S & "*"""
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.EOF = False Then
With Me.RecordsetClone
.FindFirst "ComplaintNumber = " & rst!ComplaintNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

 

[gojets1721]

you try this:

...
...
strSQL = "Select * from tblEmployees where EmployeeName LIKE ""*" & S & "*"""
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.EOF = False Then
With Me.RecordsetClone
.FindFirst "ComplaintNumber = " & rst!ComplaintNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

I gave that shot. Still nothing. I input the employee name and it doesn't do anything. No errors either.

 

[gojets1721]

Lots of ways to do this. I might try using DLookup() to find the complaint number and just filter the form with it.

Would you mind providing a quick example? I'm struggling to think of how to code that

 

[561414]

Private Sub btnSearchEmployee_Click()
On Error GoTo btnSearchEmployee_Click_Err

    Dim S As String
    Dim rst As DAO.Recordset

    S = InputBox("Enter Employee Name", "Employee Name Search")
    If S = "" Then Exit Sub

    strSQL = "Select * from tblEmployees where EmployeeName LIKE ""*" & S & "*"""
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If rst.EOF = False Then
    Me.Recordset.FindFirst "ComplaintNumber = " & rst!ComplaintNumber
    End If

btnSearchEmployee_Click_Exit:
    Exit Sub

btnSearchEmployee_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"

    Resume btnSearchEmployee_Click_Exit

End Sub

I've never used an InputBox for anything related to access, so I might say this out of ignorance, but your code pretends to do everything in one go without considering events other than the user clicking a button. Let me explain further:
Your btnSearchEmployee_Click should just open a form with a textbox, something like:

Private Sub btnSearchEmployee_Click()
    DoCmd.OpenForm "theSearchForm"
End Sub

And "theSearchForm" should have a textbox, which I will call "txtSearch", you want to program its change event, like this:

Private Sub txtSearch_Change()
    ' filtering code
End Sub

Separating those two events: Click and Change, to open the form and filter your list from a textbox in the opened form, respectively, will do the trick without any issue.
If you need further assistance, let me know.
EDIT:
Attached an implementation of the search functionality I'm talking about

 

[gojets1721]

I've never used an InputBox for anything related to access, so I might say this out of ignorance, but your code pretends to do everything in one go without considering events other than the user clicking a button. Let me explain further:
Your btnSearchEmployee_Click should just open a form with a textbox, something like:

Private Sub btnSearchEmployee_Click()
    DoCmd.OpenForm "theSearchForm"
End Sub

And "theSearchForm" should have a textbox, which I will call "txtSearch", you want to program its change event, like this:

Private Sub txtSearch_Change()
    ' filtering code
End Sub

Separating those two events: Click and Change, to open the form and filter your list from a textbox in the opened form, respectively, will do the trick without any issue.
If you need further assistance, let me know.
EDIT:
Attached an implementation of the search functionality I'm talking about

Thank you. I've attached a revised example off your original, as my situation is a little more complex.

I'm hoping to filter the frm_complaints based on a separate table. Both tables have a matching complaint number. So based off what the user inputs for a name, it then filters the frm_complaints down by utilizing matching complaintnumbers (hopefully that makes sense)

 

[Pat Hartman]

By putting the complaint number in the employee table, you limit the complaints to ONE for an employee. I'm pretty sure that isn't what you want unless you fire an employee after he gets a single complaint.

If a complaint can be about only one employee at a time, you put the employeeID in the complaint table. If the complaint can be about multiple employees, then you need a junction table to implement a many-to-many relationship. The junction table has both the ComplaintID and the EmployeeID. Use a compound PK or use an autonumber PK but make a compound unique index by using the indexes dialog to prevent accidentally adding the same complaint to an employee at one time.

Here's a link to a m-m sample in case that is what you need.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[gojets1721]

By putting the complaint number in the employee table, you limit the complaints to ONE for an employee. I'm pretty sure that isn't what you want unless you fire an employee after he gets a single complaint.

If a complaint can be about only one employee at a time, you put the employeeID in the complaint table. If the complaint can be about multiple employees, then you need a junction table to implement a many-to-many relationship. The junction table has both the ComplaintID and the EmployeeID. Use a compound PK or use an autonumber PK but make a compound unique index by using the indexes dialog to prevent accidentally adding the same complaint to an employee at one time.

Here's a link to a m-m sample in case that is what you need.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

The employees table is not an exhaustive employee list; that is a separate table. It is a storage of when a complaint is attributed to an employee. So an employee could be listed there several times depending on how many complaints are about them.

 

[Pat Hartman]

That makes it a junction table so take at the Employees form in the sample. Instead of classes, you could have a subform for complaints. You can use a tabbed form if you run out of room.

 

[561414]

Thank you. I've attached a revised example off your original, as my situation is a little more complex.

I'm hoping to filter the frm_complaints based on a separate table. Both tables have a matching complaint number. So based off what the user inputs for a name, it then filters the frm_complaints down by utilizing matching complaintnumbers (hopefully that makes sense)

What you want to do makes sense but it's likely a hack. Attached is a solution to that "hack".
I'm also going to attach what the community and me would lable "right approach" with a many to many table.

The problem is that the employee table has the complaints field, and it should be in another table. Either way, it requires a few changes to the recordsource of the main form.

 

[ebs17]

Help filtering

Using FindFirst isn't actually filtering. If you have a form, using an input box is a bit anachronistic.

Simple: Use a combo box with RowSource...

SELECT ComplaintNumber, fullName FROM employees

And then ...

Private Sub cboEmployees_AfterUpdate()
    Me.Filter = "ComplaintNumber = '" & Me.cboEmployees & "'"
    Me.FilterOn = True
End Sub

If ComplaintNumber really were a number and not text, it would make even more sense.

 

[gojets1721]

What you want to do makes sense but it's likely a hack. Attached is a solution to that "hack".
I'm also going to attach what the community and me would lable "right approach" with a many to many table.

The problem is that the employee table has the complaints field, and it should be in another table. Either way, it requires a few changes to the recordsource of the main form.

Thank you.

For the 'probably wrong' one, is there a way to code the search form so that there's a command to search on click? Rather than it updating from the textbox. I tried to add something in but it's erroring on the 'Dim theSearch As String: theSearch = Me.txtSearch.Text' line

 

[561414]

is there a way to code the search form so that there's a command to search on click?

Just add the button and move the code from the textbox change event to the on click event of the button
That should do it

 

[gojets1721]

Just add the button and move the code from the textbox change event to the on click event of the button
That should do it

Using FindFirst isn't actually filtering. If you have a form, using an input box is a bit anachronistic.

Simple: Use a combo box with RowSource...

SELECT ComplaintNumber, fullName FROM employees

And then ...

Private Sub cboEmployees_AfterUpdate()
    Me.Filter = "ComplaintNumber = '" & Me.cboEmployees & "'"
    Me.FilterOn = True
End Sub

If ComplaintNumber really were a number and not text, it would make even more sense.

Okay, so that kind of worked but the same employee could be attributed to multiple complaints. So 'John Smith' shows up multiple times in the combo box.

I'm looking for a way to select john smith and then all the complaints attributed to him are shown.

 

[561414]

I no longer know what you're doing, do you want type in textbox or choose from combobox?

 

[arnelgp]

here is another using filter.

 

[gojets1721]

here is another using filter.

@arnelgp if complaintnumber was a number field, how would you could the me.filter line? Can't seem to figure that out

 

[arnelgp]

if ComplaintNumber is Number you change the btnSearch Click Event to this:

Private Sub btnSearch_Click()
'    DoCmd.OpenForm "frm_Search", acNormal, , , , acDialog
    Dim sFilter As String
    Dim db As DAO.Database
    Set db = CurrentDb
    sFilter = "'*" & Me.txtSearch & "*'"
    If (sFilter = "'**'") Then
        Me.FilterOn = False
        
    Else
        With db.OpenRecordset("select ComplaintNumber From qryEmployeeComplaints Where fullName like " & sFilter)
            If Not (.BOF And .EOF) Then
                .MoveFirst
                sFilter = ""
                Do While Not .EOF
                    sFilter = sFilter & !ComplaintNumber & ","
                    .MoveNext
                Loop
                sFilter = Left$(sFilter, Len(sFilter) - 1)
                Me.Filter = "ComplaintNumber In (" & sFilter & ")"
                Me.FilterOn = True
            Else
                Me.FilterOn = False
            End If
        End With
    End If
End Sub

 

[gojets1721]

if ComplaintNumber is Number you change the btnSearch Click Event to this:

Private Sub btnSearch_Click()
'    DoCmd.OpenForm "frm_Search", acNormal, , , , acDialog
    Dim sFilter As String
    Dim db As DAO.Database
    Set db = CurrentDb
    sFilter = "'*" & Me.txtSearch & "*'"
    If (sFilter = "'**'") Then
        Me.FilterOn = False
       
    Else
        With db.OpenRecordset("select ComplaintNumber From qryEmployeeComplaints Where fullName like " & sFilter)
            If Not (.BOF And .EOF) Then
                .MoveFirst
                sFilter = ""
                Do While Not .EOF
                    sFilter = sFilter & !ComplaintNumber & ","
                    .MoveNext
                Loop
                sFilter = Left$(sFilter, Len(sFilter) - 1)
                Me.Filter = "ComplaintNumber In (" & sFilter & ")"
                Me.FilterOn = True
            Else
                Me.FilterOn = False
            End If
        End With
    End If
End Sub

That worked! Thanks so much!