add to combo box from access with where statment

[tulsafork]

I have been working on this application and im stuck now at a spot where i want to be able to click a button and have a combo box populate with text from a field in a access database where another field on the same record should be equal to a text item in another combo box.

somewhat like a sql statement like. SELECT [Title] From Books Where [Teacher] = teachers.text

I dont know sql or VB very well I'm teaching myself so if my examples are a bit off I'm sorry.

this is the code in VB that i tried

Private Sub see_Click()
'rs is Record set inside of a database that is previously set.
'teachers and books are both combo boxs
Do While Not rs.EOF
books.AddItem (rs!Titles(Where(rs!Teacher = teachers.Text)))
rs.MoveNext
Loop
End Sub

it keeps giving me error at rs!Teacher anyone understand me or can help?

[hkdani]

(rs!Titles(Where(rs!Teacher = teachers.Text)))

You've put the cart before the horse.

Your first step is to open the recordset using a sequel statement string: rs.open ( source, etc., , , )

Your sequel statement will go in the source parameter as a string

Dim strSequel as string

strSequel = "SELECT Titles.Teacher from Titles WHERE Teacher='" & teachers.text & "'"

rs.Open strSequel, etc. , , ,

I'm taking for granted the name of the table in your recordset is Titles and the Field you are searching is Teacher.

A good download for you would be the Microsoft Active X data objects Software Development Kit (SDK). The current release is at 2.8. It has a very well written manual which give examples on how to open up databases.

Once you open the database using the above sequel string, you should have a recordset of Titles where the Teacher is the name you have entered in the teachers text box control.

I could just give you the code in a zip file. But I think you would benefit more in the long run by downloading the SDK, reading the documentation. But if you get stuck, let us know.

Hank

[AndreRet]

Another fail safe way...........

[dim i
[If rs.EOF = true then
exit sub
else
i=o to rs.recordcount
books.additem teachers.text
rs.movenext
next i
end if]

I'm assuming that teachers datasource and datafield is reffered to rs...

[venkatramasamy]

hi,
with the same record set you have created, the following code will help you to get the result,

Private Sub see_Click()
Do While Not rs.EOF
if strcomp(rs!Titles,teachers.tect,vbTextCompare)=0 then
books.AddItem (rs!Titles)
end if
rs.MoveNext
Loop
End Sub

and instead of using while loop i suggest you to use the Forloop like...

rs.movefirst
for i=0 to rs.recordcount-1
books.AddItem (rs!Titles)
end if
rs.MoveNext
next

hope this will help you,

With regards
Venkatramasamy SN

[tulsafork]

I solved it thanks for the help guys it added some ideas to my head that really got things moving. After adding a few combo boxes named stuid and barcode which are only to make things more readable the code looks like this

Do While Not rs.EOF
    If rs!Teacher = teachers.Text Then
    barcode.AddItem (rs!barcode)
    books.AddItem rs!Title & ""
    stuid.AddItem rs!StudentID & ""
    rs.MoveNext
    Else: rs.MoveNext
    End If
If rs.EOF Then Exit Do
End If
Loop