Access MS Access SQL query using a value from a combobox

[arresth]

Hi guys, I have a question that has been trying to kill me for the whole day. I'm pretty sure it's not going to be hard for you, if it is possible.
I am trying to run a SELECT query on a table, but I want the WHERE to be defined by a selection in a combo box. So the value for WHERE should be selected by the user. Is that possible?

I will show you what I've done so far.

Private Sub cboSector_Change()
Dim SQL As String
Dim Sector As String

SQL = "SELECT FirstName, Surname, Title FROM AttendanceArchive WHERE SchoolService = cboSector.Text"

lstOutcome.RowSource = SQL
End Sub

No need to tell you I've tried all variations of the more obvious stuff. Changing, rearranging.. didn't help me. I have the feeling I'm missing something very basic.. or it might not be possible.

I chose this path of doing things, because in my old database (Attendance is now being moved to the new one, thus becoming Archive) I have a similar thing, but input comes from an inputbox. The following statement works perfectly fine in my old database.

Private Sub cmdFind_Click()
Dim sql As String
sql = "SELECT FirstName, Surname, Title FROM Attendance WHERE FirstName = EmployeeName OR Surname = EmployeeName"
lstOutcome.RowSource = sql
End Sub

It's been a while since I used VB, so a bit of a helping hand will be grately appreciated.
Thanks in advance

 

[Rockin_R63]

arresth

I am no expert, but there are 2 things you can try:

First:
SQL = "SELECT FirstName, Surname, Title FROM AttendanceArchive WHERE SchoolService = cboSector.Text"
'.text' is used to write to a field, whereas '.value' is used to read from a field. Try changing cboSector.Text to cboSector.Value?

Second:
What you are trying to do is called a 'parameter query'. I have never done this directly thru code, but it is a doddle if you design your query in the query designer. Have a look at Access Help, its quite useful on the topic.

Best regards
Ron

 

[arresth]

Thanks Ron, .Value was what i was looking for. Good man