SELECT query in vba

[vadharah]

Hi guys,

i have the following query in which i want to search for students' Name and address..where the grade is equal to a value entered in a text box..as i do that i also want to check if the recordset is empty.


Dim str As String
Dim db As Database
Dim Result as Recordset

Set db = CurrentDb()
str = "select student.MainContact,student.Add1,student.Add2 FROM student,grades WHERE(grades =forms!search!gr.value)"
Set Result = db.OpenRecordset(str)

If Result.RecordCount > 0 Then....
else ......e.g. msbox "No Matches found!"

when i run this query i get an error saying "Too few parameters. Expected 1"

where am i going wrong?

 

[MSAccessRookie]

Hi guys,

i have the following query in which i want to search for students' Name and address..where the grade is equal to a value entered in a text box..as i do that i also want to check if the recordset is empty.


Dim str As String
Dim db As Database
Dim Result as Recordset

Set db = CurrentDb()
str = "select student.MainContact,student.Add1,student.Add2 FROM student,grades WHERE(grades =forms!search!gr.value)"
Set Result = db.OpenRecordset(str)

If Result.RecordCount > 0 Then....
else ......e.g. msbox "No Matches found!"

when i run this query i get an error saying "Too few parameters. Expected 1"

where am i going wrong?

You do not seem to have joined the tables together in this query. Changing "FROM student,grades" to "FROM student INNER JOIN grades ON (Key from Student)=(Key from grades)" might get you better results.

 

[Alc]

Is 'grades' the name of a second table?
If so, join it to 'student' in the query.
If it's not, and it's actually the name of a field in the 'student' table, try

str = "select student.MainContact,student.Add1,student.Add2 FROM student WHERE (student.grades = '" & forms!search!gr.value & "')"

 

[vadharah]

Thank you guys for your replies.
'grades' is a table..
i have tried this:

Dim str As String
Dim db As Database
Dim Result As Recordset

Set db = CurrentDb()
str = "SELECT student.MainContact, student.Add1, student.Add2 FROM student INNER JOIN grades ON student.studentID = grades.studentID WHERE grades.grade=forms!search!gr.value"
Set Result = db.OpenRecordset(str)

Im still getting the same error say "too few parameters. Expected 1"

 

[boblarson]

It should be:

str = "SELECT student.MainContact, student.Add1, student.Add2 FROM student INNER JOIN grades ON student.studentID = grades.studentID WHERE grades.grade='" & forms!search!gr.value & "'"

If grade is text.

If grade is numeric then:
str = "SELECT student.MainContact, student.Add1, student.Add2 FROM student INNER JOIN grades ON student.studentID = grades.studentID WHERE grades.grade=" & forms!search!gr.value

 

[vadharah]

It should be:

str = "SELECT student.MainContact, student.Add1, student.Add2 FROM student INNER JOIN grades ON student.studentID = grades.studentID WHERE grades.grade='" & forms!search!gr.value & "'"

If grade is text.

If grade is numeric then:
str = "SELECT student.MainContact, student.Add1, student.Add2 FROM student INNER JOIN grades ON student.studentID = grades.studentID WHERE grades.grade=" & forms!search!gr.value

Works perfectly!!...thank you

 

[vadharah]

i have a similar error to the problem i was having above...

my code is:

str = "SELECT People.Name, People.[Ethnic Origin], People.Gender, People.DOB, People.Daytime, People.Mobile, People.Evening, People.Email, People.DIY FROM People WHERE People.DIY=Yes AND forms!searchPerson![diy_A].value=Yes"

where diy_A is a checkbox and within the table People.DIY is also a checkbox.
the query works fine in the 'Design View' but doesnt seem to be in VBA

i have tried

str = "SELECT People.Name, People.[Ethnic Origin], People.Gender, People.DOB, People.Daytime, People.Mobile, People.Evening, People.Email, People.DIY FROM People WHERE People.DIY=' " & forms!searchPerson![diy_A].value & " ' "

to no avail