Results 1 to 12 of 12

Thread: Report question

  1. #1
    Join Date
    Sep 2006
    Posts
    34

    Angry Unanswered: Report question

    Hi I have a report which uses dlookup to get the data. I want to be able to show the word empty if there is no data for that textbox.

    also when I print/preview the report it appears three times. I will post the code below.

    Thanks

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim varx, varY, varZ As Variant
    Dim varpk As Variant
    Dim intX, num, number, intColumnCount As Integer

    '************* FIRST COLUMN A**********************

    intColumnCount = 12
    varpk = "a"
    intX = 1
    number = 1
    varpk = varpk & number


    For intX = 1 To intColumnCount

    varx = DLookup("[design_name]", "qrytest", "[Pocket] = '" & varpk & "'")
    varY = DLookup("[design_number]", "qrytest", "[Pocket] = '" & varpk & "'")
    varZ = DLookup("[size]", "qrytest", "[Pocket] = '" & varpk & "'")

    Me("AC" + Format(intX)) = varY
    Me("AD" + Format(intX)) = varx
    Me("AS" + Format(intX)) = varZ

    number = number + 1
    varpk = "a"
    varpk = varpk & number

    Next intX

    End sub

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    DLookups are jolly slow for this sort of thing - I would see if you can get this sort of logic into your query if at all possible.

    Regarding displaying a value if the DLoopUp returns NULL - check out the NZ() function.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2006
    Posts
    34
    I need to use dlookups, the Nz function was great thanks

    All I need to workout is why it is Printing/previewing 3 times ?

    James

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you sure you need to use dlookups? I've always found a way around them which was much faster (i.e. another table, etc.) Pootle is right in that you may find this chugging if you get into a few thousand records, especially on a report.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Sep 2006
    Posts
    34
    The report itself needs to be layed out in a fixed way. so there are unbound text boxes that the data goes into based on on cell reference system.

    ie the unbound text box ad1 contains the data with the matching pocket_ref field ad1.

    Hope that makes sense.

    James

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    When you say the report prints out three times - if you run the record source query does it look ok? Are you getting everything in triplicate?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by jamesf248
    The report itself needs to be layed out in a fixed way. so there are unbound text boxes that the data goes into based on on cell reference system.

    ie the unbound text box ad1 contains the data with the matching pocket_ref field ad1.

    Hope that makes sense.

    James
    Maybe I'm under-caffienated, but that sounds like the definitive scenario for using bound report fields?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Sep 2006
    Posts
    34
    the record source query looks fine

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    In regards to it printing 3 times, have you checked on your sorting/grouping? Perhaps something in that pop-up properties box is causing the 3 reports (i.e. Keep together, interval, etc.)
    Last edited by pkstormy; 09-13-06 at 16:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Sep 2006
    Posts
    34
    no cant see anything there ???

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You said the report comes up 3 times even in preview. Seems to me it's got to be something with the record source (although you've checked that and it only shows 1 record), or with the sorting and grouping. I'm out of ideas unless it's something to do with the page width being wider and somehow causing it to do 3 pages. Do you want to send an mdb with the recordsource and report and maybe 1 record of data to look at? Also, are you sure one of your dlookup's isn't possibly returning more records than it should (although I don't think that would matter)? You can throw in a few msgbox's on some of your values when the report opens to possibly see where the problem might be.
    Last edited by pkstormy; 09-13-06 at 18:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Sep 2006
    Posts
    34
    All sorted now. The problem was that the reports record source was set to a query, when it should have been set to nothing as dlookup was getting the data.

    Thanks for all your help

    James

Posting Permissions

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