Combining multiple records as single string

[DISI]

I know very little VBA so please be specific & patient. I have a table containing multiple records of "documentation notes" (fields: ID; date; notes). I would like to display all of the records in a text box(?) of a form and later in a report. Table format

ID Date Notes
1 12/1/03 "note entry one."
1 12/2/03 "note entry two."
1 12/3/03 "note entry three."

Display to look like:
12/1/03 note entry one. [carriage return]
12/2/03 note entry two. [carriage return]
12/3/03 note entry three.

Later I will need to print this output the same way. I anticipate having a problem with too many records to display on a single page, so how will I set an appropriate page brake?

Your help is much appreciated!!!


Paul Faculjak
paul@DataIntegritySolutions.com

 

[tunsarod]

Keep it simple.

Open the database window:
select the Forms tab and click the New button
select AutoForm: Tabular
in the drop box below find the table with your data
and select it
click OK and sit back while Access does its stuff.

The resulting form should show what you want but you can easily play around with till it in design view till it looks how you want it.

With the records displayed just click File then Print to dump the lot to your printer or select a group of records and use the Selected records option before clicking OK in the Print dialogue. Don't worry about page breaks. If you have the printer setup and page setup correct Access will take care of it for you.

Use the same process to create a Report by first selecting the Reports tab in the database window and then select AutoReport: Tabular and then selecting your table from the list box.

Again you can adjust the appearance in design view.

 

[DISI]

tunsarod,

Nice simple answer. This will do the trick, but I still want to know how to loop through the records of a dataset and append each to a single field. Can you walk me through that?

Paul Faculjak
paul@DataIntegritySolutions.com

 

[tunsarod]

If you simply want to create a form to display the fields of each record in a single text box then the simplest way is to create a form where all the fields are included. Suppose the following are two such records:

example record 1
Field1 : "DR001"
Field2 : "21/09/03"
Field3 : "Mary had a little lamb"
example record 2
Field1 : "DR002"
Field2 : "29/09/03"
Field3 : "It's fleece was white as snow."

Insert a new textbox control onto the form and set it's ControlSource to: =[Field1] & [Field2] & [Field3]
Resize the new textbox control to enable all the information to be seen and set the visible property for the original fields to No.

Variations to the above :

ControlSource to: =[Field1] & " " & [Field2] & " " & [Field3] (this adds spaces between items)

ControlSource to: =[Field1] & Chr(13) & Chr(10) & [Field2] & Chr(13) & Chr(10) & [Field3]
(this adds carriage returns between each item)


If you want to add a field to the table and modify the records by combining the data from Field1, Field2 and Field3 into a new Field4 then:
Open the table in design view.
Add a new field to the table structure by filling in the necessary details on the next empty line.
Save the table.

Now you have two easy choices:

1. Create an Update Query and run it. (Save it if you think you'll need it again.)

Select the Query tab on the database window.
Click New and select Design View.
Click Close on the Show Table dialogue without selecting a table.

Now an empty query design grid is visible with the View button (top left button on the form's toolbar) showing SQL.

Click the SQL button and the SQL editor will open with the word SELECT; highlighted. Replace this with the following code modified to suite your table name and field names.

UPDATE [Your Tablename] SET [Your Tablename].Field4 = [Your Tablename].Field1 & " " & [Your Tablename].Field2 & " " & [Your Tablename].Field3 ;

When you've done that click the View button (the one that showed SQL before). This will display the data in the table fields that you have selected without actually updating the table - if all's well.

The view button is now showing the blue set square symbol for Design View. Click this and study the information that now appears in the design grid.

The design grid is the fastest way to create a query normally but in this case I suggested typing directly into the SQL editor because it avoided me having to explain how to use the design grid which is explained quite well enough in the Access help files.

Assuming the query is constructed correctly the Run button (middle of the toolbar) with big fat ! mark will execute the query and modify the data.

Needless to say you do not have to create a fourth field. You could just as easily append the contents of Fields 1, 2 and 3 into either of the second fields. Assuming that Field 1 is a numeric ID field it precludes combining text into it from other fields.

alternatively...

2. This choice involves creating a clone of the recordset or table and using the Edit and Update methods to modify the data in each record one by one. In execution it's just as quick but requires coding a sub routine in a module.

Private Sub Update_MyTable()
Dim dbs as Database, rst as Recordset

Set dbs = Currentdb
Set rst = dbs.OpenRecordset("MyTablename&quot

Rst.MoveFirst

With Rst
Do until Rst.EOF
.Append
!Field4 = Field1 & Field2 & Field3
.Update
.MoveNext
Loop
End with
Set Rst = Nothing

End Sub

 

[DISI]

tunsarod,

We are getting closer. The first method combines the fields for a single record. I must move to the next record to see the next entry. The loop coding in your second example is what I am looking for, but I don't want to append the concantinated data, rather, I want to display the concantinated fields of ALL of the records in a single text box.

Here is my use: nurses will be writing daily notes. Each entry is a seaparate record. I will use a query to select only one patient, but I want all of the notes to display as if it were on a single piece of paper where they wrote their notes. Hope this makes it more clear. Thanks sooo very much for the help.

Paul

Paul Faculjak
paul@DataIntegritySolutions.com

 

[tunsarod]

Check your email. I've sent you a solution of sorts. Let me whether it's what you need.

Rod

 

[ammujr]

Rod,
I was wondering if i could see the code that you created for DISI question.

Thank you
Jill
ammujr@yahoo.com

 

[tunsarod]

Hi Jill,

I created a simple example of how the historical notes for a patient might be selected and displayed using three tables: Staff_Details, Patient_Details and Patient_Notes.

Also one form which is setup to show the Patient_Notes in a continuous form view.

Start by creating the three tables:

Table: Patient_Details

Name Type Size
PatientID Number (Long) 4
Lastname Text 50
Firstname Text 50
Initial Text 50
Title Text 50
Doctor Text 50
Ward Text 50

Table: Patient_Notes

Name Type Size
RecID Number (Long) 4
NurseID Number (Long) 4
PatientID Number (Long) 4
Notes Memo -
DateTime Date/Time 8

Table: Staff_Details

Name Type Size
NurseID Number (Long) 4
Lastname Text 50
Firstname Text 50
Initial Text 50
Title Text 50

Next use the wizard to create a form based on the Patient_Notes table using the Tabular format. Now open the form in design view and make the header area larger by dragging the gray Detail bar down. Remove the labels from the header area.

In the detail area I hid the fields RecID, NurseID and PatientID and added an unbound field which I called txtNurseName and set its Control Source to:
=DLookUp("Lastname","Staff_Details","[NurseID] = " & [NurseID])

In the header area I added three unbound textboxes called txtWard, txtDoctor, txtPatientName and a combo box called PickPatientCombo.

The Row Source for the combo box was:

SELECT DISTINCTROW Patient_Details.PatientID, Patient_Details.Lastname, Patient_Details.Firstname, Patient_Details.Initial, [Title] & " " & [Firstname] & " " & [Initial] & " " & [Lastname] AS Expr1, Patient_Details.Doctor, Patient_Details.Ward FROM Patient_Details ORDER BY Patient_Details.Lastname, Patient_Details.Firstname, Patient_Details.Initial;

The column count for the combo box is 7 and the column widths are: 1.503cm;0cm;0cm;0cm;4.508cm;0cm;0cm

The code for the form consists of three sub procedures as follows:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
'force the form to show no records by setting a filter that produces no results.
Me.Filter = "[RecID]= 0"
Me.FilterOn = True
End Sub

Private Sub PickPatientCombo_AfterUpdate()
'set the filter to show only recoirds for the selected patient
Me.Filter = "[PatientID] = " & Nz([PickPatientCombo], 0)
Me.FilterOn = True

Me!txtPatientName = PickPatientCombo.Column(4)
Me!txtDoctor = PickPatientCombo.Column(5)
Me!txtWard = PickPatientCombo.Column(6)
End Sub

Private Sub PickPatientCombo_BeforeUpdate(Cancel As Integer)
'clear the form's header fields
Me!txtPatientName = ""
Me!txtDoctor = ""
Me!txtWard = ""
End Sub


Remember to set the event properties for before and after update to [Event Procedure] in the combo box property sheet.

Set the OrderBy property for the form to [DateTime] Desc to ensure that the most recent notes appears at the top of the list.

How you place the fields is up to you of course. The Doctor and Ward fields were just added for effect in the demo.

One variation that some might prefer is to use a query as the data source for the form. For example create a query and paste this:


SELECT Patient_Notes.RecID, Patient_Notes.NurseID, Patient_Notes.PatientID, Patient_Notes.Notes, Patient_Notes.DateTime
FROM Patient_Notes
WHERE (((Patient_Notes.PatientID)=[Forms]![Select_Patient_Notes]![PickPatientCombo]));

into the query SQL editor. Save the query and call it say SelectPatientNotes.

Enter this name into the form's property sheet as the Record Source.

In this case the forms On Open Event code:

Private Sub Form_Open(Cancel As Integer)
'force the form to show no records by setting a filter that produces no results.
Me.Filter = "[RecID]= 0"
Me.FilterOn = True
End Sub

is redundant.

The main difference between the two methods is that in the first place records are filtered directly on the form and if a user clicks the Remove the Filter button then all records will be displayed. In the second instance removing the filter has no effect since the filtering occurred when the data set was constructed by the query and cannot be undone except by requerying the source data from the table, which occurs when you select another patient using the combo box.

Hope that's all clear. All the code in blue was copied from the working example and can be pasted into your own project.


Rod