473,325 Members | 2,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Populating report based on combo list selection

68 64KB
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
6 1705
zmbd
5,501 Expert Mod 4TB
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
Stoic
68 64KB
Yes,I am. Maybe dlookup could be a shortcut to this.
Aug 13 '12 #3
zmbd
5,501 Expert Mod 4TB
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
Stoic
68 64KB
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
3,653 Expert Mod 2GB
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
5,501 Expert Mod 4TB
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

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

Similar topics

3
by: sao | last post by:
I am currently using Access 2000. In my table it is the following fields that are set up: 1 - Staff Name 2 - Department This table is already populated with 100+ records (staff along with...
3
by: Richard | last post by:
Hi, I have a form based on a table. When I filter the form I want to run a report based on the same table with the same filter as the form. No problem until I want to filter a combo box where...
9
by: DeeMing | last post by:
I have created a form in Access, similar to a switchboard in which I want to print reports, and I can do this. However, I want to print based on particular parameters; i.e. I have 2 combo boxes on...
2
by: jonosborne | last post by:
Hi, i have literally been racking my brain about this one and i think i need some help. I have a report which is produced when a button on a form is pressed. The report has many fields and records...
5
by: jonosborne | last post by:
Hi, i have managed to filter a report based on selections made in a list box but am totally confused with a message box that appears everytime i run my report. Let me explain (i apologise for...
29
by: Rickster66 | last post by:
Hey there, I have a reporting issue and any help is well appreciated. I have been requested to create a report based on cells (defined a groups of workers). So far, so good. The report is be be...
5
by: Andrew Meador | last post by:
I have a form (Change Card List by Status) with a check box (cboNOT) and a list box (lstStatus). There is an Open Report button that opens a report (Report - Change Card List) which uses a query...
0
by: frankiefrank | last post by:
Hello, I have an aspx page with a RadioButtonList that has two Items, and a "ComboBox" (DropDownList) that I want to populate based on the selection in the RadioButtonList. The first...
2
by: ghetto_banjo | last post by:
Ok, i thought i had this one figured out, apparently not. Here is my code on my button click event: stDocName = "frmInvoice" stLinkCriteria = "=" & Me! DoCmd.OpenForm stDocName, , ,...
1
by: ikuyasu | last post by:
Hi, I am trying to create a report that takes a value from the field on a table as a group category. But The value on the report takes an id number (auto increment, and the first column)...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.