CODEWITHSUNDEEP
ms-accessvba
gregg
gregg

Reputation: 1124

combobox rowsource based on forms record source (distinct) field

I have a form record source set to a elaborate SQL select statement. That is working fine. If it helps to know, the form layout is Tabular. Here is an example of the data: 

order carrier billto employee
1     smgd    horm   chrnic
2     axxm    sele   chrnic
3     smgd    horm   redned
4     mcta    cron   greand
5     mcta    cron   greand

Its basically unbilled order entries. I want a combo box to show distinct employee names (chrnic, redned, greand) based on the current records showing. I will be coding it to filter the form. Seems simple, but I am having trouble

Things I have tried:

Upvotes: 1

Views: 3240

Answers (1)

HansUp
HansUp

Reputation: 97101

"I have a form record source set to a elaborate SQL select statement."

Save that query as a named QueryDef. I will pretend you chose qryRecordSource as the name.

"I want a combo box to show distinct employee names ... based on the current records"

For the combo box row source use ...

SELECT DISTINCT employee
FROM qryRecordSource
ORDER BY 1;

And then to filter the form based on the combo selection, add a command button, cmdApplyFilter, and use this in its click event procedure ...

Me.Filter = "[employee] = '" & Me.YourComboName.Value & "'"
Me.FilterOn = True

If the employee names can include an apostrophe, use this for the Filter expression ...

Me.Filter = "[employee] = '" & _
    Replace(Me.YourComboName.Value, "'", "''") & "'"

If you want to include a combo row to clear the filter, use a UNION query as the combo row source ...

SELECT "*** ALL ***" AS employee
FROM Dual
UNION
SELECT employee
FROM qryRecordSource
ORDER BY 1;

... where Dual is any table or query which returns just one row. Then in the command button click event you can do ...

If Me.YourComboName.Value = "*** ALL ***" Or _
        IsNull(Me.YourComboName.Value) Then
    Me.Filter = vbNullString
    Me.FilterOn = False
Else
    Me.Filter = "[employee] = '" & Me.YourComboName.Value & "'"
    Me.FilterOn = True
End If

And actually you wouldn't even need the command button. You could set the Filter from the combo's AfterUpdate event.

Upvotes: 1

Related Questions