Dylan said:
I would like to have a combo box, that holds customer names. It
starts off with no customer selected.
The drop down combo box would contain all my customers and I would
assume reside on my paren form.
When you select a different customer from the combo box, the rest of
the fields on the form (and subforms) would load with the relevant
data. That's my first wish from the Forum Genies.
I would also like to be able to start typing a customer name in the
combo box and have the combo box scroll to (when epnaded down) the
relevant customers starting with that string as I type.
That is my second wish.
I have the typical setup of Customers (1) and Orders (M).
CustID
CustName
OrderID
OrderDetail
Your assistance/tips or reference to other material potentially
avaiable on the web to create this sort of combo box would be
appreciated.
Second question first, Enable the AutoExpand property on the ComboBox and it
will jump in the list as you type to matching records.
Now for the other we should first establish what is considered "best practices"
in a relational database. If this form is for creating an Order then you do not
want to *copy* all of the data from the Customers table (even though you might
want to *see* this data on the form). A good rule is "data is stored in one
place only". All you should really need stored as part of an order is the
CustomerID.
So I assume your ComboBox will store a CustomerID in a hidden column, but
display the CustomerName. If so that's a good start. What you can do is add
fields from the Customer table as additional hidden columns in the ComboBox and
have TextBoxes on your form display these columns. The ControlSource syntax for
that is roughly...
=ComboBoxName.Column(2)
Columns are indexed starting with zero so the above assumes a hidden column(0)
for the CustomerID and a displayed Column(1) with the CustomerName. The
additional columns then would start at (2).
Using this strategy you can see as much stuff about a Customer on your orders
form without redundantly making copies of that data. If their data changes
(phone number for example) then even older records will display the current
correct data.
Now the exception to the above is dynamic data that needs to be captured at the
point of order creation. An example of this would be if the Customer table
stored a DiscountRate used in calculating prices in the LineItems area. If you
look at an older order record you need to see the DiscountRate that the customer
had when that specific order was placed so for that field you DO actually want
to copy it. Another example is if the Customer table needs to provide a default
value that will sometimes be overridden on the order (Ship-To Address) is a good
example of this. In order to provide an override capability you again have to
copy the data rather than use a lookup.
One way to perform a copy is very similar to the lookup method described. Add
that data as additional columns in the ComboBox and then in the AfterUpdate
event of the ComboBox you use code that does the copying.
Me.DiscountRate = Me.ComboBoxName.Column(3)
Me.ShipToAddress = Me.ComboBoxName.Column(4)
Me.ShipToCity = Me.ComboBoxName.Column(5)
Me.ShipToState = Me.ComboBoxName.Column(6)
etc...
The above strategy copies the data from those hidden columns inot bound
TextBoxes on your form and that data is then stored as part of the Order Record.
So the first thing I recommend is to examine all of the customer data that you
want to see on an Order and decide whether it should be data that is "looked up"
versus data that should be copied. Any that falls into the first category
should have those fields removed from your Orders table as they are not needed.
