Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    report values in form (97)

    Hi, nice easy for you guys....
    I have a report based on a query that needs two values - week1 and week2
    I want the user to have four buttons to request quarterly reports (1-13,14-26,27-39,40-53) I have the buttons but cannot get the report to stop asking me for the [week1] and [week2] values in the query.
    I have tried the method of [fieldname] = [controlonform] that online help suggests , on a query with no prompts, but this returns an error as being unable to find the [fieldname] in the table ! <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>
    Any suggestions greatly received

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

    Re: report values in form (97)

    I assume you're using a form to enter the criteria for a query. Name your controls on the form something like txtWeek1 and txtWeek2. Then in the criteria for your query you can enter [Forms]![FormName]![txtWeek1] and [Forms]![FormName]![txtWeek2]


    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report values in form (97)

    Mark, I was hoping that all the user will do is click on one of 4 buttons. I should be able to get VBA to handle the change of value according to which button is pressed.
    Changing the query is not possible in so far as it supports several other forms, reports etc. Surely I can get VBA to supply the values required rather than leave the value in a form somewhere?

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

    Re: report values in form (97)

    Ahhh.... In that case you might want to use the Tag property of the form. When the uesr clicks the desired button, set the tag property to whatever you want. (Remember, the tag is a string even if it contains numbers You may need to use CInt() or CLng() to convert the string tag value to an integer or long depending on what your query needs.)

    Then have the criteria in the query set to read the tag property from the form: [Forms]![FormName]![Tag]

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  5. #5
    Star Lounger
    Join Date
    Sep 2001
    Location
    SC, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report values in form (97)

    You can try this: Instead of one report, make three more copies and alter the copy names by adding Qtr1, Qtr2, Qtr3, and Qtr4. Let all four reports point to the same query, but alter the week fields on the reports so that they have a default value of the appropriate week( you may need to use a select statement, where week1=1, week2=13 (for Qtr1)). I am at home and I can't remember exactly what you need to do, but I've done this before and I'm positive this will work. Also, remove the parameters in the query. Let your four buttons open the reports. This way, the user won't be asked for a parameter.

  6. #6
    New Lounger
    Join Date
    May 2002
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report values in form (97)

    I assume you have a docmd.openreport in the Onclick event for the command button. If so, the simplest thing to do is to add a "where" clause at the end of the docmd.

    Docmd.openreport "rptMyReport",acViewPreview,,"[Date] between #01/01/2001# and #03/31/2001#"

    Note that there are two commas between the acViewPreview and the where clause.

Posting Permissions

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