Re: list box items

From: Steve Schapel (schapel_at_mvps.org.ns)
Date: 03/06/05 

Date: Sun, 06 Mar 2005 16:01:22 +1300

Russ,

You could write a User-Defined Function in a standard module, and then
use this in your query. Something like this (caution: air code!)...

  Public Function ListMeds(TheSSN As String) As String
      Dim rst As Dao.Recordset
      Dim MedsList As String
      Set rst = CurrentDb.OpenRecordset("SELECT SSN, Medication FROM
SecondTable WHERE SSN='" & TheSSN & "'")
      With rst
          Do Until .EOF
              MedsList = MedsList & ![Medication] & ", "
          Loop
      End With
      ListMeds = Left(MedsList, Len(MedsList) - 2)
  End Function

Then, in your query, make a calculated field like this:
  PatientMeds: ListMeds([SSN]) 

-- 
Steve Schapel, Microsoft Access MVP
Russ wrote:
> I have a list box in a form.  the list box is unbound. it displays a list of 
> medications for a patient.  2 tables are involved. the first table is 
> demographics, which the SSN comes from.  teh second table has SSN and the 
> medication.  the second table has several records for each patient, one 
> record for each medication.
> 
> i can get all the medications into the list box in teh form.  but i now need 
> to put these all into one field of a query or table so that i can use them in 
> a mail merge with word.  i must use mail merge so i can send a letter to 
> referring physician.  how do i get these into one field.  i would like them 
> all to be seperated by commas if possible.
> 
> thank you,
> russ

Relevant Pages

  • list box items
    ... medications for a patient. ... which the SSN comes from. ... teh second table has SSN and the ... i can get all the medications into the list box in teh form. ...
    (microsoft.public.access.formscoding)
  • Re: Q/R Models Again..
    ... The query results depend on the query model and type of query your ... provide unique key for patient level else query would fail. ... SCP must answer ... Study UID must be included also.. ...
    (comp.protocols.dicom)
  • Re: formatting a calculated number field on a form
    ... > A query in an MDB file CAN call a user-defined function. ... > function name is Age and it returns a double. ... The user will certainly "notice" if a patient was born on Jan ... I just want control over the *display format* of a number ...
    (microsoft.public.access.forms)
  • Re: An Alternate Pain Agreement
    ... I recently posted an article about pain contracts here, ... allow little in the way of rights or autonomy for the patient. ... agree to provide opiate medications for my employer (hereafter known as ...
    (alt.support.chronic-pain)
  • Re: An Alternate Pain Agreement
    ... I recently posted an article about pain contracts here, ... allow little in the way of rights or autonomy for the patient. ... agree to provide opiate medications for my employer (hereafter known as ...
    (alt.support.chronic-pain)