How to use Group By for a Combo Box Selection

[yes sir]

I am trying to setup a Command button that will filter my form based on the selection in a combo box.

Form Name: Agent
Combobox: CompanyFilter

Some companies have multiple agents working for them, so when I created my query for the row source for the CompanyFilter comboBox, I decided to use the Total function and use the GroupBy on the query.

In my query the fields Agents.ID and Company are displayed. With the group by on the Agents.ID set to count. When looking at the query it groups all the companies together.

However, when I select a company from the comboBox the filter on the form comes up with an empty record every time.

Here is the code I am using for the command button:

Private Sub Command65_Click()
    If IsNull(Me.CompanyFilter) Then
        Forms!Agent!.FilterOn = False
    Else
     Forms!Agent!.Filter = "Company = '" & Me.CompanyFilter & "'"
       Forms!Agent!.FilterOn = True
    End If
End Sub

Now, when I change the Row Source Type for this comboBox from table/query to Value List and actually type in the name of each individual company. Then when I make a selection it will filter the form correctly. However, I don't want to have to go into design view every time a new agent with a new company pops up and add it to the Value list.

[yes sir]

I just don't understand why it works with the Value List, but not with the Table/Query as the record source type.

[ajetrumpet]

then the problem is obviously in the code or elsewhere. the issue is the box OR the code. the problem with the box would be if you've got an sql with multiple fields selected. do you?? if so, use the right column in your code!

if you only have one field going on in the sql, then try one of these different blocks below:

Private Sub Command65_Click()
    If IsNull(Me.CompanyFilter) Then
        Forms!Agent!.FilterOn = False
    Else
        forms!agent!.filteron =false
     Forms!Agent!.Filter = "Company = '" & Me.CompanyFilter & "'"
       Forms!Agent!.FilterOn = True
    End If
End Sub

Private Sub Command65_Click()
    If IsNull(Me.CompanyFilter) Then
        Forms!Agent!.FilterOn = False
    Else
     Forms!Agent!.Filter = "[Company] = '" & Me.CompanyFilter & "'"
       Forms!Agent!.FilterOn = True
    End If
End Sub

if this is the same form, use ME.:

Private Sub Command65_Click()
    If IsNull(Me.CompanyFilter) Then
        me.FilterOn = False
    Else
     me.Filter = "Company = '" & Me.CompanyFilter & "'"
       me.FilterOn = True
    End If
End Sub

NEVER mix "!" symbols with "." symbols, especially when they're sequential characters in code. 2 reasons - 1) it's unnecessary, because the combination is interchangable for either a "." or "!" in all scenarios, and 2) it confuses the heck out of you later on.

[yes sir]

Adam,

Still having issues. Uploaded a sample of what I have going on. If you have time, feel free to open it up and take a look.

On the Agent form, you will see a comboBox at the top. It is right beside the label that says "Filter by Company." This is the comboBox that is based on the query AgentFilter. I feel the problem lies within the query, because I use the 'Group By' on the Agents ID using the count option. When I look at the query (and even in the combobox itself) it lists every company. However, when I select the company from the comboBox it filters to an empty record. Sometimes, my selection in the comboBox doesn't respond correctly. I will select one company, but the combo displays a different one.

Thanks

[ajetrumpet]

sorry bud, there's no db in your folder

[yes sir]

I was wondering why it uploaded so quick. I went to my recent items and tried to zip it, all it did was zip the shortcut.

Whoops.

Try this one sir.

[ajetrumpet]

the problem is just what I had said earlier. the field you want is in COLUMN 2 of your query. hence, when you reference a box like you're doing, it looks at column one only. make sense?? that's a rule that should be noted.

here's the answer:

Private Sub Command65_Click()
    If IsNull(Me.CompanyFilter) Then
        Forms!Agent!.FilterOn = False
    Else
     Forms!Agent!.Filter = "Company = '" & Me.CompanyFilter.column(1) & "'"
       Forms!Agent!.FilterOn = True
    End If
End Sub

your original code will work just fine if you switch the field order in your query to put COMPANY first.

[yes sir]

the problem is just what I had said earlier. the field you want is in COLUMN 2 of your query. hence, when you reference a box like you're doing, it looks at column one only. make sense?? that's a rule that should be noted.

here's the answer:

Private Sub Command65_Click()
    If IsNull(Me.CompanyFilter) Then
        Forms!Agent!.FilterOn = False
    Else
     Forms!Agent!.Filter = "Company = '" & Me.CompanyFilter.column(1) & "'"
       Forms!Agent!.FilterOn = True
    End If
End Sub

your original code will work just fine if you switch the field order in your query to put COMPANY first.

Adam,

That string of code works for some of the Companys that I select from the CompanyFilter combobox, but for some it keeps resorting back to the first item in the combobox and doesn't filter at all. And when I select the first item (not counting the blank item) I get a run time error 3075.

Again, some of them filter, most of them don't.

[ajetrumpet]

it's doing that bud, because some of your values have apostrophes in it. That's a no-no.

There's ways around that stuff, but they should never have to be done. Change the values instead. If you don't, you might end up writing more code in the form of IF, ELSE's, than you thought.

[RuralGuy]

As Adam knows, just change this line:
Forms!Agent!.Filter = "Company = '" & Me.CompanyFilter.column(1) & "'"
...to...
Forms!Agent!.Filter = "Company = """ & Me.CompanyFilter.column(1) & """"

[ajetrumpet]

As Adam knows, just change this line:
Forms!Agent!.Filter = "Company = '" & Me.CompanyFilter.column(1) & "'"
...to...
Forms!Agent!.Filter = "Company = """ & Me.CompanyFilter.column(1) & """"

I do know that Allan, but I ran into issues testing that. I didn't do the work, as I want this kid to do it and figure it out. I know him, and I know he's smart enough to get it.

[RuralGuy]

Sorry Adam, I didn't mean to step on your teaching style.

[ajetrumpet]

Sorry Adam, I didn't mean to step on your teaching style.

Geez Allan, I was making a point. Not trying to boot you out! But once again, it has proven to be "over the top", apparently.

If we keep going down this road of "everyone gets along", we're gonna find ourselves in a "everyone lives in a box" scenario. Sorry, couldn't resist that truth!

Bottom line - I did not target you. thanks for the contribution. You're obviously right! (and this is not my thread, it's the board's thread)

[RuralGuy]

I should really have started my post with PMFJI but I didn't. Don't be so sensitive Adam. I was not offended. I truly did not understand the style you were using.

[yes sir]

As Adam knows, just change this line:
Forms!Agent!.Filter = "Company = '" & Me.CompanyFilter.column(1) & "'"
...to...
Forms!Agent!.Filter = "Company = """ & Me.CompanyFilter.column(1) & """"

Sorry for the late response. I was trying different quotation marks in the code, but never tried this way. It was probably the only way I didn't try.

The marks have to do with rather the column being reference is a text or number format, correct?

Thanks fellas!