Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Box - display only items not 'removed' (2003)

    Hi All,
    I have tried to search for this, I'm sure it has come up before - but I might not be wording it correctly.
    I work at a hospital and have inherited a MS Access database that needs some changes. It tracks PICCs (Peripherally Inserted Central Catheter) - a type of IV.

    It has several dropdown list boxes based on tables. Some of the choices in the tables have changed - for instance, [PICC RN] nurse names. Some of them no longer work at the hospital. The users do not want to see the choices on the form when they are entering data - and we do not want to loose that history - so,

    1. Can I add a field in the list box table to say [removed] and then tell the form to only use the list names that are NOT [removed]? If so, how?
    2. In the future, when creating a list box table - should I create a query to use on the form so that I can exclude the [removed]? Or what is the best way to handle this?

    I am not good at VB and only write a little SQL - so the easier the better for me <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thank you,
    Vicky

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Box - display only items not 'removed' (2003)

    Open the table that lists all nurses in design view.
    Add a field Removed and set its data type to Yes/No.
    Save the table design.

    Create a new form based on this table, using all fields including the Removed field.
    You can use this form to view *all* past and present nurses, and you can tick the Removed check box when a nurse leaves.

    On the main data entry form, you'll want to be able to select only presently active nurses from the combo box, but you'll still want to see nurses who have left in existing records. To accomplish this, you can set the Row Source of the combo box to a query or SQL statement that selects those nurses for whom Removed is False plus the currently selected nurse. The exact design of this query/SQL statement depends on the way the table and combo box have been set up.

    If you'd like more detailed help, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Box - display only items not 'removed' (2003)

    BTW, the e-mail notification of my reply was returned as undeliverable. Please go to Edit profile and make sure that the e-mail address for administrative use is up-to-date.

  4. #4
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box - display only items not 'removed' (2003)

    Thank you Hans, I will do what you've explained and will send a stripped down version if I still have trouble. It does make sense.

    Also, I did update my email as well.
    Vicky

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box - display only items not 'removed' (2003)

    This worked perfectly! Thank you so much. I will be using it a lot!
    Vicky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •