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
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
"MP" <MP[ at ]discussions.microsoft.com> wrote in message
news:6F33C537-DD9F-40E9-BA89-00528EB93AE5[ at ]microsoft.com...
[Quoted Text] >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.
|