PLEASE HELP: Design a Student Screener in forms module

[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

 

[KARL DEWEY]

You need to correct your table sturcture to have at least two tables.
Student table to have following fields --
SID - primary key
FName
LName
HeightIn
WeightLb

Second table for activity data --
SActID - primary key
SID - foreign key
ActDate - DateTime
Present - Yes/No - default Yes
Played - Yes/No - default Yes
Activity - - baseball, basketball
Score -

 

[sam]

Hi Karl,

I am not too worried about the table structures. I want to developt this
screener with will helps find the students based on the selections as I
explained.

I know how the table structures should be, But rite now its all about the
Student screener.

Hope you can help me with this.

Thanks in advance

 

[Douglas J. Steele]

"is not working yet" doesn't give much to go on...

What's the actual SQL Statement you're getting in strSQL1?


What does it do that it's supposed to? What does it do that it's not
supposed to?

 

[sam]

Hey Douglas,

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