A project such as you describe is well-suited to Access, but it is a
fairly
large undertaking as a learning project.
Consider just two tables for now: tblSupplier and tblOrder. A table
contains information about one real-world entity such as person,
supplier,
and so forth. Another way to think about it is that what is stored in a
table should be describable in a single sentence without using the word
"and". Having said that, name and address are OK in the same table
because
they are characteristics, if you will, of a supplier. However, name and
order do not belong in the same table, because an order is not a
characteristic of the supplier.
Each supplier may have many orders, but each order is associated with
just
one supplier, so the relationship between Supplier and Order is
one-to-many.
Click Tools > Relationships and add both tables, then drag SupplierID
from
one table to its namesake in the other table. Click Enforce Referential
Integrity when the dialog box appears. Close the Relationships window.
The one unchangeable field in each Supplier record is SupplierID. No
matter
if the company name or address changes, it will always be identified in
your
system by the SupplierID. By the way, if SupplierID in tblSupplier is a
PK,
and is to be in a relationship with SupplierID in tblOrder, SupplierID in
tblOrder *must* be a number field (long integer).
There are several approaches you can take to this. Here is one way.
Make a
query that includes tblOrder and tblSupplier. Add just the fields that
will
be needed from tblSupplier (name, address, etc.; leave out SupplierID,
and
any unneeded fields such as tax ID or whatever else will not appear on
the
Order), and all fields from tblOrders, including SupplierID. Build an
Order
form (omitting the OrderDetails) with just the basic Order information:
SupplierID (from tblOrder), Date, Buyer, and so forth. Make a combo box.
Set its Control Source to SupplierID (remember, SupplierID from
tblOrder).
Click the three dots next to Row Source, add tblSupplier to the design
view,
and add SupplierID and SupplierName to the query design grid. Sort by
name.
Close, and save as prompted.
Back to the combo box property sheet: set the Bound Column to 1 (on the
Data tab); on the Format tab set the Column Count to 2, and the column
widths to something like 0"; 1.5". On the order form, make an unbound
text
boxes with its Control Source set to =[Address1]. When you select a
supplier from the combo box, the SupplierID will be stored, but the
SupplierName will be displayed in the combo box. In the unbound text box
you should see the Address field. Use similar means to display other
fields.
This is all from recollection, so I hope I haven'tomitted anything. It's
all I have time for now, except to say you can do the same thing to build
a
Product list. Once you get the hang of how this works you can consider
OrderDetails. The relationships get a little more complex in that each
Order may contain many Products, and each Product may be associated with
many orders, which is why I urge you to get the simpler relationships
under
your belt first.
WoodyAccess said:
Thanks for replying Bruce. Wasn't that far into establishing the
database
so
have deleted all relationships so that the SupplierID can be a
AutoNumber
and
the Supplier Name can remain as the name.
I am now encountering a problem of when I want to link the fields in
the
relationships I dont know which SupplierID relates to which Supplier
Name.
And when I try to set a query so that the name appears in a combobox
rather
than a number I get an error!
There is a small thing wrong but I don't know how to change it
--
Thanks
WoodyAccess
:
The short answer is to add SupplierList and Orders to a query and use
that
as the Record Source for your Orders form. Bind text boxes to the
appropriate Address fields.
The longer response is that companies have been known to change their
names,
so the supplier name is not a good choice for SupplierID. One way to
solve
that is to add an autonumber field to the Supplier table, and use that
as
the SupplierID.
You should probably have a structure something like this:
tblSupplier (supplier table)
SupplierID (autonumber primary key, or PK)
SupplierName
Address, etc.
tblOrders
OrderID (PK)
SupplierID (foreign key, or FK)
OrderDate
other information specific to the order (buyer, etc.)
tblProducts
ProductID (PK)
SupplierID (FK) - this is so that you can easily select products
just
for that supplier
Description, UnitPrice, etc.
tblOrderDetails
DetailID (PK)
OrderID (FK)
ProductCode (FK)
Quantity
UnitPrice
The Products table is because you probably select the same items in
repeated
orders for a particular vendor.
It makes sense to have the Orders form as you describe, except that
you
really should rethink using the supplier name as the PK. However,
unless
an
order is always going to be for a single item you should keep the
details
in
another table, one record per detail. I made the mistake in an early
project of copying the existing Word form, which had ten lines. There
are
empty fields in some records because there are fewer than ten items,
and
in
other cases ten fields is not enough. The hardest part of Access at
first
is learning how to think about it. It is nothing like a spreadsheet.
If you make an Order form with a Details subform, and use on the
subform
a
combo box based on tblProducts table to select the product, you will
have
a
lot of flexibility without redundantly storing information. The
Northwinds
database that ships with Access may give you some ideas how to set up
something like you need.
Another point is that calculations such as totals should in most cases
be
performed on the fly rather than being stored.
I have an Orders Form that has fields of OrderNo, OrderDate,
TotalOrderCost
and a combobox of SupplierId (which is the name of the company in
text)
All I want to do is when the SupplierId is selected, that suppliers
relevant
address details (Address1, Address2, Address3, Region, City,
PostCode
and
Country) appear in textboxes beneathe. The information
(Address1.....)
comes
from a table called SuppliersList
I know it should be simple but I'm a novice and am not good with
Code
yet!
Could someone help me with the code. thanks