Filter dropdown in ComboBox but retain visible on form

[Sierras]

Hi

In a time sheet database, I have a drop down combo box that filters
out only Active Employees when assigning times. It works fine.
But when an employee is no longer active, the drop down filters the
names not to show that person anymore (that's good) - but it also
shows a blank field for all the previous times that the employee
worked (that's bad)

How can I make it so that the drop down ONLY gets filtered for active
employees and remove the filter from the form to show all the records
that are already in the database.

Thanks..

 

[Sierras]

Well after playing around with this a bit, I found a way to do it. But
it's not pretty...

Basically, I put a text box covering the comboBox field so that just
the drop down arrow is showing and then enter this code in the text
box:

=DLookUp("FieldName","tblxx","[IDFieldName] = " & Nz([IDFieldName],0))

This seems to work, but is kind of messy and the performance isn't
great. It takes a split second to populate the fields if the user
scrolls the continous form real fast. Also, since I locked the text
box, the user can no longer just edit the field but rather is forced
to use the drop down arrow. That takes a little bit out of the
functionality.

Anyone got another way of doing this?

 

[Douglas J. Steele]

Take a look at my February, 2006 "Access Answers" column in Pinnacle
Publication's "Smart Access".

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Well after playing around with this a bit, I found a way to do it. But
it's not pretty...

Basically, I put a text box covering the comboBox field so that just
the drop down arrow is showing and then enter this code in the text
box:

=DLookUp("FieldName","tblxx","[IDFieldName] = " & Nz([IDFieldName],0))

This seems to work, but is kind of messy and the performance isn't
great. It takes a split second to populate the fields if the user
scrolls the continous form real fast. Also, since I locked the text
box, the user can no longer just edit the field but rather is forced
to use the drop down arrow. That takes a little bit out of the
functionality.

Anyone got another way of doing this?

Hi

In a time sheet database, I have a drop down combo box that filters
out only Active Employees when assigning times. It works fine.
But when an employee is no longer active, the drop down filters the
names not to show that person anymore (that's good) - but it also
shows a blank field for all the previous times that the employee
worked (that's bad)

How can I make it so that the drop down ONLY gets filtered for active
employees and remove the filter from the form to show all the records
that are already in the database.

Thanks..

 

[Bruce Rusk]

One way to do this would be to incorporate the NewRecord property of the
form into the RowSource for the combo box. That is, if the record is
new, allow only active employees to be listed, otherwise allow all
employees to be listed. You could either code this in the Current event
of the form or make a query that include the "Active" field (e.g.,
"SELECT EmployeeID, Name FROM tblEmployees WHERE Active =
Forms!frmEmployees.NewRecord OR Active = TRUE" On new records, only
employess where Active is true (assuming it's a yes/no field) will
display; on older records all will display.

The problem with this approach is that on existing records you will be
able to enter inactive employees, but this might be necessary in order
to enter historical data (e.g., an employee who quit on Thursday but
worked on Wednesday).

 

[Sierras]

Thanks - I like the method you show here instead of the D lookup
method. The performance of the form works much better and there is no
longer a delay when the user fast scrolls through the form. I
especially like the way you set focus to the underlying combo. It
gives the user a much better feel when clicking around the form. In
fact I'll probably go back and modify all my cascading combo boxes I'm
using in other databases to this method.

The only thing I don't like is that little one second delay when
refreshing the data for the combo when the user wants to modify the
combo. But I've noticed that it doesn't happen all the time. I find
that it mostly happens when you first launch the database and open
that form. And seems to go away at some point while using the
database.

Can't seem to figure out what's causing it?

Thanks again..

 

[Douglas J. Steele]

Can't say I've noticed it.