Selective list in combo boxes

[GerryGoldberg]

I have a form that contains Patient demographic data along with a subform that contains Clinic visit (e.g. date, clinic ID, Doctor seen, etc). Both Clinic ID and Doctor seen are combo boxes. Only specific users are allowed to enter new visit records, but all users may view this screen. Because Clinics and Doctors change from time to time, I have added a "Status" (active/Inactive) field to the Clinic and Doctor tables that populate these combo boxes. I don't want the "data entry" user to be able to select "inactive" Clinics (or Doctors) but I do want the "view" user to be able to see all Clinics (or Doctors), regardless of status. I have tried to implement this by checking to see if a user is a "data entry" type and, if so, showing only "active" entries in the combo boxes. If a user is a "view" type, then I show all entries in the combo box drop down. The combo box lists contain two columns, "code" and "description". The "code" field is stored but the "description" field is displayed.

Now, my problem comes when the "data entry" user displays an old clinic record which contains an "inactive" code for "Clinic" or "Doctor". Because there is no match in the combo box list for this situation, the field displays as blank. The only way I can think of to get around this is to display all list entries, active or inactive, and then check the combo value for its status in the beforeUpdate event. I could then notify the "data entry user" if the selected was "inactive" and then cancel the update.

Is there an easier way to do this, perhaps in combination with limitToList and the notInList event?

Thanks for your help,

Gerry Goldberg

 

[RickSpr]

Is it necessary that the data entry user even see the records for inactive clinics or doctors? If not, you could modify the form's Record Source on open to eliminate the records that are causing you this problem.

If it is necessary that data entry users see these records, I think your BeforeUpdate solution is good. But I can think of another one.

In your form's Current event, you can check Me.NewRecord to determine whether the user has positioned to the "empty" record at the end of the recordset. If so, you can simply change the list boxes' Row Source to eliminate the inactive clinics and docs. If Me.NewRecord is False, set the Row Source to include every clinic and doctor. You may also have to requery the list boxes, but I think this happens automatically when you change the Row Source.

This could be inefficient, though, if your list box sources are large tables, because you'd be reloading the list boxes on every record. You could improve the efficiency by using a module-level or static variable to keep track of whether you last loaded the whole list or the restricted one. Then in the Current event, you check the variable to avoid reloading the list boxes unnecessarily. Rick Sprague