Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't assign report RecordSource property via vba (2003)

    I'm trying to set the RecordSource property for the Report1 report in the attached mdb using the following code:

    Private Sub Report_Activate()
    Dim strNewRecord As String
    strNewRecord = "SELECT OreP.NOME, OreP.mese, OreP.anno, OreP.ore, ProdEnTotM." & _
    [Forms]![mseledata]![Mese] & ", ProdEnSGB." & [Forms]![mseledata]![Mese] & ", ProdEnPa." & _
    [Forms]![mseledata]![Mese] & ", ProdEnPp." & [Forms]![mseledata]![Mese] & _
    " FROM OreP, ProdEnTotM, ProdEnSGB, ProdEnPa, ProdEnPp " & _
    "WHERE OreP.mese=[forms]![mseledata]![mese] AND OreP.anno=[forms]![mseledata]![anno]"

    Me.RecordSource = strNewRecord
    End Sub

    but why does it give me the error message:
    <font color=448800>Run-time error '2191':
    You can't set the Record Source property in print preview or after printing has started.</font color=448800>
    <img src=/S/question.gif border=0 alt=question width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't assign report RecordSource property via vba (2003)

    Use the On Open event of the report instead of the On Activate event.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't assign report RecordSource property via vba (2003)

    Thank you Hans,
    Assigning the RecordSource via code prevents the Field list to be shown so how can I bind the various fields in the RecordSource result set to corresponding text boxes in the Detail section of the report?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't assign report RecordSource property via vba (2003)

    Temporarily set the record source of the report to an appropriate query. When you have set the control sources, you can clear the record source.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't assign report RecordSource property via vba (2003)

    Thank you,
    Please check Query1. The problem is using VBA you can dynamically pick the month associated with, say, the ProdEnTotM query according to what month you select in the startup form while you cannot do that in Query1, or can you?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't assign report RecordSource property via vba (2003)

    No, and that means that you cannot set the control source of controls in design view either. There are two ways to handle this:

    - Give the month fields from ProdEnTotM fixed aliases, so that they will have the same name in the query, whatever the month you select.

    or

    - Set the control source of the controls bound to the month fields in code, in the On Open event of the report.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't assign report RecordSource property via vba (2003)

    Hans,
    I suppose when you say "Give the month fields from ProdEnTotM fixed aliases", you mean something like this:
    SELECT OreP.NOME, OreP.mese, OreP.anno, OreP.ore, ProdEnTotM.[4] as a, ProdEnSGB.[4] as b, ProdEnPa.[4] as c, ProdEnPp.[4] as d
    FROM OreP, ProdEnTotM, ProdEnSGB, ProdEnPa, ProdEnPp
    WHERE (((OreP.mese)=[forms]![mseledata]![mese]) AND ((OreP.anno)=[forms]![mseledata]![anno]));
    the problem in this case is how do you tell Query1 that for example you've chosen month [3] in the startup form?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't assign report RecordSource property via vba (2003)

    You are setting the record source of your report in code. I thought you wanted to have a way to set the control source of the controls on the report in design view. You can use a query like the one you posted for that. If you set the same aliases in the record source in code, the controls will work.
    The alternative is to leave the control source of controls blank in design view, and to set both the record source of the report and the control source of the controls in code. You don't need aliases then, since you know which month the user has selected.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't assign report RecordSource property via vba (2003)

    Oh yes, I get you now, thank you so much Hans. You said, "so that they will have the same name in the query" so I thought you were talking about modifying Query1. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Since the sum of the 4 central columns in the report doesn't always equal the rightmost TOTAL column, I've tried to use the CInt function in the 4 invisible text boxes in the report to no avail, how can I make the sum equal the total?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't assign report RecordSource property via vba (2003)

    CInt converts a value to the nearest Integer, i.e. a whole number between -32768 and +32767. The values of pac, ppi, olm and sgb are already whole numbers, but they are much larger than 32767, so CInt returns an error.

    If it is really important to you that the text boxes Text11 through Text14 display whole numbers AND that the sum of the displayed numbers equals ore, you must save the result of the query in a temporary table, and use code that applies some kind of algorithm to decide which numbers must be rounded up and which must be rounded down in order to make the addition correct. For example, the values for Lazzarini Samuele are 10.40, 55.96, 59.30 and 43.34. If you want to round these to whole numbers and keep the sum equal to 169, you will probably want to round 10.40 to 11, even though it is nearer to 10 than to 11.

    It's easier to display pac etc. with one or two decimals, the sum will then be correct at the whole number level.

Posting Permissions

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