Creating a Dropdown with ONLY Active Users' Names

[Jim Johnson]

Hi,

I couldn't find anything regarding my specific dilema. I simply need to
maintain a dropdown that lists my users so we can track who entered which
record. My original dropdown selected the values of RequestorName from a
table called tblRequestors that the fields RequestorID, RequestorName and
Function.

This worked fine until someone left and we didn't need to see their name in
the list anymore. When I deleted their entry from tblRequestors, all records
they had entered showed a blank value for the Requestor field.

This is no good because most of reporting is dependant on the function of
the requestor that is tied to the person's name in tblRequestors. Also, even
if a person leaves our team we still want a history of which records they
created.

I tried adding a yes/no field on tblRequestors called Active. I then used
the following for the row source: SELECT tblRequestor.RequestorID,
tblRequestor.RequestorName, tblRequestor.Function FROM tblRequestor WHERE
(((tblRequestor.Active)=True)) ORDER BY tblRequestor.RequestorName;

Unfortunately, this produces the same result as before. When I deselect
Active for a requestor's name on tblRequestors, their individual records have
blank requestor fields. Can anyone please tell me what I'm doing wrong?

Thanks,
Jim

 

[Joan Wild]

You could muck around with a textbox overtop the combobox, changing focus, etc. however I usually just change the rowsource to sort the inactive to the bottom of the list - they'll still show for their records, but aren't likely to get picked.

So drop the WHERE clause and use ORDER BY Active, RequestorName

 

[Jim Johnson]

Hi Joan,

Thanks for your response. Unfortunately, my boss' boss is adamate that the
list only show the active requestors (which is what prompted the change). Is
there a flaw with my code in the row source or is there a field property that
I need to change? Does anyone else have any ideas?

Thanks,
Jim

 

[Joan Wild]

OK, then use your new rowsource that eliminates the inactive requestors. Create a textbox and overlay it on the combobox so that it takes up the space of the combo text area. Set its control source to the Requester's name. In the Got Focus event for the textbox set the focus to the combobox, so that the user can select from the limited list.

The textbox will display the stored value, even if that requestor isn't in the combo list.

 

[Jim Johnson]

Hi Joan (or anyone else that can help),

I was able to accomplish my goals with your resolution. However, the field
I created to display all requestors is displaying with the numeric primary
key instead of the text with the user's name.

Can anyone help me to set this field to display the text and not the number?
Any advise you can give would be greatly appreciated.

Thanks,
Jim

 

[Joan Wild]

The recordsource for your form needs to be a query that includes your main table as well as the tblRequestors. You can then include the person's name as a column in the query - bind your textbox to this field.