Referencing Text Boxes in a Report and getting "Enter Parameter Value" (1 Viewer)

[jdunca4]

I have a report and within the report I have added some text boxes with some simple logic such as sum, count, etc. These text boxes function well as long as the logic is referencing exisiting fields. The minute I try to have a text box reference another text box I get the "Enter Parameter Value" box pop up. I don't understand this as the text box I am referencing has a vaule based on what it is referencing.

Example: 1st text box control source =Sum([existingfield]) I get a value.
2nd text box control source =[existingfield2]/[1st text box] I get "Enter Parameter Value" of 1st text box.:banghead:

Any help on this would allow me to complete my project. Thanks.

 

[JHB]

This kind of error mostly appear when a control/field name is unknown and it is really sensitive about it, a space in front of the control name, (like in the picture below is enough), and it can be difficult to recognize.

Double check if the names of the text boxes really exist in the report.
Else post a stripped version of the database with some sample data, (zip it).

 

[jdunca4]

I am working on zipping it. I don't know what to strip that will allow it to still function properly. I checked the names and they seem to be good. I don't know if this matters but the situation I described is a text box in the Report header referencing a text box in another header of the same report.

 

[JHB]

I am working on zipping it. I don't know what to strip that will allow it to still function properly.

Okay - if it isn't to big, then zip and send the whole database.

 

[jdunca4]

So I compacted and repaired the database and it brought it from 1.8GB to 6MB then zipped it and now it is at ~500kB. I checked and it seems to be working fine but it is confusing me that it dropped so much maybe another convo on why that is.

So if it will open up it should open at a form (Report Generator) this form is used for filtering the data and producing a table and a report. So to make if go quicker select TX for state and click the button labeled "Design". This should give you about 4000 rows in the table and then the report will open after. In the report header I am totaling information "Yes'", "No's", "Points", etc. The yes' and no's work fine but the points and everything after is where my problem rises. When you look at the table produced via the query from the button selection you will see that every row has a Point and WR. If I strictly sum them or put a total at the footer I get the ~4000 but in reality there is one point per "X" number of questions. So I need to figure out how to get the "Points" total to give me 4000/X. That is easy but based on the form filter the query can produce different values for X there inlies the problem. Let me know if you need additional info. If I can get the referencing text box issue resolved I think I can solve the problem with IIF statements in text boxes. I want to avoid further queries, etc. Thanks for any help.

 

[JHB]

So I compacted and repaired the database and it brought it from 1.8GB to 6MB then zipped it and now it is at ~500kB. I checked and it seems to be working fine but it is confusing me that it dropped so much maybe another convo on why that is.

It is normal, you've to compact and repair the database regularly when you run queries with big amount of data.

So if it will open up it should open at a form (Report Generator) this form is used for filtering the data and producing a table and a report. So to make if go quicker select TX for state and click the button labeled "Design".

Do you mean "Design Ans. Query", else I can't find it.

This should give you about 4000 rows in the table and then the report will open after. In the report header I am totaling information "Yes'", "No's", "Points", etc. The yes' and no's work fine but the points and everything after is where my problem rises. When you look at the table produced via the query from the button selection you will see that every row has a Point and WR.

Do you mean "WRNumber" and "NoOfPoints", you need to be very clear when you refer to something, else I'm in doubt if I'm looking on the right thing or I can't find it!
You know you database I do not, therefore!

If I strictly sum them or put a total at the footer I get the ~4000 but in reality there is one point per "X" number of questions. So I need to figure out how to get the "Points" total to give me 4000/X. That is easy but based on the form filter the query can produce different values for X there inlies the problem.

I can't see you have put anything in the footer, so ... ?
Can you give me some light on this?

You wrote in post #1

Example: 1st text box control source =Sum([existingfield]) I get a value.
2nd text box control source =[existingfield2]/[1st text box] I get "Enter Parameter Value" of 1st text box.

I don't get that error! Which field are you actually summing and which control do you have "control source =[existingfield2]/[1st text box]"?

 

[jdunca4]

Gotcha, so open the Form "Report Generator". Using the "States" drop down select "TX" leave everything else blank. Click the button "Design Ans. Query". After the query runs a table and a report will open. The report that opens is "Design Report". In the blue header you will see totals Yes=3976, No=2, Points=153, WR's=Blank, Fail=Blank, Pass=Blank. Then below that you will see other information. When the query is run as stated the value 153 in the "Points" text box is accurate as is the value in the "Yes" and "No" text boxes the others are blank and hopefully the help I am requesting will fix those. If you were to rerun this same query only selecting "TX" for States and then selecting "2" for the "Design/Construction Answers" drop down on the form "Report Generator" and clicking "Design Ans. Query" button you will get values in the "Design Report" header of Yes=0, No=2, and Points=1. Here is my problem.

When running this or my other queries without additional filters the simple equation in the "Points" text box =IIf([DYes]+[DNo]/26) provides the correct value 153. But if you add the additional filter such as selecting "2" for the "Design/Construction Answers" this equation is no longer valid as you will get 0+2/26=.07 points. The current equation I have in the "Points" text box is =IIf([DYes]<>0,([DNo]+[DYes])/26,[DNo]/(Count([NoOfPoints]))) this equation again works when no additional filter is applied but provides an answer of "1" when the other filter is applied.(i.e. 2/2=1).

Go back to the form and change "States" to "LA" and leave "Design/Construction Answers" set at "2". This query will give you a better idea of my overall issue. The totals will be Yes=0, No=10, Points=1 and the rest blank. I feel I can get an equation to work properly if I attempt to reference another text box within this report.This is the equation I attempted to put in the "Points" text box when I began recieving the "Enter Parameter" issue. =IIf([DYes]<>0,([DNo]+[DYes])/26,Count([DPF])). The answer should be 7 but it wants me to "Enter a Parameter". If I were to use the equation =IIf([DYes]<>0,([DNo]+[DYes])/26,Count([NoOfPoints])) I don't have to "Enter a Parameter" but the answer is 10 as there is a point associated with each row of the query.

I am basically trying to get the "Points" to equal the true number of points that show up on the report regardless of the amount of rows that are produced from the query. In the property sheet of [NoOfPoints] I have duplicates hidden so I understand the program is doing what I am asking by counting the true number of points which is the quantity of rows produced but in this particular query each point could have from 1-26 rows depending on the data thus I need some equation that will allow me to take [NoOfPoints] and divide by the moving target 1-26.

 

[JHB]

Okay - I've made a solution for you in the attached database, see if you can use it, then we can discuss it afterwards.
You can't count [DPF] because it is only a control on the report, with no connection to the underlying query. Ie. you can not count how many times a control appears in a report/form.

 

[jdunca4]

Perfect it is working. Thank you. And thanks for not looking at my database and saying what the heck is he doing. lol.

Questions: I understand you can't count the number of times a control appears in a report or form but how or can you just count the value within that control? (i.e. [DPF].value instead of just [DPF] or something along those lines?
Are all 3 new queries required as it seems you are only referencing query 1 in the text box?
Is there a way to avoid the code you used to get the value to show up in the text box such as having the text box reference the count of query 1?
In the code instead of the SQL of query 1 can you just say query 1?
What is the If statement saying in laymans terms? (i.e. if recordset is not at end of file then move to last of recordset? what is the last value? I ask because you set the text box to rst.recordcount can the If Not statement be excluded and jump straight to setting the text box to rst.recordcount?)
Is me.mytextbox the syntax everytime to declare a control and then set it to some value?
Will I need a query or 3 for each of the "totals" text boxes shown in each of the four reports I have?
Other than these questions I think I have it and can reproduce.

Another topic is I am in the process of converting this database to a web database though this works in a regular database the distinct function is not available, thoughts?

 

[JHB]

Perfect it is working. Thank you. And thanks for not looking at my database and saying what the heck is he doing. lol.

You know best what you are doing, so no comments!

Questions: I understand you can't count the number of times a control appears in a report or form but how or can you just count the value within that control? (i.e. [DPF].value instead of just [DPF] or something along those lines?

No it will not work, the only other way I can see right is another query like the one I've use with the recordset, and then combine the two, (but you write no more queries so ..., but sometimes you need to review your claim. )

Are all 3 new queries required as it seems you are only referencing query 1 in the text box?
Is there a way to avoid the code you used to get the value to show up in the text box such as having the text box reference the count of query 1?
In the code instead of the SQL of query 1 can you just say query 1?

Which other all 3 new queries required???
About avoid the code, look at my answer above.

What is the If statement saying in laymans terms? (i.e. if recordset is not at end of file then move to last of recordset? what is the last value? I ask because you set the text box to rst.recordcount can the If Not statement be excluded and jump straight to setting the text box to rst.recordcount?)

Look at the comments insert in the code below.

'If no record return by the query sting in the OpenRecordset("SELECT DISTINCT...", then rst is EOF (end of file).   
If Not rst.EOF Then
  'Record(s) is/are return then move to the last record, (if no record returned an error would raise, therefore the IF statement.).      
    rst.MoveLast
   'Now set the value in control on the report to the count of record the rst returned. 
    Me.Text61 = rst.RecordCount
  End If

"Me" refer to the Report/(Form) object, it is more flexible to use Me as:
[Reports]![ReportName].[Report]![ControlName]
Because if you rename the report the above line would cause an error.

Is me.mytextbox the syntax everytime to declare a control and then set it to some value?

Not declare, but refer.

Will I need a query or 3 for each of the "totals" text boxes shown in each of the four reports I have?
Other than these questions I think I have it and can reproduce.

It depend on how you calculate them?

Another topic is I am in the process of converting this database to a web database though this works in a regular database the distinct function is not available, thoughts?

Here I can't give you a clear yes/no, the databases I work with on the Internet is based on ASP, VB/Java-Script and here it would be possible to get hold of the value .

 

[jdunca4]

Which other all 3 new queries required???[/Quote]

In the database you sent back to me it had 3 new queries, query 1, query 2, and query 3 but you only refernce query 1 in the code for the text box.


Here I can't give you a clear yes/no, the databases I work with on the Internet is based on ASP, VB/Java-Script and here it would be possible to get hold of the value .[/QUOTE]

How do I determine what my database is based on such that I can know if this will work?

Again thanks for all your help.

 

[JHB]

Sorry for that - you can delete the 3 queries I've only used them for checking purpose and for getting the right syntax.

How do I determine what my database is based on such that I can know if this will work?

Only advice I can give, convert the database to a web database and see if and how much it will works.