PLEASE HELP: Design Students Screener in Access

[sam]

Hi All,

I have a Student2009 Database With the following Data elements:

FName
LName
SID
HeightIn
WeightLb
DaysPresent
DaysAbsent
Percent
TotalCourseNo
AvgBaseBallScore
AvgBasketBallScore

So now I want to design a screener which will help me find the students I
want based on what I select. I have already started it by designing a custom
form in forms module where I can select the criterias like BaseBallAvg1,
BaseBallAvg2, BasketBallAvg1 and BasketBallAvg2, Height1 and Height2 etc .
here is my code, But it is not working yet and I need some help with it.
PLEASE HELP!

Option Compare Database

Private Sub SearchStudents_Click()


Dim conn As Object, rst1 As Object, strSQL1 As String

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students2009.accdb;" & _
"Jet OLEDBatabase Password=mystudents;"

strSQL1 = "SELECT * " & _
" FROM Students09 WHERE AvgBasketBall BETWEEN " & _
Me.BasketBallAvg1.Value & _
" AND" & _
Me.BasketBallAvg2.Value & _
" AND AvgBaseBall BETWEEN" & _
Me.BaseBallAvg1.Value & _
" AND" & _
Me.BaseBallAvg2.Value & _
" AND Height BETWEEN" & _
Me.Height1.Value & _
" AND" & _
Me.Height2.Value & _

Set rst1 = conn.Execute(strSQL1)


Set rst1 = Nothing

Set conn = Nothing

End Sub

NOTE: Here BaseBallAvg1, BaseBallAvg2, BasketBallAvg1 and BasketBallAvg2 are
text boxes AND Height1 and Height2 are Comboboxes.

Thanks in Advance

 

[J_Goddard via AccessMonster.com]

Hi -

If this code is in your form, your form will never show you anything. The
first thing you do after opening the recordset is close it again. Where do
you expect the data to go? Secondly, you close (disconnect from) the
database again, so you don't have any access to it.

There are a number of questions that arise here:

1) Is the form you are using in Student2009 database? If so, you don't need
to connect to it in the code - you are already in it.

2) Does the form have a defined record source (table or query)? If the
answer to that is no, where do you expect the data you see to come from?

3) What are the controls on the form?

Please give us more info on what you are trying to do.

John

 

[sam]

Hey J_Goddard,

I got a few things to work. But I am changing the approach on how to screen
for students.

Now it’s not only going to screen students from one school, they are from
different school zones.. So basically.. Here is how I want it to work:

I have 4 dropdown lists:

School, Gender, Physical_attributes, Conditions

The values of these dropdown lists are:

School_Zone: West, North, East, South
Gender: Male, Female
Physical_attributes: Height, Weight
Conditions: =,<,>
And a Textbox

And there is a button "Search" .

So now,
Once I select a school from the dropdown list and click search, I see the
results based on what I selected for school only.

If I Select a School and a Gender, I see the results based on what I
selected for School and Gender..

If I Select “South†for School, “Height" for Physical_Attributes and “>"
from conditions dropdown and input "147" in the textbox, I see the results
based on what I selected for School, Physical_Attributes, Conditions and the
value in textbox.

So basically, the search criteria’s are independent of each other, But if
you select multiple search criteria’s you should see the results based on
what you selected for all the search criteria’s.

Here is the code I have so far:

StrSql = "SELECT * FROM Students_Table" & _
" WHERE Students_Table.Zone = " & _
Me.Zone.Value & _
" AND " & _
" Students_Table.Gender = " & _
Me.Gender.Value & _
" AND " & _
"Students_Table.Physical_Attributes = " & _
Me.PhysicalAtt.Value & _
" AND " & _
Me.conditions.Value & _
Me.TextBox.Value

Also, Once I click the "Search" Button, How can I make it display at the
bottown of the form?

Hope I was clear

Thanks in advance