HOW DO I SHOW ALL NAMES IN ONE OR TWO LINES?

  • Thread starter Thread starter Glint
  • Start date Start date
G

Glint

Hi All,
I have a table consisting of records of classes (date/time of lessons,
venue, book etc) and a related one for the teachers. There can be up to six
teachers to a class, minimum of one.
The problem I have is that of space: I have only two lines to show the names
(names only) of all teachers assigned to a class on my form (sub to the main
form showing classes).
Can you suggest an easy way to do this?
 
the easiest way to handle it is to make a scroll bar available in the
subform, so the user can scroll the names as needed.

hth
 
Thanks, Tina.
I forgot to mention that I need to print the form as a report sometimes. In
fact, that is the main reason for need to save space: it has to fit into one
page only. So I only have two lines to display the names of the teachers.
I was thinking of using one textbox and pour the names as comma separated
values into it, but I don't know how to that either.
 
what are you going to do when you have a few teachers with long names, so
six names strung together just simply takes up more than two lines?

at any rate, i'd stick with the scroll bar for the form. for the report, you
could use a subreport with columns to display the teacher names. three
columns would do it - see the "long names" issue above, though.

another solution might be to build a text string in the report's VBA module
and assign it to an unbound textbox control in the report, using the
OnFormat or OnPrint event of whatever report section you put the control in.
again, see the "long names" issue above.

hth
 
Thanx again, TINA.
I think I will go with your last option: building a text string in VBA (God
help me on that one). If I get into trouble, I will holler again.
Thanks.
 
You might try the following. You can call it in a query. That would
look something like the following - depending on table structure and
field types.


Field: ListNames: Concatenate ("SELECT TeacherNames FROM Teachers INNER
JOIN ClassTeachers on Teachers.TeacherID = ClassTeachers.TeacherID
WHERE ClassTeachers.ClassID = " & Classes.ClassID)



Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with
both ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO
while the default for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Set db = CurrentDb
Set db = DbEngine(0)(0) '<<<< Faster to create >>>>
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next three lines========
'====== comment out DAO above ========
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, _
'adLockOptimistic

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing

'====== comment out next line for ADO ===========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
I built a function to return the teachers' names for each class, and assigned
it to the control source of the textbox, and it appears to be wroking well.
Thanks.
 
Hey, John Spencer, YOU ARE A HERO!
That was exactly what I needed. And in a query too. Now I can discard my
longer, ugly code for your suggestion.
My compliments to Duane Hookom too.
 
Back
Top