Forms

[clauses]

I have two controls on a form One to display a code and another that will display the description or name associated with the code in the first control. Haow can I pass the value in the first control to a query that will produce the value that should be displayed in the second control.

 

[Jack Cowley]

Base the form on the query. Bind the second field to the Description field. Create a Combo box on the form using the Wizard and on the first screen select the 3rd. item, "Find a record..." and do the rest of the Wizard using the Code as the item to select. Now when you select a code in the Combo box the form will display the Description.

 

[clauses]

The form is based on a file, that contains the field that is displayed in the first control. I would like to used the value in this control to link to another file and find a discription for the code. For example the first text box contains a students major code. The second text box should contain the descripton of the major represented by the code. I am not exactly sure how to pass the value in the first text box to a query in odrer to display the majors description.

 

[Jack Cowley]

I am confused by your 'form connected to a file', but be that as it may you can put syntax like this in your queries criteria field:

[Forms]![YourFormName]![FieldNameOnFormWithCodeNumber].

Now the Criteria for your query will come from the field on your form.

[This message has been edited by Jack Cowley (edited 11-13-2001).]

 

[Pat Hartman]

Create a query that joins the two tables. In addition to the fields from the main table, select the description column from the lookup table. Then use that query as the recordsource for the form. Make sure that you set the Enabled property of the description field to No so that it will not be updateable on this form. This is called an "auto lookup" query. You can read about the concept in help. If you change the value of the code field on the form, Access will automatically change the value of the description field.

To make updating easier, it is common to use a combobox control for the code field. Set the limit to list property to Yes. This will ensure that only valid values can be entered in the code field.

You can see an example of this technique in the Orders.mdb and also in Northwind.mdb.

 

[clauses]

I made the changes to my query but after changing the control source for the second control I still get #Name? as the value. the control source for the second control is as follows =[qryQueryName]![Field Name].

 

[Pat Hartman]

That is incorrect syntax as a recordsource.

If you changed the query, you should see the description field in the field picker. If you press the down arrow in the controlsource field, does the new field show up in the list? If it does not, check the recordsource of the form. Sometimes Access takes your query and "hard codes" it into the recordsource rather than referring to the stored querydef. You can delete whatever is showing as the recordsource and replace it with your querydef. This should cause Access to refresh the form's field collection.

 

[clauses]

The form is used to update a file that contains codes used to identify information on other files. The form is used to Enter/Update information on the student file. This file contains codes for the studens major and advisor. It is thse codes that are displayed on the form. I also want to display the major discription and the advisors name which are located on other files and cannot be changed using this form. I have tried to used the value entered in the advisor code field as a parameter on a query that returns the advisors name and displays it in my second field. As of yet I have not been able to accomplish this and I am not sure exactly how to do it.

 

[Pat Hartman]

Since you didn't understand my explaination, look up autolookup queries in help. There is a sub-topic "My AutoLookup query isn't filling in data." that may identify the problem for you.