Change Control Source for field on a report

doulostheou

Registered User.
Local time
Today, 01:33
Joined
Feb 8, 2002
Messages
314
I thought this was going to be as easy as doing it on a form, but apparently I was wrong. I have a month end report. When you click the button to open it the "MonthSelect" form opens and asks you to select the appropriate month. When you click okay, the form opens. My problem is that it is partially based off a crosstab query. In this query there are values for each month, Scores.Jan, Scores.Feb, etc. I wanted to note which month was selected and change the controlsource of the field appropriately. I've done this in forms before, so I tried using the code:

If Forms!MonthSelect!Month="January" Then
Score.ControlSource = "Score.Jan"
Else If etc...

Apparently, the module was not recognizing Score (the name of my field in the report) and consequently would not let me asign a controlsource to it.

I then tried to manipulate at the query level. For one of my rows I typed

Score: "Score" & Left([Forms]![MonthSelect]![Month],3)

Unfortunately, this just returned the string "Score.Jan" and did not look up the appropriate value. Without creating six new queries (I simplified the names, there is actually a SOScore, SOOutof, SOCount, CRScore, CROutof, & CRCount), I am not sure where to go from here. Any suggestion would be appreciated.
 
If you set the criteria of the query to limit its output then any report based on the same query will also be restricted. Why do you need to change the record source?
 
I do not need to change the record source, The correct information is already in my query. I just wanted the field on my report to point to the proper field in the query depending on the month the user selected. In a form, I can simply change the control source of the field in question and it will pull up the appropriate information from the query. But the record source never changes.

I know I can setup new queries that will only pull data from the date range I specify, but this would require multiple queries to be created. I was hoping to pull the data from my existing queries.

I am not sure if I was clear on my actual setup. The query I presently have is based off of several queries and gives me information for different categories broken down by month (i.e. ServiceObserveScore.Jan, ServiceObserveScore.Feb, etc.; ServiceObserveOutOf.Jan, ServiceObserveOutOf.Feb, etc.; ServiceObserveCount.Jan, ServiceObserveCount.Feb, etc.; ClaimReviewScore.Jan, and so forth.) In my report, I only want to display data for one month at a time. It took several queries to get these different totals and I was hoping to avoid recreating all of them with a date range criteria. So I was looking for a way (either at the query level or report level) to make the report automatically target the appropriate month. For instance, if the month in question was Feberuary, it would pick ServiceObserveScore.Feb as opposed to ServiceObserveScore.Mar.

If I am still being unclear, please let me know. Thanks for all your help.

[This message has been edited by doulostheou (edited 02-18-2002).]
 
You wrote:

>>If Forms!MonthSelect!Month="January" Then
Score.ControlSource = "Score.Jan"
Else If etc...<<

Try:
...
Score.ControlSource = "=Score.Jan"
...

i.e. an equal sign between the open qoutation mark and the capital S.

HTH

Lyn
 
Thanks to everyone who tried to assist me with this. My coding was actually all correct. Apparently, I had a mistake in the query level (My names had gotten mixed up so the report couldn't find the correct name to associate with the Control Source). What threw me off though was that visual basic usually lists ControlSource as one of the options in the drop down list when you type the object name. For some reason, in the report this wasn't an option (I don't know if this problem is Access 97 specific or not). However, when I fixed the problem with the query, I was able to set the ControlSource value with no problems. Thanks again.
 
Ditto A2k. ControlSource was also not listed but I used the property anyway in a report using the above method and lo! it worked.

Lyn
 

Users who are viewing this thread

Back
Top Bottom