Using lookup to fill in form

[Bill0722]

This is probably too elementary for you, but it has me stumped. Here's what I want to do.
I have two tables -- Customers and Orders
I want to have a form which is basically based on the Orders table. I want it to open on a Customer Number field, which would be a lookup field. I want to select the Customer number from that field and then have the Customer Name and address automaticall filled in in the Orders table. I sure this is pretty basic, but I can't seem to figure it out. Thanks.

 

[fneily]

On the AfterUpdate of your customer number control, you would use the Dlookup function to search the Customer Table for additional information. Such as:

Private Sub CustomerID_AfterUpdate()
Me![Name].Value = DLookup("[Name]", "tblCustomer", "[CustomerID] = " & Forms![YourFormName]![CustomerComboboxName])

Me![Address].Value = DLookup("[Address]", "tblCustomer", "[CustomerID] = " & Forms![YourFormName]![CustomerComboboxName])
End Sub

 

[TheAceMan1]

How are ya Bill0722 . . .

[blue]I have two tables -- Customers and Orders . . . [purple]have the Customer Name and address automaticall filled in in the Orders table.[/purple][/blue]

This is [blue]redundant[/blue], let alone defy table normalization. [blue]Name & address[/blue] should be in the [blue]Customers[/blue] table only.

You can have what you want but I will not send you on a road you should'nt travel! . . . (many problems ahead in this way you seek). Rather, have a look at the following which should open your eyes and force you to ask your question in another way . . .

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

I'd love it if you posted back how you fully understand the links . . . [blue]its to your benefit as a designer[/blue] and once you understand you'll have a grip on access you didn't think possible . . .

See Ya! . . . . . .

 

[JoeAtWork]

Actually for the first time since frequenting this forum I'm going to have to disagree with Aceman.

When it comes to Orders, Invoices, etc. I would store the customer address directly in the Order or Invoice table. Why? Because customers sometimes change their address, but Orders, and similar documents, are historical, so the address on the Order should always stay the same, even if the customer has moved 3 times since the order was created.

Denormalization is justified when sticking to pure normalization would make the data not match the real life situation.

 

[TheAceMan1]

Howdy JoeAtWork . . .

[blue]Shippers address yes![/blue] . . . that makes sense!

Have a look at the [blue]NorthWind[/blue] database . . . its an excellent example!

See Ya! . . . . . .

 

[TheAceMan1]

woops! . . .

Sorry, I meant [blue]Ship To[/blue] address! . . .

See Ya! . . . . . .