Updating more than one field using combo box

[Guest]

I have two tables in an ordering system database. One contains names, phone
numbers etc of staff placing the orders and the other the main table
containing full details of the order. I use a combo box in a form to select
the name of the staff member from the staff table and place this name in the
main table. How do I also get other data to transfer across at the same time,
ie place the phone number from the staff table into a corresponding field in
the main table.

Regards

Harry

 

[Craig Alexander Morrison]

Data in a proper relational database should only exist in ONE place.

There are what appear to be exceptions but they are not, if in fact they are
point-in-time data such as the price of the items at the time they were
ordered as opposed to the price of the items as they are currently, if that
is the policy of the company.

I do not believe you have a system where your Staff Members are changing
their phone numbers between orders and even less likely that they are
changing their names. And even less likely still that you want to know what
their name and phone number was for each order....

I would suggest you consider using a relational database design rather than
two buckets. Orders would normally consist of at least two main tables one
for Order data that applies to the whole order and OrderDetails for data
that applies to each item on that order. You should probably have a foreign
key relationship to the Personnel (StaffMember) table where you can get both
the PersonnelName and PersonnelPhoneNumber.

BTW If you really have to update more than one field after selecting an item
from a ComboBox use the AfterUpdate event to set the value of the other
field(s). From your explanation of your problem I would not recommend doing
this without reviewing your design which I believe (from the limited
information you have provided) is flawed.

 

[Craig Alexander Morrison]

BTW you use the .Column(n) suffix to the ControlName of the ComboBox to get
the values.