automatic data display

[Guest]

I have a database with the following tables.
Customers, Inventory, Price, order entry(order details), orders, and colors.
On order to reduce data entry errors, when the CustomerID is entered into
the order entry form, I wolud like for the business name, contact first name
and contact last name to appear on the form when the Customer_No is entered
into the form . All of these fields reside on the Custtomer table.
Additionally, I would lilke to price of the item( from the Price table) and
the item name (from the inventory table) to display on the form after the
item_no is entered. Is this possible? I an rather new to Access but I
understand that in theory this should not be too difficult to do.

Thanks!

 

[John W. Vinson]

I have a database with the following tables.
Customers, Inventory, Price, order entry(order details), orders, and colors.
On order to reduce data entry errors, when the CustomerID is entered into
the order entry form, I wolud like for the business name, contact first name
and contact last name to appear on the form when the Customer_No is entered
into the form . All of these fields reside on the Custtomer table.

These can appear on the Form if you use a Combo Box to *select* an existing
customer from the Customers table. You can use the NotInList event of the
combo box to pop up a customer form in order to add new customers.

Put textboxes on the form with a Control Source like

=cboCustomerID.Column(n)

where cboCustomerID is the name of the customer combo box, and (n) is the zero
based index of the field from the combo's row source that you want to display.

Additionally, I would lilke to price of the item( from the Price table) and
the item name (from the inventory table) to display on the form after the
item_no is entered. Is this possible? I an rather new to Access but I
understand that in theory this should not be too difficult to do.

You can use the same technique for the item name; but for the price, it's
probably better to "capture" the price as of the time the order is placed.
Prices may change in the future but you don't want to have previous orders
changing as a result!

Use the Item combo's AfterUpdate event to set the price field's value.

You may want to look at the Northwind sample database Orders form, which does
all this...

John W. Vinson [MVP]