Combobox Based on a Query

[injanib]

Hello all,



On a form two of my fields are comboboxes, cboTerm and cboClass.
I use this form to register students in a class. The cboTerm is for the name of the semester and its row source is a table. The cboClass contains the name of the classes and its row source is a query that is based on multiple tables (tblClass, tblTerm, tblStudents).
the tblStudent has a field called EligibilityLevel, which is a number field.

In the query that serves as the row source of cboClass, I had a criteria in the TermID field from the tblClass. The criteria was this:

Like [txtTermID] & "*"

the after update event of the cboTerm requerried the cboClass, and the cboClass only displayed the classes that are being offered in the selected term by the above criteria. This worked perfectly fine and if I navigated between the records, the value of the cboClass displayed for each record.

I decided to add another criteria based on the students' eligibility so that the cboClass only displayed the classes that are offered in the selected term and also for which the students are eligible.
I added the criteria under the EligibilityLevel field of tblClass in the query as follows

>=[txtEligibilityLevel]

This works if I am entering a new record, but when I navigate back to the records, the cboClass is blank. And therefore it throws off all my other codes, because of the blank fields.

Can someone tell me what I am doing wrong?

Thank you in advance.

[TheShabz]

Try throwing in a:
Me.cboClass.Requery in the AfterUpdate section of whatever control it is you use to enter the data.

[injanib]

Thanks for the reply. I already have that on the AfterUpdate event of the first combobox. As I mentioned, the combobox is requeried fine when I am entering new records. It is only when I navigate back to the record, the combobox goes blank and so does the unbound fields that get their values based on this combobox.

I even tried the requery on the forms Current event to requery the combobox for the old records, but that didn't help either.

Any other suggestions?

[TheShabz]

Not sure. Sorry =/

[injanib]

I think the problem is with the Less than sign. It works with the equal sign or "Like", except of course I don't get the result I want.

I seems the less than or greater than works fine in a query criteria if referred to a given number or a field in the query, but not with an control on a form.

Any idea? is there another way to express "less than" in a query criteria?