Auto fill Data on a form

[Ed-Masto]

Hello:

I have what seems to be a very simple request for help.

I have a customer table (with address info, etc) and a sales order header
table and a sales line table.

When creating a new order, the first field to be completed is the customer
number (which is a lookup to the customer table). After the customer number
is entered, I would like certain information from the selected customer table
to auto fill in certain fields (ie address information) on the sales order
header table.

Please help me!

 

[NetworkTrade]

ok first - database best practices is that you don't need any duplicate info
- just put in the valid cross reference field value for that customer into
your sales record.

second - this requires you work via forms and not directly in the table
themselves...because one can load the vb to do this autowriting into a form
but can not in a table...

assuming you have forms based on these tables - what you want to do is after
selection of that customer put in some vb into an appropriate event
AfterUpdate maybe...that inserts the values from the customer form into the
sales form

 

[Ed-Masto]

Thanks for the reply.
First, I agree that there is no need to duplicate information, but sometimes
the address info needs to be changed for just one order. Therefore, I cannot
just get the address information from the customer record every time the
order prints.

I am not a very good vb code jockey. Can you please just give me an example
of the code that needs to be placed on the form in the After update section
for the customer number field and I will try to modify it to suit.

Much thanks!!

Ed

 

[John W. Vinson]

Hello:

I have what seems to be a very simple request for help.

I have a customer table (with address info, etc) and a sales order header
table and a sales line table.

When creating a new order, the first field to be completed is the customer
number (which is a lookup to the customer table). After the customer number
is entered, I would like certain information from the selected customer table
to auto fill in certain fields (ie address information) on the sales order
header table.

Why?

Storing this information is redundant. If you have the address in the customer
table, then you can use a Query linking the order header table to the customer
table (on the customer ID) to look up the address. You can also display
(without storing) the information on your Form; just include the fields that
you want to display in the combo box's rowsource query, and use

=comboboxname.Column(n)

where n is the zero based position of the desired field in the query to
display it.

If you store the address in the orders table, then you'll have to track down
all the records with the address and fix them if the customer's address
changes. The only reason to store it is if you want to keep a record of what
the address WAS at a previous time... do you?

 

[NetworkTrade]

like this in the AfterUpdate event of the combobox that is looking up the
customer...and presuming that combobox has several fields such as Name,
Street, City, etc.

Me.NameField=me.CustomerCombo.Column(2)
me.AddressField=me.CustomerCombo.Column(3)
me.CityField=me.CustomerCombo.Column(4)

that's the idea....

 

[Ed-Masto]

I see. Thanks! That looks pretty easy. Lemme give it a whirl.

 

[Klatuu]

If the Ship To address needs to be changed from time to time, you need an
additional table to handle ship to addresses. That would include the
customer's default Ship To address. The order header table should then have
a foreign key field that points to the correct address record.

Then in your form, you could add an additional combo that would allow you to
either select an existing ship to or create a new one. You would need to
use the Cascading Combos technique for that.