Combo Box were you select one feild but adds data to many.

[gadget3302]

Okay here is the deal. I have a table called Customers. E.g. name, city, state, zip ... In a Form I select the customer name. What I want to happen is that when I select this name, it will add the name to a field in a new table called consignee, but I also want the other fields city, state, zip to be entered into this new table. Is this possible? If so how can I do it?

Thanks,

Brian

 

[Remou]

You can create your combo on a form built on the Consignee table. The combo should include the fields customer, city, state, zip and so on. In the after update event of the combo, you can set the various Consignee fields by using the Colum property of the combo:

[tt]Me.City=Me.cboCustomer.Column(1)[/tt]

Numbering starts from zero.

The Orders form of the Northwind sample database has an example.

 

[RuralGuy]

If you want to keep your tables normalized then just put the CustomerID (PK) in the Consignee table as a ForeignKey and use a query of the Consignee table and the Customer table joined on CustomerID to have the fields "appear" in the Consignee table.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[Remou]

RuralGuy

I think that a 'ship to' address may be one of the rare exceptions, in that while an item may normally ship to the customer's regular address, in some cases the address may need to be changed, for example, a book to be delivered as a gift.

 

[RuralGuy]

I agree if for historical reasons.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[TheAceMan1]

[blue] . . . add the name to a field in a new table called consignee, but I also want the other fields city, state, zip to be entered into this new table.[/blue]

Here's an example [blue]append query[/blue] (assuming you used [blue]Remou's[/blue] suggestion of a combobox) which runs in the [blue]AfterUpdate[/blue] event of the combo. You just need to make sure the [blue]first column[/blue] in the combo's [blue]rowsource[/blue] is the primarykey of table customers (CustomerID in the example). CustomerID is assumed to be numeric:

[blue]   Dim SQL As String
   
   SQL = "INSERT INTO tblConsignee ( Name, Adr, City, State, Zip ) " & _
         "SELECT tblCustomers.Name, " & _
                "tblCustomers.Adr, " & _
                "tblCustomers.City, " & _
                "tblCustomers.State, " & _
                "tblCustomers.Zip " & _
         "FROM tblCustomers " & _
         "WHERE (tblCustomers.CustomerID=[Forms]![FormName]![ComboName]);"
   DoCmd.RunSQL SQL[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[gadget3302]

TheAceMan1 what is the & _ used for in your statements?

 

[PHV]

& string concatenation operator
_ line continuation character

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

Have you had a chance to look at The Orders form of the Northwind sample database?

 

[gadget3302]

Sorry, No. But I will look into it. Thanks guys for all your help!