List all data of field from a subform to single field in the mainf

[Guest]

How can i list all fields in a subform to a single field in the mainform. An
example is a patient record (main form). In the subform is the list of
medicine the patient has taken. I want in the mainform to list in a single
record all the medicine the patient has taken (e.g. penicillin, norfloxacin).

 

[Albert D. Kallal]

Any reasons why you don't have a continues sub-form that displays this
information as a grid?

Take a look at the following screen shots:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

In the above, you *often* see data listed as a grid. So, build a nice
sub-form (or, you can consier a slist box) to somply display all of the
child reocrds as a grid. You can even do this without even writing one line
of code!!

The above should give you some ideas.

 

[Guest]

actually, i wanted a field that would list all the medicines i have chosen in
a continuous manner, (e.g. penicillin, gentamycin) and not in a row. In the
link you gave me, lets assume that the example is 1 record. now i need a
field that would list all the "Destinations" for that record (golden,
panorama, panorama, panorama, banff). I needed it to be listed in a field in
the main form (one to many relationship with the subform) this way and not in
a row.

I hope it is clear. Thanks!

 

[Albert D. Kallal]

Ah, ok.

We don't want to actually "copy" the data to field in the main form (as this
would make maintains impossible). If you were to edit a destinations in the
sub-form, (or even by code, or sql updates, then the system would have to
"know" to copy the data to the field in the main table. This breaks nearly
every relational database rule in the world. (and, makes the data editing
impossible). Just deleting a record in sub-form would mean that special code
has to run, and re-update the text box in the main table.


However, we most certainly can *display* this information in a text box on
the main form (but, as mentioned, copy data?..no, do NOT do that!!).

For the particular form, you could write a function like:

Public Function ShowDest() As String

Dim rst As DAO.Recordset
Dim strText As String


Set rst = Me.child1.Form.RecordsetClone

If rst.RecordCount > 0 Then
rst.MoveFirst
Do While rst.EOF = False

If strText <> "" Then
strText = strText & ","
End If

strText = strText & rst!desc

rst.MoveNext
Loop
End If

Set rst = Nothing

ShowDest = strText
End Function

Then, simply place a un-bound text box on the main form, and then set the
data source of that text box to the above function

=ShowDest()

The above will thus do what you want.....

If you don't actually have a sub-form in the particular form, then you left
that detail out, but the above code can be modified to deal with this.

Of course, you change the name of the above field name used, and sub-form
name used to whatever you used....

So, the nice thing about the above solution is that if you change, or
delete, or edit the values in the sub-form data, then you don't have to run
special code to display the list as above.

Get the above working. You likely may have to add some code to "refresh" the
list during editing the sub-form data. get the base code working, and then
we can move on to having the display updated correctly *DURING* editing of
the sub-form.

 

[Guest]

thanks for the reply. am just new in using codes for access and i can't dont
know which part to change so could you please help in filling-up the code you
made. My main form is FM_bi while the subform is FM_bi_ext. Its is a
one-to-many relationship. The field "antibiotics" from the subform is the
data i want to display in the "resistant" field of mainform.

Also in which query should i write the function =ShowDest(copy)

Thanks!

 

[Guest]

Thanks for the reply!

I am new in using codes and i dont know how to use the code you made. Can
you please help me fill-up you code? The main form is FM_bi while the subform
is FM_bi_ext. Its a one-to-many relationship. The field "antibiotics",
which is from the subform, is the data i want to display in the "resistant"
field of the mainform.

Also in which query should i write =ShowDest()

Thanks a lot!