Hold Filter on Subform

[acarterczyz]

Hey all!

I've been searching for a while for a way I could make this work..

I'm trying to make it so I could filter a subform from the main form and it will hold those results and allow them to be edited in the subform without it refiltering it. I want it to still be able to update the table by whats edited in the subform, but I don't want it to "disappear" from the subform when the filtered "matching" criteria is changed on a record.

How could I go about accomplishing this?

Here is my code for the filter of my subform:

Private Sub Form_Load()
    strFilter = "[Rep Number] = " & "RepNumberField" & _
                " AND " & "[UserName] = " & "UserNameField" & ""
    RepReassignmentSubform.Form.Filter = strFilter
    RepReassignmentSubform.Form.FilterOn = True
End Sub

Thanks in advance!

 

[vbaInet]

I don't follow. Can you elaborate?

By the way, the filter criteria is incorrect.

 

[acarterczyz]

I don't follow. Can you elaborate?

By the way, the filter criteria is incorrect.

Incorrect?

So here is the scenario: I have a list of sales reps that are assigned to regions. I want to be able to open a subform and filter it by the sales rep. When I reassign it to a different sales rep, the record disappears from the subform (because the rep from the filter criteria changed). I want it to remain in that subform.

 

[vbaInet]

Let's address this:

    strFilter = "[Rep Number] = " & [COLOR="Red"]"RepNumberField"[/COLOR] & _
                " AND " & "[UserName] = " & [COLOR="red"]"UserNameField"[/COLOR] & ""

What are those? Field names or control names or perhaps functions?

Still isn't making much sense to me. When you reassign what? The rep in the filter criteria didn't change it just doesn't match what's in the criteria. Perhaps it would be better if you explain using examples, i.e. with data.

 

[acarterczyz]

Let's address this:

    strFilter = "[Rep Number] = " & [COLOR="Red"]"RepNumberField"[/COLOR] & _
                " AND " & "[UserName] = " & [COLOR="red"]"UserNameField"[/COLOR] & ""

What are those? Field names or control names or perhaps functions?

Still isn't making much sense to me. When you reassign what? The rep in the filter criteria didn't change it just doesn't match what's in the criteria. Perhaps it would be better if you explain using examples, i.e. with data.

Thanks for the response.
RepNumberField and UserNameField are fields on my main form. The user name field is a field generated by the computer login.

The database is being used in my workplace. When we have a sales rep leave the company, we need to reassign all their territories to another sales rep. When you open the database, the main form appears and allows you to enter the sales rep's number. This brings up a new window that shows the subform filtered by the sales rep's employee number. At this point, we could change the sales rep number to a different person's number (reassign it). When this is done, the subform somehow filters out that change, as we are filtering it by their employee number to begin with.

Sorry if this isn't making sense. Its difficult to explain. I could attempt to build a sample database with random data and upload it.

 

[vbaInet]

In that case your filter should be this:

    strFilter = "[Rep Number] = " & Me.RepNumberField & "'" & _
                " AND " & "[UserName] = '" & Me.UserNameField & "'"

If you want to still display the re-assigned user, save the filter in a string and concatenate the new Rep Number filter string to the saved fields.

There's a piece of code that does the re-assigning so make sure to save the filter before it's run.

 

[acarterczyz]

In that case your filter should be this:

    strFilter = "[Rep Number] = " & Me.RepNumberField & "'" & _
                " AND " & "[UserName] = '" & Me.UserNameField & "'"

If you want to still display the re-assigned user, save the filter in a string and concatenate the new Rep Number filter string to the saved fields.

There's a piece of code that does the re-assigning so make sure to save the filter before it's run.

Thank you, but I'm not sure I follow... The filter is in a string (strFilter). How would I concatenate the filter to the saved values?

 

[vbaInet]

Are you filtering from the subform or the main form? The Filter property also returns the string so you can get it from there.

 

[acarterczyz]

Are you filtering from the subform or the main form? The Filter property also returns the string so you can get it from there.

I'm filtering from the main form. But I'm not sure how to go about doing this.

 

[vbaInet]

Get the first filter working properly first and we'll take it from there. You may think it's working but from what I see it's not properly set.

What is the significance of the UserName in this filter? Why do you need to filter by user name as well?

 

[acarterczyz]

I've edited the filter to:

       strFilter = "[Rep Number] = " & Me.RepNumberField & "'" & _
                " AND " & "[UserName] = '" & Me.UserNameField & "'"
        RepReassignmentSubform.Form.FilterOn = True

The UserName field is based off the user's computer login. There are about 12 people in my department who have several sales reps under them. This filters for only the sales reps under them.

 

[vbaInet]

Ok. I don't know how the re-assigning process works so you tell me. If after re-assigning an ex-employee, is s/he still assigned to the user at the table level?

 

[acarterczyz]

Yes. Basically its taking out the ex-employee's number and replacing them with a new employee, which is under the same manager.

 

[vbaInet]

I think you misunderstand my question. If you're saying that a replacement takes place then the ex-employee will no longer be linked to the Manager.

 

[acarterczyz]

I think you misunderstand my question. If you're saying that a replacement takes place then the ex-employee will no longer be linked to the Manager.

That is correct, that ex-employee would not be linked to anyone.

 

[vbaInet]

So that's one problem.

After the replacement, if you remove the filter completely does this ex-employee still show up? I'm trying to ascertain if the current dataset contains all employees (current and old) or only those employees who are linked to a manager.

 

[acarterczyz]

So that's one problem.

After the replacement, if you remove the filter completely does this ex-employee still show up? I'm trying to ascertain if the current dataset contains all employees (current and old) or only those employees who are linked to a manager.

The ex-employee is only listed in that subform(table), so if you removed him from that subform, he would be gone completely. If you removed the filter completely, it would show all territories under all managers and who is assigned to that territory. The current dataset only includes current employees, as old employees are "overwritten" with new ones.

The table has the following columns:
1. Manager (UserName)
2. Territory
3. Rep Number (Rep Number)

 

[vbaInet]

Well you need to create a query that will contain all employees then you will be able to filter accordingly.

 

[acarterczyz]

Well you need to create a query that will contain all employees then you will be able to filter accordingly.

I'm not certain how that would help me filtering. I have a table with all the reps listed (separate from the assignments table). My issue is keeping the subform from filtering out the change as its made. I want to be able to filter a subform by rep number and then change them on the subform and not have them be filtered off as I'm overwriting the rep I've filtered for.

 

[vbaInet]

If the change no longer exists in the dataset it cannot be fabricated can it?