Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access (2000 SR1)

    I have a table which contains fields including the following: date, patient ID, study performed, Indication, ordering physician, and results. I would like to have a list box to limit the responses in the RESULTS field so that searches and statistical reports will be more accurate. There are about 10 different kinds of studies allowing 4-6 responses each for results. Some of the appropriate responses are the same for dirfferent studies (such as normal/abnormal or negative/positive) but about half of the various studies require more specific responses. Is there a way to have the RESULTS listbox show only the appropriate responses for the specific study entered in the STUDY PERFORMED field? Or is there a better way to offer a list of appropriate responses for each type of study?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Access (2000 SR1)

    Yes there is.
    Firstly you need an extra table
    * a StudyResults table that says which results apply to which studies.
    Then the data source for the Results List box would be a query that takes the Study Performed field as a parameter so that it only lists results relevant to that study.
    Then in the "After update" event for the Study Perfomed field you would need code to requery the Results List box, and also in the "On current" event for the form.

    Do you know how to specify a form value as a query parameter?
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access (2000 SR1)

    Thank you for your response. What you describe it about what I imagined I would need to do. I had already created the Results table (with these fields: ID (autonumber and primary key, StudyType, and Results) and tried using the LOOKUP LIST WIZARD but was unsuccessful. I got as far as creating the dropdown lookup list on the data input form, with all the results possibilites listed, and then when I picked one the autonumber ID field was placed in the RESULTS field instead of the results text I wanted. So, I guess the answer to your question: ("Do I know how to specify a form value as a query parameter?") is . "No. No, I don't. [img]/forums/images/smilies/smile.gif[/img]

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Access (2000 SR1)

    I attach a little file showing this working.
    The Lookup Wizard automatically adds the autonumber field which you don't want. so in the row source of the combo box remove the autonumber field, then in the format area of properties set the column count to 1, and in the column widths area delete the 0 width field width.
    Your combo still will list all possible results rather than just those relevant to a previously selected study type.
    Create a new query as shown below and set the row source of the combo box to that query. This query selects just the results relevant to the study. the afterupdate event for the studyperformed field needs code to requery the other combo box.
    Attached Images Attached Images
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access (2000 SR1)

    Thank you very much for your reply to my post. I will give it a try.

    You folks are all so very helpful and patient with those of us who are new to this process. I really do appreciate it.

Posting Permissions

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