Using Combo Box to Create Query Criteria

[mesci]

Hi,

I've been attempting this for about 1.5 weeks now and it's finally getting to me.

What I'm trying to do:

I'm trying to set up a form that shows a combo box (displaying course name) from which the user can select a course (as query criteria) and enter data for all students who are on this "selected" course. (and I want to use the same method to produce reports based on a course name)

What I have tried:

1) I designed the form using a combo box (field list) from the main data table to list all courses. This enabled the user to choose the course required to search on and then to start the query to enter any required data for all students on this course. HOWEVER, everytime I did a search using this it entered the course name chosen into the first student record which had no course assigned.

2) I've tried changing the combo box to one where the courses are inputted as records and pulled from there to make the combo box. I then created the form etc, though this time it didn't show any data - it displayed the combo box but with no choices.

3) So I went back to using the original combo box but put it in it's own table (so it {hopefully} won't interfere with the student records as in example 1) however no it's saying that I can't choose from the combo box as it is locked to an unknown field.

I think I'm getting there slowly, but if anyone can help me along the way - I'd greatly appreciate it.

Cheers (in advance), Kate

 

[mesci]

Sorted It!!

See I said I was close!

I just set the combo box to unbound and used the combo box which was sourced from the table with the course names as records not field names. It works perfectly now!!! (HUGE SMILE)

 

[Hameed]

Hi Kate,
I hope my comments helps you develope you project.
Add a combo box and link it to the table [Courses]. Design a query for students table, under the course column in the query, you should right click on criteria then Build. Now locate the form on which you added the combo box. Double click on the required form and then you will get the list of all objects availible on the form in the list box adjucent to first one. Double click on the combo box (that contains the courses list), it will writen an expression on the top text box. then click ok.

Now you can write the following code either on for the click of the combo box or for the click even of a button you add to process the query.

DoCmd.OpenQuery "Your Query Name",acViewNormal

if you want to write the resultant query into any other table, create a Recordset (Provided that the DB(database is already defined))

Option Explicit
Dim RstStudnets as DAO.Recordset


Form_load()
Set RstStudnets=DB.OpenRecordset("Your Query")

End Sub

Now you can use your RstStudnets throughout your application.

I hope it helps you.