Populate unbound controls on report using VBA

  • Thread starter Thread starter Raas
  • Start date Start date
R

Raas

I need to create a report where some of my fields can be populated from a
recordset opened in code. I have no problem opening the recordset, reading
the records, etc., until I need to move the data from a recordset field to
the report.

A simple example (I hope): I have opened the recordset.
The unbound field on the report is: rptStudentId
The field in the recordset is: StudentID

I just need to move the StudentID to rptStudentID at various times and in
various sections (including the detail section) in the report.

I realize what you have here isn't the complexity of the report, but help in
getting the right code to move the data for printing will help me go on with
the rest of the report!

I need to be able to do this sort of populating in the Report Header, the
Student ID Header, the Class Header, the Submissions Header, the Detail
Section, and the Student ID Footer.

Basically, the entire report is going to be written from code. No, I'm not
a student and this is not an assignment, and yes, it needs to be done this
way rather than from bound records.

Thanks all!





Thanks!
 
Thanks! Already tried that, but for some reason it wouldn't take. I'm using
Windows 7 with Access 2007. Don't know if that's part of it or not.

I also tried it with my other beta software, Windows 7 and Office 2010. No
success there either.

I was afraid that was the answer. I even tried it in the Paint section.
 
Sorry, hit the enter too soon:

I keep getting a message that says I can't assign a value to this object.
 
Raas said:
I need to create a report where some of my fields can be populated from a
recordset opened in code. I have no problem opening the recordset, reading
the records, etc., until I need to move the data from a recordset field to
the report.

A simple example (I hope): I have opened the recordset.
The unbound field on the report is: rptStudentId
The field in the recordset is: StudentID

I just need to move the StudentID to rptStudentID at various times and in
various sections (including the detail section) in the report.

I realize what you have here isn't the complexity of the report, but help in
getting the right code to move the data for printing will help me go on with
the rest of the report!

I need to be able to do this sort of populating in the Report Header, the
Student ID Header, the Class Header, the Submissions Header, the Detail
Section, and the Student ID Footer.

Basically, the entire report is going to be written from code. No, I'm not
a student and this is not an assignment, and yes, it needs to be done this
way rather than from bound records.
Click to expand...


You can use the Format event procedure of the section
containing the unbound text box:

Me.rptStudentId = rs!StudentID

Creating an unbound report is a real pain and you lose some
features. Since you have used grouping, the report must
have a record source for at least the fields used for
grouping. OTOH, it the record source does not include at
least one detail field, the KeepTogether features can wreak
havoc on your code. I suspect that if the record source has
a detail field, it should be possible to join to the data in
your recordset and obviate the need for the recordset and
the associated code.
 
You're exactly right. The problem I'm trying to get around with this effort
is that when I run the report, I get the student information fine, then the
Class information works well, then the asssignments assigned work well, then
the submissions work well, and the totaling works along with grade
calculations, EXCEPT that if a student has turned in more than one submission
for an assignment, then the total points possible for the assignment get
added twice or three times, etc, but of course, the student only gets credit
for his/her total points, so an assignment that is worth 50 points could have
a student receiving 15 points on the first submission, as additional 20 on a
second submission, and then 15 points on the third submission for a total of
50 points, however the report totals up 50 points possible for each
submission for a total of 150 points, and of course the grade is wrong.

I posted this problem months ago, and just worked the final information by
hand, but I have over 400 students working on-line this semester, and it's a
pain to do by hand. No one every helped with the problem before, so I
decided to try to go another route to create the report.

I really only need the one problem solved. I would be glad to e-mail my
program to a VIP if they wanted to see exactly what I'm talking about.

You're all great to try to help, and I appreciate it!!!!
 
Raas said:
You're exactly right. The problem I'm trying to get around with this effort
is that when I run the report, I get the student information fine, then the
Class information works well, then the asssignments assigned work well, then
the submissions work well, and the totaling works along with grade
calculations, EXCEPT that if a student has turned in more than one submission
for an assignment, then the total points possible for the assignment get
added twice or three times, etc, but of course, the student only gets credit
for his/her total points, so an assignment that is worth 50 points could have
a student receiving 15 points on the first submission, as additional 20 on a
second submission, and then 15 points on the third submission for a total of
50 points, however the report totals up 50 points possible for each
submission for a total of 150 points, and of course the grade is wrong.

I posted this problem months ago, and just worked the final information by
hand, but I have over 400 students working on-line this semester, and it's a
pain to do by hand. No one every helped with the problem before, so I
decided to try to go another route to create the report.

I really only need the one problem solved.
Click to expand...


Have you tried grouping on the assignment and using the
group footer to total the points received from all the
submissions for the assignment? If you can do that, you
would not need to sum the assignment's possible points. If
you need a total of the possible points in a higher level
group, then use a running sum text box in the assignment
group footer.
 
Steve said:
As you imply, you need someone to take a look at your program. I provide
help with Access, Excel and Word applications for a modest fee. I would be
glad to take a look at your program and fix the problems for you for a
nominal fee. Contact me if you want my help.

Steve
(e-mail address removed)
Click to expand...


These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP
 
All,

What was needed was a subreport. There is 1 Assignment to many Submissions
and since each Assignment is worth so many points it could get the correct
total as long as the Submission table was included. Hope that made sense...
I believe for now this concludes this thread.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina Whipp said:
I have the database and am reviewing for FREE. Afterwards, I will write an
abbreviation of how the issue was solved for anyone still watching the
thread.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
Click to expand...
 
THIS IS WRITTEN TO THANK YOU FOR YOUR HELP!!!!!!!!

Marshall started the pointing in the right direction, but Gina took it one
step further. From the information she sent me, I was able to make three
changes to my control source query, then create another query (which Gina
worked out for me), and then use the new query as the control source for a
sub report.

The program works absolutely correctly now!!!!!

Again, thanks for the MVP work and for being patient enough with me that we
were able to finally resolve my dilemma.

At 65 + I'm still learning, and as Gina said: "I may become an Access
junky". Now if only I could find some way to learn VBA for Access, then she
may be a prophet!

Thanks All!
 
And THANK YOU ofr the kind words...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
Click to expand...

I stumbled across this newsgroup having a similar problem and wanted
to share my approach. The situation was I needed to conditionally
populate a text box on a detail section of a report with an indicator
(in this case two asterisks), to notate that certain detail records
stood out from others. Specifically, the goal was to examine a
company's country where they did business and provide a roster of
employees. Employees residing outside of the company's home country
of business where to be highlighted on the report. For instance:

Company Name and Location: ABC Company, New York, NY USA

Name Date of Birth City Country
** John M. 3/17/1946 Rome Italy
Joe T. 2/10/1965 Topeka USA

** Indicates member resides outside USA (footer notation)

My solution:
1) Create an unbound text box on the report to hold the asterisks or
hold an empty string. I called this text box txtCountryIndic.

2) Within the detail section of the report, create a second text box
who's control source is set to a function call. The function call
assigned to the text box's control source looked like this:

=CountryIndicator([txtCompanyHomeCountry],[txtEmployeeHomeCountry])

You can call this 2nd text box whatever you like since its only
purpose is to hold the code in its control source to pass to the
function. I use this indirect method since if I tried to do this in
txtCountryIndic, it would become a bound text box and I wouldn't be
ab;e to assign values to it.

3) Then I wrote a simple function that was:

Function CountryIndicator(strCompany As String, strEmployee as String)
As String

On Error GoTo CountryIndicator_Error

If strCompany = strEmployee Then
[Reports]![REPORTXXX]![txtCountryIndic] = ""
Else
[Reports]![REPORTSXXX]![txtCountryIndic] = "**"
End If

On Error GoTo 0
Exit Function

CountryIndicator_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure CountryIndicator of Module UDF"

End Function

Hope someone can get some mileage out of this. I am sure there is a
more elegant way of doing this, but this works for me.

- Dave Ferguson
Washington, DC
 
I stumbled across this newsgroup having a similar problem and wanted
to share my approach. The situation was I needed to conditionally
populate a text box on a detail section of a report with an indicator
(in this case two asterisks), to notate that certain detail records
stood out from others. Specifically, the goal was to examine a
company's country where they did business and provide a roster of
employees. Employees residing outside of the company's home country
of business where to be highlighted on the report. For instance:

Company Name and Location: ABC Company, New York, NY USA

Name Date of Birth City Country
** John M. 3/17/1946 Rome Italy
Joe T. 2/10/1965 Topeka USA

** Indicates member resides outside USA (footer notation)

My solution:
1) Create an unbound text box on the report to hold the asterisks or
hold an empty string. I called this text box txtCountryIndic.

2) Within the detail section of the report, create a second text box
who's control source is set to a function call. The function call
assigned to the text box's control source looked like this:

=CountryIndicator([txtCompanyHomeCountry],[txtEmployeeHomeCountry])

You can call this 2nd text box whatever you like since its only
purpose is to hold the code in its control source to pass to the
function. I use this indirect method since if I tried to do this in
txtCountryIndic, it would become a bound text box and I wouldn't be
ab;e to assign values to it.

3) Then I wrote a simple function that was:

Function CountryIndicator(strCompany As String, strEmployee as String)
As String

On Error GoTo CountryIndicator_Error

If strCompany = strEmployee Then
[Reports]![REPORTXXX]![txtCountryIndic] = ""
Else
[Reports]![REPORTSXXX]![txtCountryIndic] = "**"
End If

On Error GoTo 0
Exit Function

CountryIndicator_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure CountryIndicator of Module UDF"

End Function

Hope someone can get some mileage out of this. I am sure there is a
more elegant way of doing this, but this works for me.

- Dave Ferguson
Washington, DC
Click to expand...

Hi Dave,

I'm working on something similar... What if you wanted to call the values of strCompany or strEmployee based upon if one of them were null.

Don't exactly expect an answer through here but figured I'd give it a shot.

Thanks Guys
Luke
 
Back
Top