report values in form (97)

Topic

New Reply

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 !
Any suggestions greatly received

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote