Auto complete field on form

[oam]

I am a new user to Access 2007 and I built a form in Excel 2007 for my employees to complete and email when requesting information from management. The form would auto complete some of the fields based on other fields. The problem is, when the employees have to send several emails on the same subject they have reenter all the required information each time. So I wanted to change the form from Excel to Access so it would archive the information and when they would resend a request on the same subject then they would search for the previous information, change some of the entries, and resend.



I have built a form in Access for the employees to complete but I need one field (Message) in the form to auto complete (info from table FedExLookup) based on two entries (Type of Application and Disposition) on the form.
Can someone help me with the information on how to auto complete a field in a form based on two other fields?

[June7]

The FedExLookup table has three fields for this info: Type of Application, Disposition, Message? Is there a unique ID field in this table, such as an autonumber? How many combinations of Type and Disposition are there?

Since a basic principle of relational database is to not duplicate data, you might consider a setup where users select the record from FedExLookup instead of making two entries. The unique ID would be saved into the other table. This will establish a relationship between the two tables and data will not have to be stored in two places. The RecordSource for the form would be a query that joins the two tables on the unique ID. All related data will then be available for presentation on the form.

Use a multicolumn combobox to present the choices to users. The RowSource would be like: SELECT ID, [Type of Application] & " | " & Disposition As TypeDisp FROM FedExLookup ORDER BY [Type of Application], Disposition;
Set combobox properties:
Column Count: 2
Column Widths: 0";3"
Bound Column: 1
Control Source: FedExCode (field in other table)

Otherwise, options are:
1. Compound key field linking between the two tables on the two common fields.
2. DLookup function.
Both of these will show the related message but not save it to the other table. That will require code.

[oam]

June 7,

There is a Unique ID in each table and there are 7 choices for Type of Application and 10 choices for Disposition and different Message depending Type of Application and Disposition. I will try the seperate forms for each and see if will work for the employees. I just trying to make it as easy as possible.

[June7]

So there are 70 possible choices?

What do you mean 'try the seperate forms for each'? I did not suggest separate forms.

[oam]

There is actually more choices than that. There is also Action Requested has an additional 8 selections but I was going to use separate forms for each. I guess I misunderstood your statement but I am still open for suggestions on making it easy on the employees.

[June7]

Your original question was about how to select the correct message based on two values entered by users. That is all my reply dealt with. Any other choices users have to make on the form are not related to this issue.

No idea how you could interepret my suggestion to mean separate forms.