Combo Box Where Clause

[Karen]

I have a form that is based on a query. On that form is a combo box field
called "Entered By" with a sql statement for the rowsource. For data entry
purposes, I want to exlude those users who are no longer with the department.
However, when the form is in edit or view, I want the name of the user to
appear. When I include the Expire field in the sql where clause and I view
previous entries for expired users, I can not see their name on the form.

The sql statement is:
SELECT tblUsers.UserID, tblUsers!FirstName & " " & tblUsers!LastName AS
FullName
FROM tblUsers
WHERE (((tblUsers.Expire)=No))
ORDER BY tblUsers.LastName;

Any help will be greatly appreciated

 

[Bob Quintal]

I have a form that is based on a query. On that form is a
combo box field called "Entered By" with a sql statement for
the rowsource. For data entry purposes, I want to exlude
those users who are no longer with the department.
However, when the form is in edit or view, I want the name of
the user to
appear. When I include the Expire field in the sql where
clause and I view previous entries for expired users, I can
not see their name on the form.

The sql statement is:
SELECT tblUsers.UserID, tblUsers!FirstName & " " &
tblUsers!LastName AS FullName
FROM tblUsers
WHERE (((tblUsers.Expire)=No))
ORDER BY tblUsers.LastName;

Any help will be greatly appreciated

What I do is put a textbox in exactly the same position as the
combobox.Set the combobox.visible property to false
bind the textbox to the underlying field, and remove the bind
from the combobox.

Put a statement in the combobox afterupdate event to put the
combobox value into the textbox.

In the On Current event of the form, test if there is a blank in
the textbox, and if it is, make the combobox visible and the
textbox invisible. If it is not empty, you invert the .visible
values.

 

[Carl Rapson]

I have a form that is based on a query. On that form is a combo box field
called "Entered By" with a sql statement for the rowsource. For data
entry
purposes, I want to exlude those users who are no longer with the
department.
However, when the form is in edit or view, I want the name of the user to
appear. When I include the Expire field in the sql where clause and I
view
previous entries for expired users, I can not see their name on the form.

The sql statement is:
SELECT tblUsers.UserID, tblUsers!FirstName & " " & tblUsers!LastName AS
FullName
FROM tblUsers
WHERE (((tblUsers.Expire)=No))
ORDER BY tblUsers.LastName;

Any help will be greatly appreciated

One thing you might try is, in the Current event of the form check to see if
you are on a new record (Me.NewRecord). If so, modify the RowSource of the
combo box to exclude expired users; if not, modify the RowSource to show all
users.

Carl Rapson

 

[Steve]

Add an unbound checkbox to your form, label it "Show All Users" and set its
default to False. Change your Where clause to:
WHERE (((tblUsers.Expire)=Forms!NameOfYourForm!NameOfTheCheckBox))
Put the following code in the AfterUpdate event of the checkbox:
Me.Requery

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Karen]

I tried that, but could not get it to work. I am not very good at VBA yet.
Also, I have a macro that runs from the On Current of the form for something
else. Does one cancel the other out?

 

[Carl Rapson]

That's a good question. My guess would be that both should execute, but I'm
not sure. You say "could not get it to work"; what did happen when you tried
it? What code did you use in the Current event?

Carl Rapson