Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    change format of subreport (Access97)

    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...

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    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.
    <pre>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</pre>

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

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    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?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    Change the function as follow and replace rst!Naam with rst!.... (the name of the field you want to appear).
    <pre>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</pre>

    Francois

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    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?

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    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 ?
    Francois

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    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.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    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 ?
    Francois

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    The code would then be:
    <pre>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</pre>

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

  10. #10
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    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.

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    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)
    Francois

  12. #12
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    Francois,

    I have just sent it. Thank you. Jenn

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change format of subreport (Access97)

    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.
    <pre>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
    </pre>

    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
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •