Autofill via a combo box

[roccoau]

Hi sorry I am only new to this, I am trying to learn as I go so please excuse my ignorance. (I have done some searching on this forum but am just getting more confused)
Anyway
I have a form which is based on a table that holds all the info for orders placed by customers.
It holds info on all the orders plus some info about the customer

Example of Orders Table

Order ID
Customer No
Suburb
State
Item Number
Qty Ordered
Description
Length
Weight
Order Type
Day Received
Last Dispatch Day
Actual Dispatch Date
Comments
Date Due at Customer

On this form I would like a combo box which is linked to a Customer table which has all the details regarding the customers. When I pick the customer I would like all the other fields relating to the customer on this form (Customer No, State, Suburb) to be auto populated to save entering data individually.

Example of my Customer Table

CustomerID
Customer Name
Customer No (not unique)
State
Suburb
Customer Factory
Rail Terminal code
Destination Rail code
Destination Road code
Cust Code

I have tried many different ways but no success.

Any help or advise would be most appreciated
Tks

 

[stopher]

You say you want to "save" the customer details through the form ? You should not do this. The only thing you should save is the CustomerNo. By saving the customer no. you can alway look up all the customer details whenever you need then (like on you delivery note).

So you question splits down into two problems (if I read you correctly).
- How do I get the combo to list all my customers
- Once selected, how do I display customer details on my form (without actually saving them)

How do I get the combo to list all my customers
Create a combo box and set the Control Source for this combo to the CustomerNo field
Set the Row Source Type to Table/Query
Set the ROw Source to the name of your customer table

how do I display customer details on my form
There are a least two ways to do this.
One way is to create a join query joining your order table and your customer table (related by customer number and customerID). Make sure your table lists all the fields you wan to see. ALso make sure that you list CustomerNo from the orders table and not CustomerID from the customer table (this is important!).
Now you can change your forms source to this query rather than the orders table. Add all the fields you require. When you run the form you will be able to enter a customer number and any customer detail fields will automatically be populated. I've you've implemented the combo then the same happens. Note that the user will be able to change customer details here directly (and it will affect details in the customer table and hence any other order with the same customer) so you make want to make the customer detail fields non-editable.

Another way to show customer details is to use the DLookup function. Typically you would put something like the following expression in an unbound field (assumes a text field):

DLookup("customerName", "CustomerTable","CustomerID='" & me!CustomerIDfield & "'")

hth
Stopher

 

[roccoau]

Thanks Stopher for your quick & detailed reply.
I will try it out & see how I go. (not sure if I understand everything fully but its something to work on)
Thanks again