Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking up a table field in a report (2000)

    If you select January 3, 2004 on the calendar in the startup form in the attached mdb, you can see that the CODF combo box diplays the value of the DESCR field in the FERMI table relevant to the selected date. I'm trying to do the same in the CODF text box in the RDezzo report by using
    =DLookUp("descr","fermi")
    but it doesn't work because it always displays the same value no matter what date is selected in the calendar. How can I do it?
    Attached Files Attached Files

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

    Re: Looking up a table field in a report (2000)

    The same way: use a combo box with Control Source set to (I presume) G1DEZ.CODF, Row Source set to Fermi, Column Count set to 2 and Column Widths set to 0;1 (or something like that)

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

    Re: Looking up a table field in a report (2000)

    On Dezzo, the function Fermo is called with the value of Giorno in the subform; this is linked to CGior on the main form and through this to the calendar control. Even if the subform has no existing records, the value of Giorno is still set through the link with the main form (this value will be used as soon as you enter data in the subform.)

    From GioRep, you open the report with a filter (where-condition). There are no records for May 6, 2004, so there is no value for Giorno, hence the error No Current Record. Passing a where-condition to a report is different from linking a main form and subform.

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

    Re: Looking up a table field in a report (2000)

    Ciao Hans,
    I don't understand why if I put a breakpoint in the Fermo function in Module1 and select, say, May 6, 2004 on the Dezzo form calendar, the value of the gio argument in the Fermo function is 5/6/04 while if I select the same date on the GioRep form calendar and click the ANTEPRIMA REPORT button and hover the mouse over the same gio argument in the Fermo function I get the message: NO CURRENT RECORD.
    I mean, the record source of the MG1Dez form is the G1Dez table while the record source of the RDezzo report is query2 which is based on the G1Dez table so why the different behaviour?
    Attached Files Attached Files

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

    Re: Looking up a table field in a report (2000)

    The DiffLet function in the Module1 module which is used in the RDezzo report also uses the gio argument, how can I set the return value of this function to null in case there's no value for Giorno?

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

    Re: Looking up a table field in a report (2000)

    I'd like to avoid opening the RDezzo report when the date selected on the calendar on the GioRep form of the attached mdb doesn't have a corresponding record in the RDezzo report record source so I think I could insert something like
    <font color=red>if Format(Me.CGior, "mm/dd/yyyy") not in(select giorno from g1dez) then
    msgbox "no data available for the selected date"
    exit sub
    end if</font color=red>
    in the click event for the Comando130 button on the GioRep form
    but it doesn't work, how can I achieve it?
    Attached Files Attached Files

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

    Re: Looking up a table field in a report (2000)

    You can use the filter in combination with DCount:
    ...
    strFilter = Me.Filter
    If DCount("*", "G1Dez", strFilter) = 0 Then
    MsgBox "No data available for the selected date", vbInformation
    Exit Sub
    End If
    ...

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

    Re: Looking up a table field in a report (2000)

    Thank you Hans,
    I don't understand why, only when I select Jan 10, 2004 on the calendar on the GioRep form and press the Comando130 button, I still get the "No current records" message and this in spite of the fact that the G1Dez table has indeed a record for Jan 10, 2004. Can you make that out?
    Attached Files Attached Files

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

    Re: Looking up a table field in a report (2000)

    G1Dez does contain a record for January 10, but the record source Query2 of the report doesn't. This is because Query2 is based on G1Dez, G2Dez and G3Dez joined on Giorno. G2Dez and G3Dez do not contain a record for January 10. You might check if Query2 contains a record for the selected day, instead of G1Dez.

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

    Re: Looking up a table field in a report (2000)

    Thanks,
    When I select, say, Jan 3, 2004 on the calendar on the GioRep form and press the Comando130 button, an input box pops up asking for the value of the G1DEZ parameter. I've noticed that if I delete the REND text box from the RDezzo report the input box doesn't come up. The control source of the REND text box is
    =Rendi([Energia],[PORT1],[Pmedia],G1DEZ.salto)
    I don't see what could be wrong with that, do you?
    Attached Files Attached Files

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

    Re: Looking up a table field in a report (2000)

    In the record source of the report, you return LETTUR from each of the tables, so the field names are G1DEZ.LETTUR, G2DEZ.LETTUR etc. But you only return Salto from G1DEZ, not from the other tables. So the field name for Salto is simply that, not G1DEZ.Salto. You can verify this by opening Query2, or by looking at the Field List when the report is open in design view.

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

    Re: Looking up a table field in a report (2000)

    Hans, I forgot to add another table(PORTDEZ23) to Query2, you can find it in the attachment. You can see that now both Query2 and the field list window name the field as G1DEZ.salto.
    So now
    =Rendi([Energia],[PORT1],[Pmedia],G1DEZ.salto)
    should be fine but it isn't because I still get the input box popping up asking for the value of the G1DEZ parameter when I select, say, Jan 3, 2004 on the calendar on the GioRep form and press the Comando130 button.
    How can I solve it?
    Attached Files Attached Files

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

    Re: Looking up a table field in a report (2000)

    It also prompts for PORTDEZ23, but I can't find what causes it. Check the control source of all text boxes on the report very carefully. If you can't find the culprit either, it's probably best to re-create the report from scratch. Don't do it all at once, but test from time to time if it still works.

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

    Re: Looking up a table field in a report (2000)

    Don't you think that the cause of the input box popping up asking for the value of the G1DEZ is the Rend text box on the report? As as soon as you delete it the input box doesn't come up.

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

    Re: Looking up a table field in a report (2000)

    But I don't know why.

    Added: if I take out the Rend text box, I get prompted for Rend, G1DEZ and PORTDEZ23. I have to take out the whole block with the Rendimento and Coefficiente Energetico text boxes to get rid of the prompts. If I leave them in, and press OK in the prompts, all these display #NAME. Still don't know why - debugging doesn't help.

Page 1 of 2 12 LastLast

Posting Permissions

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