Results 1 to 9 of 9

Thread: Code ?

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code ?

    I'm sure others will have more to say, but the first thing I noticed is that you need to make sure your code assigns the variable to the function name:<pre>Public Function MyProject()<font color=red> as Variant</font color=red>
    Dim varX As Variant
    varX = DLookup("[ProjectNumber]", "tProjectInfo", "[ProjCode] = " & Reports!rTEST!ProjCode)
    <font color=red>MyProject = varX</font color=red>
    End Function</pre>


    Just a thought...

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code ?

    Hi Mark,

    Thanks for the suggestion, I applied it to my existing code and it still produces the same error message.

    Any other suggestions?

    Thanks in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code ?

    Hmmm..... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    I would check your Dlookup statement. I have often had problems getting the parameters of the Dlookup statement to work correctly when being pulled from a Form or Report control. To troubleshoot I usually open the form or report, then type the Dlookup statement into the Immediate Window (Ctrl+G):<pre>Print Dlookup("[WhateverField]","TableOrQuery","[Field]=" & Reports!ReportName!FieldName)</pre>

    You may have to modify the parameters until you find where the problem is.
    Hope this helps <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code ?

    Mark,

    If I put just the DLOOKUP function in the control source of a textbox in the report, it works fine...the report will return the values I am looking for.

    Thanks,

    Roberta
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Code ?

    You're trying to work with a report the way you would with a form, which often leads to errors. First of all, make sure your report controls do NOT have the same name as the underlying fields. Then, DLookup is a very slow method to use, and you haven't explained clearly what it's doing here anyhow. Why would you have two textboxes using the same formula to return a value? There isn't really any reason to repeat a value on a report, or if there is, calculate the value in the underlying query and simply bind both controls to that calculated field. What is in Reports!rTEST!ProjCode? Is that a control name or are you referring to the underlying field?
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code ?

    Good Morning Everyone,

    I have a rProjectInfo that I want to put 2 text boxes in. The recordsource for this report is qProjectInfo which is a crosstab query without any parameters. In a textbox I want to ....and I'm not sure how....have the text box call the following code:
    Public Function MyProject()
    Dim varX As Variant
    varX = DLookup("[ProjectNumber]", "tProjectInfo", "[ProjCode] = " & Reports!rTEST!ProjCode)
    End Function

    In the control source of tboxDescription I put the following:
    =MyProject()

    When I open the report I get the following error msg:

    Run Time error 64479
    The expression you entered as a query parameter produced this error'
    'The object doesn't contain the Automation object "R110501".

    End Debug

    "R110501" is a project number that is in both the report and the table (tProjectInfo).


    If I select End I get that error msg for each project code in the report. If I select Debug it goes to the 3rd line of code.

    What on earth am I doing wrong?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code ?

    Hi Charlotte,

    I never said I was using 2 textboxes with the same formula. What I said was "I have rProjectInfo with the recordsource as qProjectInfo which is a CROSSTAB query and a textbox that I want to put the following code in....."

    The reason I am attempting to apply MyProject() to ONE of the textboxes is because I can't get the Project Description information in the crosstab query but I need to include it in in my report.

    Once I have this working properly, I will build another one to get the Name of the Client and will put that in the OTHER text box.

    Thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Code ?

    This
    <hr>I have a rProjectInfo that I want to put 2 text boxes in<hr>
    is what confused me. Why can't you get the project information in the crosstab query?
    Charlotte

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code ?

    Hi Charlotte,

    I didn't think I could...when I tried it before, it didn't return any values. I just tried it again and made it a row heading and show first and it works fine.

    Thanks for the suggestion.

    I am however, still curious on how to make my original post work...as I do have other uses for it.

    As I mentioned earlier, if I put the DLOOKUP function in the control source of a txtbox, the report opens fine and with the correct data. This tells me it is not the dlookup function that is incorrect, rather something in my code.

    Any suggestions?

    Thanks in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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