Results 1 to 13 of 13

Thread: Creating Report

  1. #1
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Unanswered: Creating Report

    I made a form with a lot of controls.
    Depending on the values of these controls I create a SQL-statement.

    Now I want to generate a report based on this SQL-statement.

    for example :

    strSQL = "SELECT field1, field2, field3, field4 FROM myTABLE WHERE field1 = "condition1" AND field2 = true GROUP BY field3 ORDER BY field4 "

    But now I got stuck: I have no idea how I can use this statement to build a report.
    Anyone did this before ?

    Thanks

  2. #2
    Join Date
    Mar 2003
    Posts
    130
    You can't just create a report on the fly, you have to design it and set
    it's rowsource to the SQL statement you have. You'll have to make sure
    the form is open before you run the report if it needs the forms fields.

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by wey97
    You can't just create a report on the fly, you have to design it and set
    it's rowsource to the SQL statement you have. You'll have to make sure
    the form is open before you run the report if it needs the forms fields.
    If you want to creat a form on the fly, you can do it, by creating the Controls (mailnly text boxes and labels) using CreateControl

  4. #4
    Join Date
    Mar 2003
    Posts
    130
    but artemide asked about a REPORT not a form.

    See if this code helps:
    Code:
    Dim rpt As Report
    Dim txt As Control
    Dim lbl As Control
    
    Set rpt = CreateReport
    
    Set txt = CreateReportControl(rpt.Name, acTextBox, , , "FieldSource")
    
    Set lbl = CreateReportControl(rpt.Name, acLabel, , , "Label", 1000)
    
    rpt.RecordSource = "" ' set this to your SQL statment
    
    DoCmd.OpenReport rpt.Name, acViewPreview
        
    'DoCmd.Save ' if you want to save the report

  5. #5
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by wey97
    but artemide asked about a REPORT not a form.

    See if this code helps:
    Code:
    Dim rpt As Report
    Dim txt As Control
    Dim lbl As Control
    
    Set rpt = CreateReport
    
    Set txt = CreateReportControl(rpt.Name, acTextBox, , , "FieldSource")
    
    Set lbl = CreateReportControl(rpt.Name, acLabel, , , "Label", 1000)
    
    rpt.RecordSource = "" ' set this to your SQL statment
    
    DoCmd.OpenReport rpt.Name, acViewPreview
        
    'DoCmd.Save ' if you want to save the report
    I think (hope) I'm making progress.
    But I still need some more advice.

    This is my code so far :
    Code:
    Dim myReport As Report
    Dim myControls(20) As Control
    Dim controlnumber As Byte
    Dim strTemp as String
    Dim strSQL as String
    
    strSQL = "SELECT field1, field2, field3, field4 FROM myTABLE WHERE field1 = "condition1" AND field2 = true GROUP BY field3 ORDER BY field4 "
    
    Set myReport = CreateReport
        
    controlnumber = 0
        
    rsTemp = returns the fieldnames I want to include in my report --> in this case field1, field2, field3, field4
    
        rs.Open strTemp, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    
    For every field I want in my report I create a new control        
    Do While Not rs.EOF
    &nbsp Set myControls(controlnumber ) = CreateReportControl(myReport .Name, acLabel, acDetail)
    &nbsp controlnumber = controlnumber + 1
    &nbsp rs.MoveNext
    Loop
            
    rs.Close
    
    myReport .recordSource = strSQL
    DoCmd.OpenReport myReport .Name, acViewPreview
    A report is created, but it remains blank.
    Do I have to assign field1 to myControls(0), field2 to myControls(1), field3 to myControls(2) and field4 to myControls(3) ?
    If yes, how do I do this?
    What else should I do to make sure the data is grouped by field3 and ordered by field4?


    Thanks

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by wey97
    but artemide asked about a REPORT not a form.

    See if this code helps:
    Code:
    Dim rpt As Report
    Dim txt As Control
    Dim lbl As Control
    
    Set rpt = CreateReport
    
    Set txt = CreateReportControl(rpt.Name, acTextBox, , , "FieldSource")
    
    Set lbl = CreateReportControl(rpt.Name, acLabel, , , "Label", 1000)
    
    rpt.RecordSource = "" ' set this to your SQL statment
    
    DoCmd.OpenReport rpt.Name, acViewPreview
        
    'DoCmd.Save ' if you want to save the report
    Sorry, about the fomr but he can use the CreateReportControl as you explained

  7. #7
    Join Date
    Mar 2003
    Posts
    130
    Originally posted by artemide
    I think (hope) I'm making progress.
    But I still need some more advice.

    This is my code so far :
    Code:
    Dim myReport As Report
    Dim myControls(20) As Control
    Dim controlnumber As Byte
    Dim strTemp as String
    Dim strSQL as String
    
    strSQL = "SELECT field1, field2, field3, field4 FROM myTABLE WHERE field1 = "condition1" AND field2 = true GROUP BY field3 ORDER BY field4 "
    
    Set myReport = CreateReport
        
    controlnumber = 0
        
    rsTemp = returns the fieldnames I want to include in my report --> in this case field1, field2, field3, field4
    
        rs.Open strTemp, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    
    For every field I want in my report I create a new control        
    Do While Not rs.EOF
    &nbsp Set myControls(controlnumber ) = CreateReportControl(myReport .Name, acLabel, acDetail)
    &nbsp controlnumber = controlnumber + 1
    &nbsp rs.MoveNext
    Loop
            
    rs.Close
    
    myReport .recordSource = strSQL
    DoCmd.OpenReport myReport .Name, acViewPreview
    A report is created, but it remains blank.
    Do I have to assign field1 to myControls(0), field2 to myControls(1), field3 to myControls(2) and field4 to myControls(3) ?
    If yes, how do I do this?
    What else should I do to make sure the data is grouped by field3 and ordered by field4?


    Thanks
    for CreateReportControl you need to specify acTextBox instead of
    acLabel and you need to set the source of each TextBox to the field you
    want to show.

    CreateReportControl(rpt.Name, acTextBox, , , "FieldSource")

    Why in the heck don't you just use a wizard or design the report instead
    of using code to build it?

  8. #8
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Creating Report

    Originally posted by artemide
    I made a form with a lot of controls.
    Depending on the values of these controls I create a SQL-statement.

    Now I want to generate a report based on this SQL-statement.

    for example :

    strSQL = "SELECT field1, field2, field3, field4 FROM myTABLE WHERE field1 = "condition1" AND field2 = true GROUP BY field3 ORDER BY field4 "

    But now I got stuck: I have no idea how I can use this statement to build a report.
    Anyone did this before ?

    Thanks
    it is easire and quicker to produce a series of report templates and use those instead of trying to create them on the fly. It would require a lot of coding to produce formatted reports. You can hide controls and sections on a report on the fly, which should reduce the number of templates required.

    You could also use Excel as a reporting tool - just export the data. With the Excel object model you could easily format the spreadsheet on the fly

  9. #9
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by wey97
    for CreateReportControl you need to specify acTextBox instead of
    acLabel and you need to set the source of each TextBox to the field you
    want to show.

    CreateReportControl(rpt.Name, acTextBox, , , "FieldSource")

    Why in the heck don't you just use a wizard or design the report instead
    of using code to build it?

    The database is used by 50 users and each of them needs different data, so they have to be able to create their own reports showing only the data they need.
    Using the Access-wizard you can create reports group and sort the data, but as far as I noticed it is not possible to filter the data (using conditions).
    Or am I wrong?

  10. #10
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by artemide
    The database is used by 50 users and each of them needs different data, so they have to be able to create their own reports showing only the data they need.
    Using the Access-wizard you can create reports group and sort the data, but as far as I noticed it is not possible to filter the data (using conditions).
    Or am I wrong?
    Use the folllowing command to filter the recordset for a report:

    DoCmd.OpenReport ReportName:="MyReportName", View:=acViewPreview, WhereCondition:="MyFieldName = MyCriteria"

    This way the same report can be used by any one by just filtering the data accordingly. The WhereCondition property is the same as the Where criteria in a SQL statement by without the word Where

  11. #11
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by justin_tighe
    Use the folllowing command to filter the recordset for a report:

    DoCmd.OpenReport ReportName:="MyReportName", View:=acViewPreview, WhereCondition:="MyFieldName = MyCriteria"

    This way the same report can be used by any one by just filtering the data accordingly. The WhereCondition property is the same as the Where criteria in a SQL statement by without the word Where
    I used your method and it works fine, except I'm not able to group and sort the data as I desire.
    Is there any way I can do this on the fly?
    Isn't there anything like SortCondition or GroupCondition I can add to the DoCmd.OpenReport - statement ?

    Thanks

  12. #12
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by artemide
    I used your method and it works fine, except I'm not able to group and sort the data as I desire.
    Is there any way I can do this on the fly?
    Isn't there anything like SortCondition or GroupCondition I can add to the DoCmd.OpenReport - statement ?

    Thanks
    If you create the report with all the groups and sorts that you are likely to need, you can then turn them on/off as you require by making the groups visible/invisible. You could do the same trick for controls on the report. You can do this from the report open event and the OpenArgs property (available from Docmd.OpenReport to set the property) of the report. An example of the code you could use would be:

    Private Sub Report_Open(Cancel As Integer)
    If Len(Me.OpenArgs) Then
    Select Case Me.OpenArgs
    Case "Report 1"
    ' Hide the first group header
    Me.Section(acGroupLevel1Header).Visible = False
    Case "Report 2"
    Me.Section(acGroupLevel1Header).Visible = True
    Me("MyReportControl").Visible=False
    End Select
    Else
    ' Open the report un altered
    End If
    ' Error Handler
    End Sub

  13. #13
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by justin_tighe
    If you create the report with all the groups and sorts that you are likely to need, you can then turn them on/off as you require by making the groups visible/invisible. You could do the same trick for controls on the report. You can do this from the report open event and the OpenArgs property (available from Docmd.OpenReport to set the property) of the report. An example of the code you could use would be:

    Private Sub Report_Open(Cancel As Integer)
    If Len(Me.OpenArgs) Then
    Select Case Me.OpenArgs
    Case "Report 1"
    ' Hide the first group header
    Me.Section(acGroupLevel1Header).Visible = False
    Case "Report 2"
    Me.Section(acGroupLevel1Header).Visible = True
    Me("MyReportControl").Visible=False
    End Select
    Else
    ' Open the report un altered
    End If
    ' Error Handler
    End Sub


    I've been trying and testing and now ...I got it (almost) all working.
    Based on a SQL-string I build a report on the fly.
    The user is able to select the fields he desires, to sort and even to group the data.
    But there's one small problem left.
    When I create a report and want to group the data, the records are not sorted, even if my SQL-string contains an ORDER BY-statement.
    How can I solve this one?

    Thanks

Posting Permissions

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