i think this is an easy question......

[Melissa2781]

I want to have a field in my main form that when i type the value in, if it has already been entered all of the other fields automatically fill in on that form and on the subform. For example...... If i type a customer ID number in its field and it is a previous customer, all of the customer's info will automatically show on screen. How would i go about doing this?

 

[RickSpr]

If your table design is right, and you're adding a new record, you can use AutoLookup and not have to write any code.

The field in question must be a foreign key to a table (call it Customers), and that table must be joined by that key to the table in which you're inserting the new record (call it Orders). Any other controls bound to fields of the Customers table will automatically be filled in when you update the foreign key. That's AutoLookup.

Caution: If any fields from the Customers table are enabled and unlocked, the user can update the Customers table using your form. That's usually not a good idea--updates to the Customers table should generally have their own form. So typically, you would make the Customers fields locked and disabled. However, it's up to you.

So, does AutoLookup work for you, or is your table structure something else? If so, describe the form's underlying recordset (best way is to give the equivalent SQL statement), and identify the fields you want filled, including which table each comes from.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Melissa2781]

Autolookup..... exactly what is this and how do i go about using it?

 

[Melissa2781]

and... how do i make a key a "foreign" key?

 

[WildHare]

To answer your most recent post first (because it's the easiest..lol) a FOREIGN key is a NON-key field in one table that is the KEY to another table.

For example, think of a small personnel system.

Table: Departments
Key : Department Number
Field : Department name

Table: Employees
Key: Employee Number
...
Field : DepartmentNumber


The Department-number field in the EMPLOYEE table is a FOREIGN key to the Departments table.

Foreign keys allow for the implementation of referential integrity (you can not assign a department number at the employee level using a department that does not exist), and also for "autolookup" purposes -

With Autolookup, you enter what amounts to the Foreign key value (e.g., "24&quot and what you see being returned is "Finance & Planning" , or whatever DepartmentNAME goes with DepartmentNUMBER 24 in the Department Table.

Autolookup would be implemented in this example at the EMPLOYEE level - in the properties pane for your Employee.DepartmentNumber, click the AUTOLOOKUP tab and enter the appropriate lookup-and-extract criteria.

See how easy it is?

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm