By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,232 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Populating report based on combo list selection

P: 68
I think I am making some mistake here but maybe someone can help find out the mistake. This is what I have:

Private Sub Report_Activate()
UpdateHeader
End Sub

Public Sub UpdateHeader()
Dim rs As New ADODB.Recordset
Dim c As ADODB.Connection

Set c = CurrentProject.Connection

Me.txtLevel = gLevel
'xMe.txtSession = gSession
Me.txtAcademicYear = gYear
Me.txtType = gType


'Get School Name
rs.Open "SELECT districtid,[school name] as schoolname FROM Schools WHERE code ='" & gSchoolCode & "';", c, adOpenDynamic, adLockReadOnly
Me.txtNameOfSchool = rs.Fields.Item("SchoolName").Value
Dim gdistrictid
gdistrictid = rs("districtid")
rs.Close

'xstudentFilter = "[Students].[Level]='" & gLevel & "' AND [Students].[Session]='" & gSession & "' AND students.acaYear='" & gYear & "' and [students].[program type] = '" & gType & "' and students.code='" & gSchoolCode & "'"
studentFilter = "[Students].[Level]='" & gLevel & "' AND students.acaYear='" & gYear & "' and [students].[program type] = '" & gType & "' and students.code='" & gSchoolCode & "'"

'Get Total Student Count
rs.Open "SELECT COUNT(*) AS StudentCount FROM Students WHERE " & studentFilter, c, adOpenDynamic, adLockReadOnly
Me.txtNumberOfStudents = rs.Fields.Item("StudentCount").Value
rs.Close

'Get Male Student Count
rs.Open "SELECT COUNT(*) AS StudentCount FROM tblTeachersRegistration WHERE " & studentFilter & " and strGender='Male'", c, adOpenDynamic, adLockReadOnly
Me.txtMale = rs.Fields.Item("StudentCount").Value
rs.Close

'Get Female Student Count
rs.Open "SELECT COUNT(*) AS StudentCount FROM tblTeachersRegistration WHERE " & studentFilter & " and strGender='Female'", c, adOpenDynamic, adLockReadOnly
Me.txtFemale = rs.Fields.Item("StudentCount").Value
rs.Close

'Get County Name
rs.Open "SELECT County FROM Counties WHERE ID=" & gCountyID, c, adOpenDynamic, adLockReadOnly
If rs.EOF = False And rs.BOF = False Then Me.txtCounty = rs.Fields.Item("County").Value Else Me.txtCounty = "N/A"
rs.Close

'Get District
rs.Open "SELECT District FROM districts WHERE ID=" & gdistrictid, c, adOpenDynamic, adLockReadOnly
If rs.EOF = False And rs.BOF = False Then Me.txtDistrict = rs.Fields.Item("District").Value Else Me.txtDistrict = "N/A"
rs.Close

'Cleanup
Set rs = Nothing

End Sub

I would like to use DLookUp instead, to run this report.
Jun 28 '12 #1
Share this Question
Share on Google+
6 Replies


zmbd
Expert Mod 5K+
P: 5,397
Stoic...
First you need to by using the "code" tags around your posted code ( Posting Guidelines )

Your question:
Are you doing all of this in a report?
Aug 13 '12 #2

P: 68
Yes,I am. Maybe dlookup could be a shortcut to this.
Aug 13 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
OK,
The reason I'm a tad off on this is that normally you'd be using queries to pull the data you're after instead of pulling individual field information at runtime.

It would be helpful to understand what you're trying to achieve with the report. Where are the fields located within the report... header, detail, footer?

-z
Aug 13 '12 #4

P: 68
Ok zmbd. I have a table name tblSchoolInformation with fields (teacherName, gender, School); and I have a report that depend on the table. In this report, I have placed a text boxes with the same field names in the header. Now, I have a form with the same field names with a some combo boxes with drowdowns (school, gender, etc) and a button to generate the report. I would like to populate in the report, information after selecting the options from the combo so that the report generate the names of the teachers as per that school I have selected.

header:
School Name:
Total Male,
Total Female

Details:
Teacher Name Gender GradeLevel
XXXX XXX XXX
Aug 13 '12 #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
Stoic,

I can't agree with zmbd more..... Use the values of the controls on your form to generate a query that serves as the record source for your report. Saves a bunch of headaches trying to code multiple recordsets for a report. and allows you to see the results of your data set, as well.
Aug 13 '12 #6

zmbd
Expert Mod 5K+
P: 5,397
You can use a parameter based query that will use the selections in your comboboxs to filter. Once you have that, then you can can set the header/footer totals (several threads here in bytes about how to do this).

Step by Step for creating parmeter based queries using form data: http://office.microsoft.com/en-us/ac...010096314.aspx

This insight article might also be helpful, post back with any other questions http://bytes.com/topic/access/insigh...port-using-vba

I have another thread for the total's in the header... let me find it and I'll add it here for you to refer to... busy in the lab this morning. :)

{edit} well I can't find it right now in the time I have...
however, in the header section you can place an unbound text box and set the control source... for example I need the average of the results for a particular test in field [TheTestResult]... so in the header I inserted the text box and set the control source in the data-tab of the text box to:
Expand|Select|Wrap|Line Numbers
  1. =Format(Avg([TheTestResult]),"Fixed")
I could have used count or max or some other agregate function if I need that information.

-z
Aug 13 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.