change format of subreport (Access97)

Topic

New Reply

I have a combo box on a subreport. Its row source is a select distinct row statement. I would like to change the formatting of the text from a list to a paragraph type format with the data separated by commas unless of course there is only one result. example is below:

Currently:

radio
television
sports
health & beauty
music

Hoping for this:

radio, television, sports, health & beauty, music

Thanks for any ideas you may have… Jenn…

Reply | Quote

Replies

Set the visible property of the combo to No
Create a textbox which will contain the data of the combobox.
Assuming that your combo is named Combo0 and the data is in the first column enter the following function in the code window of the report.

Function ComboToLine() As String
Dim x As Integer
For x = 0 To Me.Combo0.ListCount - 1
   ComboToLine = ComboToLine & Me.Combo0.ItemData(x) & ", "
Next x
ComboToLine = Left(ComboToLine, Len(ComboToLine) - 2)
End Function

In the control source of the textbox enter
=ComboToLine()

Reply | Quote

That kinda sorta works but not completely. I am getting a list of numbers instead of words. The original combo box had a select distinct row statement. How do i now get the new text box to display a value other than the bound column? Also, when i try to view the main report the subreport code generates a runtime error #2478 which doesn’t allow me to use this method in the design view. Any further suggestions?

Reply | Quote

Change the function as follow and replace rst!Naam with rst!…. (the name of the field you want to appear).

Function ComboToLine() As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(Combo0.RowSource)
rst.MoveFirst
Do While Not rst.EOF
ComboToLine = ComboToLine & rst!Naam
rst.MoveNext
Loop
ComboToLine = Left(ComboToLine, Len(ComboToLine) - 2)
Set rst = Nothing
Set db = Nothing
End Function

Reply | Quote

great, that worked… now how do I make it stop outputting all values and only output those with the same ID as the parent report?

Reply | Quote

What is the SQL of rowsource of the combo ?
What is the name of the control containing the ID in the parent report ?
Is the ID numeric or text ?

Reply | Quote

the child and master fields from the parent report to sub report is MEMID. The subreport that contains the combo box is called Speciality. The fields in the subreport are Memid, AffiliationID and an autonumber. The SQL of the rowsource for the combo box is: SELECT DISTINCTROW tblAffiliation.AffliationID, tblAffiliation.Affiliation FROM tblAffiliation ORDER BY tblAffiliation.Affiliation; bound column is 2 (it’s a number).

Also… the parent report is based on a qry that has MEMID as one of it’s fields… memid is not used in any controls… just as the means of linking the subreports as it is the only field common to the subreport and the reports underlying query.

Reply | Quote

Do you have MEMID in the tblAffiliation ?
And you want the text box contains only the names where the MEMID in tblAffiliation = MEMID in the subreport ?

Reply | Quote

The code would then be:

Function ComboToLine() As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT DISTINCTROW tblAffiliation.AffliationID, tblAffiliation.Affiliation " & _
         "FROM tblAffiliation Where tblAffiliation.Memid = " & Me!Memid & " _
         " ORDER BY tblAffiliation.Affiliation"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
   ComboToLine = ComboToLine & rst!Naam
rst.MoveNext
Loop
ComboToLine = Left(ComboToLine, Len(ComboToLine) - 2)
Set rst = Nothing
Set db = Nothing
End Function

and there is no need any more to put the combo on the report.
This suppose also that Memid is numeric

Reply | Quote

MEMID is not a field in tblAffiliation. There is a junction table that links affiliations to members and is called jctblAfftoMem and contains an autonumber, MEMID, and AffiliationID. The subreport called Specialy, that is in question here, is based upon this junction table and the parent report is based on qryCommList which supplies the member ID. Because of the use of the junction table… affiliation ID is just a number and has no meaningful value to the user of the report… so I need to go back to the original tblaffiliation so i can pull a value from the affiliation field.

Reply | Quote

If the data is not sensitive, can you attach the mdb or send it by mail so I can have a look at it. (my address in in my profile)

Reply | Quote

Francois,

I have just sent it. Thank you. Jenn

Reply | Quote

I have deleted the subreport specialty and do all the stuff in the on format event of the report rptcommitteelist.
I have also add code to the on no data event of the report in case there was no data to print. Therefore I hand also to modify the error routine in the On click event of the button cmdprvwCommlist of the form frmoperations.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As DAO.Database
Dim rst As Recordset
Dim strSQL As String
Dim strSpeciality As String
Set db = CurrentDb
If IsNull(Me.MEMID) Then
    Me.txtSpeciality = ""
    Exit Sub
End If
strSQL = "SELECT jcttblAfftoMem.MEMID, tblAffiliation.Affiliation " & _
         "FROM tblAffiliation INNER JOIN jcttblAfftoMem ON " & _
         "(tblAffiliation.AffliationID = jcttblAfftoMem.AffliationID) AND " & _
         "(tblAffiliation.AffliationID = jcttblAfftoMem.AffliationID) " & _
         "WHERE jcttblAfftoMem.MEMID = " & Me.MEMID
Set rst = db.OpenRecordset(strSQL)
strSpeciality = ""
If rst.RecordCount  0 Then
    rst.MoveFirst
    Do While Not rst.EOF
        strSpeciality = strSpeciality & rst!Affiliation & ", "
        rst.MoveNext
    Loop
    strSpeciality = Left(strSpeciality, Len(strSpeciality) - 2)
    Me.txtSpeciality = strSpeciality
Else
    Me.txtSpeciality = ""
End If
Set rst = Nothing
Set db = Nothing
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No data for this Committee", vbOKOnly, "No Data"
Cancel = True
End Sub

Jennifer,

In the database I send you by mail could you modify the lasts lines of the on detail format event of the report to
End If
Set rst = Nothing
Set db = Nothing
End Sub

Reply | Quote