Retrieve tabledata for unbound textbox (1 Viewer)

[larshgf]

Hi gurus!

I have a problem which I hope can be solved in this forum:

I have a Form (Frm_Patient) based on a table (Tbl_Patient) containing basic data on patients in my clinic (name, address, telephone, patient_ID etc).
I also have a table (Tbl_Journal) containing journal data (date, diagnoses, notes and patient_ID) - one record for every contact.

My Frm_Patient is tabbed. On the first tabsheet I have the basic data, and on the second tabsheet i want the Journal-notes for this particular patient.
My first solution has been af subform on the journal-tabsheet. But because it will look better I would like to do it like this:
In VBA coding be able to retrieve from the Tbl_Journal all journal-data for a given patient_ID and put it in one (big) unbound textbox note by note, like this example:

23/12/2009 Arthrosis
... here comes Journalnote number 1.....

30/12/2009 Arthrosis
... here comes Journalnote number 2.....

etc..

It implies something like this:
1: loop through the Tbl_Journal and retrieve the data for the actual patient_ID.
2: For every record matching the patient_ID retrieve the data and place them in the textbox as ilustrated.

Can somebody gove me a hint (or piece of VBA code) on how to acomplish this?

Thank you in advance (I'm an optimistic guy!)
Lars

 

[maxmangion]

You can use a subform on the second tab and all related records to the patient will be displayed automatically. Try searching for subform and you will find several examples how you can achieve this.

 

[larshgf]

"My first solution has been af subform on the journal-tabsheet. But because it will look better I would like to do it like this:"

Thank you MaxMangion but as I wrote I have used this solution and I would like the other solution because I want all journal text with headers (date & diagnosis) in one big textbox.

 

[neileg]

I don't understand. If you are using a continuous subform it can be made to look exactly like you want. You have full control over the appearance and positioning of the controls on the form.

You're not using a subform in datasheet mode are you? You have no control over the appearance in that mode.

 

[larshgf]

Thank you Neileg!

Right now I do in fact use subforms for my journals and in this way I have the induvidual journal notes "stacked" upon each other with the date and the diagnosis to the left of the notes. It is not datasheet but all the same i have sort of 3 columns (1: date, 2: diagnosis, 3: journalnote).
What I would like was one, big textbox that contain all the notes for the patient I have selected: the individual journalnotes headed by date and diagnoses (maybe in bold in order to get a better overview).
Right now I am reading "Access 2007 VBA Programming for Dummies" and reading about Recordsets right now I think that a recordsset might be a solution for me.

BR
Lars

 

[neileg]

No, a continuous form is the answer. A very simple example is attached in A2003 format since I don't have A2007.

 

[larshgf]

Hi neileg,

Thank you for your access db example. As I mentioned this solution can be used but it is not what I want.
In the meantime I have solved the problem using this code in the Change eventhandler in my Combobox called "myselctor". I am choosing a patient in my combobox and in a big textbox called "ResultatTekst" the journal-notes for the selected patient are showed - each beginning with the date, type of contact (consultation, telephone, e-mail) and diagnosis in bold.
The Date goes in MyRecordSet.Fields(2), Contact-type in MyRecordSet.Fields(4) and diagnosis in MyRecordSet.Fields(3).
The code in the eventhandler looks like this:

Private Sub myselector_Change()

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim MyRecordSet As New ADODB.Recordset
Dim MyLoop As Integer
Dim MySQL As String
MyRecordSet.ActiveConnection = cnn1
MyRecordSet.Open "[Journalnotat] WHERE [cpr-nr] = '" & [Forms]![Stamkort].[MySelector] & "' ORDER BY [Dato] DESC"
MyRecordSet.MoveFirst
MyLoop = 0
ResultatTekst.SetFocus
ResultatTekst.Value = ""
Do While MyRecordSet.EOF = False
MyLoop = MyLoop + 1
If MyLoop > 1 Then
ResultatTekst.Value = ResultatTekst.Value & "<div> </div>" & "<Strong>"
Else
ResultatTekst.Value = ResultatTekst.Value & "<Strong>"
End If
ResultatTekst.Value = ResultatTekst.Value & MyRecordSet.Fields(2) & " [" & MyRecordSet.Fields(4) & "] " & MyRecordSet.Fields(3) & "</Strong>"
ResultatTekst.Value = ResultatTekst.Value & vbNewLine & MyRecordSet.Fields(5)
MyRecordSet.MoveNext
Loop
Overskrift.Caption = [Fornavn] & " " & [Efternavn] & " (" & Left([cpr-nr], 6) & "-" & Right([cpr-nr], 4) & ")"
ResultatTekst.SelStart = 0
​

End Sub

Maybe somebody can use this code...

Best Regards
Lars

 

[neileg]

Up to you. Just a harder way of achieving the same result as far as I can see.