How do I limit drop-down but not displayed combo box?

  • Thread starter Thread starter elillis
  • Start date Start date
E

elillis

I have a combo box that lists employees from tblEmployees.

PersonID LastName FirstName Active?
1 Smith John Yes
2 Jones Bob Yes
3 Tucker Joe No

On the drop-down I only want active employees (not Joe Tucker), but on the
form I want to show Joe Tucker if he is already listed.
 
elillis,

The items shown in the drop-down list of a combobox is determined by the
combobox's Row Source property. You can make a query with a Criteria in
the Active field so you only get the active employees, and then use this
query for the row source of the combobox.
 
I did it this way except an existing record with 'Joe Tucker', who is
already in the system shows up blank because he is not an 'active' employee.

ed
 
Ed,

I'm not sure that I understand you, but it sounds like you need one
query for the Row Source of the combobox, which only shows the active
employees, and another separate query for the Record Source of the form
itself, so the inactive employees are also included. Alternatively,
have your table or query, showing all emoployees, as the Record Source
of the form, and then set the Row Source of the combobox to the
equivalent of...
SELECT PersonID, LastName, FirstName FROM YourTableOrQuery WHERE
Active = "Yes"
 
Almost:

Joe Tucker (PersonID=3) is in the Record Source of the form (because at one
time he was an Active employee).
He isnot in the Record Source of the combobox (because he is no longer an
Active employee).

Therefore, Joe Tucker is not being displayed in the combobox!

I want him displayed in the combobox, but not in the dropdown! This record
is not about Joe Tucker, but about the report he wrote. I still want people
to know he wrote it. I do not want Joe Tucker assigned as a new report
auther.

ed
 
Ed,

Aha! I think I've grasped your meaning, at last.

Well, one way to approach this would be to adjust the Row Source of the
combobox using VBA procedures. For example, on the Enter event of the
combobox, you could do like this...
With Me.YourCombobox
.RowSource = "SELECT PersonID, LastName, FirstName FROM
YourTableOrQuery WHERE Active = 'Yes'"
.Requery
.DropDown
End With

.... and then on the Exit event, like this...
With Me.YourCombobox
.RowSource = "SELECT PersonID, LastName, FirstName FROM
YourTableOrQuery"
.Requery
End With

Another possibility is to set the RowSource of the combobox to a Union
Query, something like this...
SELECT PersonID, LastName, FirstName
FROM YourTableOrQuery
WHERE Active = 'Yes'
UNION SELECT PersonID, LastName, FirstName
FROM YourTableOrQuery
WHERE PersonID = [Forms]![YourForm]![ComboboxName]

.... and then Requery the combobox on the Current event of the form.

A third idea is to set the Limit To List property of the combobox to No.
You could then put a Validation Rule on the field the combobox is
bound to in the table, or else code on the combobox's Before Update
event, to check the data entered.
 
Thanks Steve, I like door number 1...

ed

Steve Schapel said:
Ed,

Aha! I think I've grasped your meaning, at last.

Well, one way to approach this would be to adjust the Row Source of the
combobox using VBA procedures. For example, on the Enter event of the
combobox, you could do like this...
With Me.YourCombobox
.RowSource = "SELECT PersonID, LastName, FirstName FROM
YourTableOrQuery WHERE Active = 'Yes'"
.Requery
.DropDown
End With

... and then on the Exit event, like this...
With Me.YourCombobox
.RowSource = "SELECT PersonID, LastName, FirstName FROM
YourTableOrQuery"
.Requery
End With

Another possibility is to set the RowSource of the combobox to a Union
Query, something like this...
SELECT PersonID, LastName, FirstName
FROM YourTableOrQuery
WHERE Active = 'Yes'
UNION SELECT PersonID, LastName, FirstName
FROM YourTableOrQuery
WHERE PersonID = [Forms]![YourForm]![ComboboxName]

... and then Requery the combobox on the Current event of the form.

A third idea is to set the Limit To List property of the combobox to No.
You could then put a Validation Rule on the field the combobox is bound to
in the table, or else code on the combobox's Before Update event, to check
the data entered.

--
Steve Schapel, Microsoft Access MVP
Almost:

Joe Tucker (PersonID=3) is in the Record Source of the form (because at
one time he was an Active employee).
He isnot in the Record Source of the combobox (because he is no longer an
Active employee).

Therefore, Joe Tucker is not being displayed in the combobox!

I want him displayed in the combobox, but not in the dropdown! This
record is not about Joe Tucker, but about the report he wrote. I still
want people to know he wrote it. I do not want Joe Tucker assigned as a
new report auther.

ed
Click to expand...
Click to expand...
 
Actualy I liked door number 2 better. door number 1 blanked out the
combobox while the cursur was on it.

Ed

elillis said:
Thanks Steve, I like door number 1...

ed

Steve Schapel said:
Ed,

Aha! I think I've grasped your meaning, at last.

Well, one way to approach this would be to adjust the Row Source of the
combobox using VBA procedures. For example, on the Enter event of the
combobox, you could do like this...
With Me.YourCombobox
.RowSource = "SELECT PersonID, LastName, FirstName FROM
YourTableOrQuery WHERE Active = 'Yes'"
.Requery
.DropDown
End With

... and then on the Exit event, like this...
With Me.YourCombobox
.RowSource = "SELECT PersonID, LastName, FirstName FROM
YourTableOrQuery"
.Requery
End With

Another possibility is to set the RowSource of the combobox to a Union
Query, something like this...
SELECT PersonID, LastName, FirstName
FROM YourTableOrQuery
WHERE Active = 'Yes'
UNION SELECT PersonID, LastName, FirstName
FROM YourTableOrQuery
WHERE PersonID = [Forms]![YourForm]![ComboboxName]

... and then Requery the combobox on the Current event of the form.

A third idea is to set the Limit To List property of the combobox to No.
You could then put a Validation Rule on the field the combobox is bound
to in the table, or else code on the combobox's Before Update event, to
check the data entered.

--
Steve Schapel, Microsoft Access MVP
Almost:

Joe Tucker (PersonID=3) is in the Record Source of the form (because at
one time he was an Active employee).
He isnot in the Record Source of the combobox (because he is no longer
an Active employee).

Therefore, Joe Tucker is not being displayed in the combobox!

I want him displayed in the combobox, but not in the dropdown! This
record is not about Joe Tucker, but about the report he wrote. I still
want people to know he wrote it. I do not want Joe Tucker assigned as a
new report auther.

ed
Click to expand...
Click to expand...
Click to expand...
 
Ed,

Well, this is true, it probably would in the case of an inactive
employee aleady being selected... although the cursor would only be on
it if you were selecting another one, right? Why else would you want
the focus on the combobox? And in that case, why does it matter if it's
blank as you are entering editing? Anyway, pleased to hear that we've
made progress here.
 
Back
Top