Using an unbound combobox to filter another combobox

[MP]

I am using Access 97. I have a form with the record source of tblDocReview.
In the form I have two combo boxes: cboStdyExt and cboStdyNo. StdyExt is a
field from tblStudyDescription and StdyNo is a field created from
qryStudyNumber combining multiple fields from tblStudyDescription. The
following is a summery of the tables/query and their fields.

tblDocReview (contains many more fields but these are the related ones)
DocID (PK)
Autonumber (FK)

tblStudyDescription
Autonumber (PK)
StdyExt

qryStudyNumbers
Autonumber
StdyNo
StdyExt

What I want to do in my form is use cboStdyExt to filter cboStdyNo so I do
not have to scroll through hundreds of numbers. I need the autonumber that's
associated with the chosen StdyNo to be populated back into tblDocReview.

I tried creating a filter query with a when statement and an on change
event which worked to filter but changed those fields in all records and
didn't save back to the original table.
SELECT qryStudyNumbers.StdyNo, qryStudyNumbers.StdyExt
FROM qryStudyNumbers
WHERE
(((qryStudyNumbers.StdyExt)=[forms]![frmDocumentReview].[cboStdyExt].[value]));
Private Sub cboStdyExt_Change()
'Me is the fastest way to refer to the user form
'cboStdyNo is the name of the second combo box
'Requery refreshes the combobox based on the selection of the first combobox
Me.cboStdyNo.Requery
End Sub

I figured I need to use the Autonumber field to be able to insert it back
into the table so I created a cboAutonumber and replaced all the cboStdyNo
references. It's still not working.

 

[Arvin Meyer [MVP]]

If you have Access to a machine with Access 2000 or later, I have a sample
database which will give you an example of what you are trying to do. You
should be able to convert it to Access 97 easily. If you can't find an
Access 2000 or later machine, post back. The file is at:

http://www.accessmvp.com/Arvin/Combo.zip