list box items

[Russ]

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

 

[Steve Schapel]

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])

 

[Russ]

Steve,
thank you for your help, but i am confused as to where i put this first
code. do i put it instead of the code i had which put all of the meds into
alist box. or do i put it into a new field in the form, which will then list
the meds in teh list box int eh desired format, seperated by commas?

also, i am not aware of where i put a public function. what event heading
does this go under?

tahnk you.
russ

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

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

 

[Steve Schapel]

Russ,

I was suggesting you put this code into a Module. If you don't already
have an existing standard module you could include this in, you can make
a new one by selecting New from the Modules tab of the Database Window.

This is irrelevant to your form and your listbox. This applies to the
Query that you will use for your mail merge. I understood that you have
a table, called SecondTable in my code example, where all the
medications are listed for each patient. My suggested function is an
example of you you can get the medications concatenated into a single
comma-delimited string for each patient. So you just make a calculated
field in the query using the function, and then use the query as the
basis of your mail merge. Have I understood your question correctly?

 

[Russ]

Steve,
I pasted teh function into a new module. I have a few questions.

First: TheSSN and SSN. Which one is from table 1 and which one is from the
second table?

Second: Teh function for the query PatientMeds: ListMeds([SSN]), is this SSN
from the first or second table?

Third: does either table have to be in the sql of teh query?

Fourth and most important: I have been fiddling with different combonations
to get it to work. it seems i have it, but the when i go to view the query,
the program freezes and goes to "Not Responding." is there something i am
doing or not doing that is causing this? it seems to get stuck while it is
running the code. whenit comes time for it to display that field in teh
query is when it freezes.

thank you very much for your help,

Russ

 

[Steve Schapel]

Russ,

I have assumed your second table is called SecondTable and that the
fields are named SSN and Medication. If this is not correct, you will
need to change the code to show the correct names of these fields and
the correct name of the table.

Your query can be based on the first table. Can you please post back
with the SQL view of the query as you have it so far, if it is still
causing problems.

 

[Russ]

Steve,

the Sql is SELECT Demographics.SSN, Demographics.LastName, ListMeds([SSN])
AS PatientMeds
FROM Demographics;

it is still freezing. the code i put into the module is as follows:

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 Patient
Medication WHERE SSN='" & TheSSN & "'")
With rst
Do Until .EOF
MedsList = MedsList & ![Medication] & ", "
Loop
End With
ListMeds = Left(MedsList, Len(MedsList) - 2)
End Function

the secondtable is actually called "Patient Medication" sorry for the
confusion.

also the first table is "demographics" and also has a filed entiled SSN. is
this going to be a problem that they ahve the same name?

thank you very much for your help.
russ

Russ,

I have assumed your second table is called SecondTable and that the
fields are named SSN and Medication. If this is not correct, you will
need to change the code to show the correct names of these fields and
the correct name of the table.

Your query can be based on the first table. Can you please post back
with the SQL view of the query as you have it so far, if it is still
causing problems.

--
Steve Schapel, Microsoft Access MVP

Steve,
I pasted teh function into a new module. I have a few questions.

First: TheSSN and SSN. Which one is from table 1 and which one is from the
second table?

Second: Teh function for the query PatientMeds: ListMeds([SSN]), is this SSN
from the first or second table?

Third: does either table have to be in the sql of teh query?

Fourth and most important: I have been fiddling with different combonations
to get it to work. it seems i have it, but the when i go to view the query,
the program freezes and goes to "Not Responding." is there something i am
doing or not doing that is causing this? it seems to get stuck while it is
running the code. whenit comes time for it to display that field in teh
query is when it freezes.

thank you very much for your help,

Russ

 

[Steve Schapel]

Russ,

There are 2 problems I see with this. One is because of the space in
the table name, so the code will not work unless you put []s around it.
The other problem is my mistake... very sorry. Missed out a MoveNext
line in the code I gave you. Try it like this...

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
[Patient Medication] WHERE SSN='" & TheSSN & "'")
With rst
Do Until .EOF
MedsList = MedsList & ![Medication] & ", "
.MoveNext
Loop
End With
ListMeds = Left(MedsList, Len(MedsList) - 2)
End Function

 

[Russ]

Steve,
thank you. now it does not freeze and will put the medications into one
field. however, the last line does not work. it gives an error and sends me
to debug the code. in order for the code to work, i need to use
ListMeds = (MedsList)
instead of
ListMeds = Left(MedsList, Len(MedsList) - 2)

but this leaves the comma and space at the end. how do i fix this last line?
could you help me solve this last problem? thank you for your time.

russ lafrance

Russ,

There are 2 problems I see with this. One is because of the space in
the table name, so the code will not work unless you put []s around it.
The other problem is my mistake... very sorry. Missed out a MoveNext
line in the code I gave you. Try it like this...

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
[Patient Medication] WHERE SSN='" & TheSSN & "'")
With rst
Do Until .EOF
MedsList = MedsList & ![Medication] & ", "
.MoveNext
Loop
End With
ListMeds = Left(MedsList, Len(MedsList) - 2)
End Function

--
Steve Schapel, Microsoft Access MVP

Steve,

the Sql is SELECT Demographics.SSN, Demographics.LastName, ListMeds([SSN])
AS PatientMeds
FROM Demographics;

it is still freezing. the code i put into the module is as follows:

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 Patient
Medication WHERE SSN='" & TheSSN & "'")
With rst
Do Until .EOF
MedsList = MedsList & ![Medication] & ", "
Loop
End With
ListMeds = Left(MedsList, Len(MedsList) - 2)
End Function

the secondtable is actually called "Patient Medication" sorry for the
confusion.

also the first table is "demographics" and also has a filed entiled SSN. is
this going to be a problem that they ahve the same name?

thank you very much for your help.
russ

 

[Rob Parker]

Test the string before removing trailing characters:

....
If Not IsNull(MedsList) Or Len(MedsList) > 2 then
ListMeds = Left(MedsList, Len(MedsList) - 2)
Else
ListMeds = ""
End If
....


HTH,

Rob

The problem was that it would not work becaues some of the pts did not have
any meds listed, so it could not subtract 2. so i guess my final question
becomes how do i tell it to avoid running the command on those patients who
are not taking medication, or who do not have a record filled out in that
table?

thanks
russ

Russ,

There are 2 problems I see with this. One is because of the space in
the table name, so the code will not work unless you put []s around it.
The other problem is my mistake... very sorry. Missed out a MoveNext
line in the code I gave you. Try it like this...

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
[Patient Medication] WHERE SSN='" & TheSSN & "'")
With rst
Do Until .EOF
MedsList = MedsList & ![Medication] & ", "
.MoveNext
Loop
End With
ListMeds = Left(MedsList, Len(MedsList) - 2)
End Function

--
Steve Schapel, Microsoft Access MVP

Steve,

the Sql is SELECT Demographics.SSN, Demographics.LastName, ListMeds([SSN])
AS PatientMeds
FROM Demographics;

it is still freezing. the code i put into the module is as follows:

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 Patient
Medication WHERE SSN='" & TheSSN & "'")
With rst
Do Until .EOF
MedsList = MedsList & ![Medication] & ", "
Loop
End With
ListMeds = Left(MedsList, Len(MedsList) - 2)
End Function

the secondtable is actually called "Patient Medication" sorry for the
confusion.

also the first table is "demographics" and also has a filed entiled SSN. is
this going to be a problem that they ahve the same name?

thank you very much for your help.
russ

 

[Steve Schapel]

Russ,

Ak, ok, didn't think of that.

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
[Patient Medication] WHERE SSN='" & TheSSN & "'")
With rst
Do Until .EOF
MedsList = MedsList & ![Medication] & ", "
.MoveNext
Loop
End With
If Len(MedsList) Then
MedsList = Left(MedsList, Len(MedsList) - 2)
End If
ListMeds = MedsList
End Function