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

    Text box stuck with the same ol' record

    I have a report whose Record Source is set to a query(qryProdGio) through code(see it below). If you opened the report, you'd see that if I keep the fields separated, the records from mediegio are displayed correctly but if I try to combine them in a single text box(Produzgio), it keeps on displaying the first record.
    Can anyone tell me what's wrong?
    Note: I wanted to post the relevant mdb file too but its size is 153k so if anyone cares to receive it, by all means drop a line <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>
    Here's the code of the report module:
    Option Compare Database
    Option Explicit
    Dim cnn As ADODB.Connection, cat As New ADOX.Catalog
    Private Sub Report_Activate()
    Set cnn = CurrentProject.Connection
    Dim cmd As New ADODB.Command
    cat.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "SELECT anno, mese, giorno, " & Forms!mask1!List11 & " as selectedfield, selectedfield/24 as powerday FROM mediegio"
    With cat.Views
    .Item("qryProdGio").Command = cmd
    End With
    Set cmd = Nothing
    Reports!prodgio!Produzgio = anno & " " & mese & " " & giorno & " " & selectedfield & " " & powerday
    End Sub

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Text box stuck with the same ol' record

    Hi,
    Given that you're always returning the same fields to the report, you can just set the control source of the text box to =[anno]& " "&[mese]&" "&[giorno]&" "&[selectedfield]&" "&[powerday] in design view and it should contain the correct data.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    You've done it again!

    Though I can't make out why the same expression used in code doesn't give an equal result.
    You see I created the powerday field in the Sub Report_Activate().
    How can I format the field to show two decimals?

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

    Re: Text box stuck with the same ol' record

    I've solved the format problem by putting
    =[Anno] & " " & [Mese] & " " & [Giorno] & " " & [selectedfield] & " (" & Format([powerday],"#.00") & " kW)"
    in the text box control source but, in this way, since the [selectedfield] field, which is numeric, doesn't always have the same number of digits in its value, moving from one record to the next, the report's detail section shows records in which the values in [selectedfield] and [powerday] are not lined up from one record to the next .
    How can I make the report show field values in neat columns without resorting to keeping each field in its own text box?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Text box stuck with the same ol' record

    Can I ask why you don't want them in separate textboxes? If your [selectedfield] can be of varying length then I don't think you can do it with one textbox unless you format the control with padding zeroes (i.e. you might end up with 00000102.40000 to accommodate all possibilities). Alternatively you could use 3 text boxes - 1 that concatenates the year, month and day; 1 for [selectedfield] and 1 for powerday and kW.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: You've done it again!

    This is an answer to this post and to the email you sent me with a sample of the report. Your problem, of course, was that you had an unbound textbox in the report, populated from code, so its control source was static. Once you put the fields themselves into the controlsource of the textbox, it could respond to the changes in records in the report. The simple answer to your powerday question, is don't create. Instead, make your textbox controlsource read like this:
    <hr>=[anno] & " " & [mese] & " " & [giorno] & " " & [selectedfield] & " " & " (" & Format(([selectedfield]/24),"#.00") & " kW)"<hr>
    Charlotte

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

    Re: You've done it again!

    Thanks Charlotte(and Rory).
    Let's say I put another text box immediately to the right of the text boxes already in the detail section. This text box should show the message, "less than X kW"(where X is an arbitrary value I set by typing it in a text box on a form) whenever the value of [powerkey] falls below X. The table Mediegio, which through qryProdGio feeds data to the text boxes in the detail section, actually is made up of more than a thousand records, so this event(falling below X) is bound to happen many times if I set X low enough. Well, because of this and any time the event occurs, I'd like the actual message to read, "less than X kW n.Y" where Y is incrementing each time the event occurs.
    Two problems:
    How can I make the text box visible only when [powerkey] falls below X and how can I count the times the event occurs and display it in the text box?

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

    Errata Corrige

    I'm sure you've already realized it but just to be on the safe side:
    the field is [powerday] not [powerkey](fourth line of my message) <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

Posting Permissions

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