Simple Code in Orders Form

[Guest]

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

 

[BruceM]

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.

 

[Guest]

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.

 

[BruceM]

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.

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

 

[Guest]

Thanks BruceM worked like a charm. In your 2 replies I have already learnt a
lot about Access, you explain it very well and easy to understand (to a
novice). I will try and do the same with the products and then try and
progress to the order details.
--
Thanks
WoodyAccess

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.

 

[BruceM]

Glad to hear it helped.

Before you get too far with OrderDetails, consider this possible scenario:
Each order may have many products, and each product may be associated with
many orders. That is a many-to-many relationship, as I mentioned. Since
you can't make a many-to-many relationship directly between two tables, a
third table, often known as a junction table, is needed to resolve the
relationship. That third table is OrderDetails. As you can see from my
first reply, there are two FKs in that table: OrderID and ProductID (which I
mistakenly wrote as ProductCode). I should mention there is no requirement
that joined fields have the same name, but it simplifies explanations. You
may wish to call the FK field ProductID_OD, as it can help to keep things
straight if both ProductID fields (PK and FK) are referenced in code or
included in a query.

In a typical situation frmOrder will contain a subform (fsubDetails) bound
to tblOrderDetails. You select the product from a combo box (cboProd) on
fsubDetails. The combo box Row Source is tblProduct (the other table in a
one-to-many with tblOrderDetails). Its bound field is ProductID (the FK
field), and its visible column is ProdDescription. Its row source has a
column for UnitPrice, and may have other columns for ProductCode, etc.

Your actual situation may differ, so I won't go into a lot of details. For
instance, if you are purchasing commodities the price may vary from
day-to-day, so you will probably want to enter the price as part of the
OrderDetails record. In other cases the price may change infrequently, so
it would be better as a field in tblProducts, which is the situation I
assumed. In any case, you will probably want to store the UnitPrice in
tblOrderDetails. The other Product fields, such as ProdCode, should not be
stored in tblOrderDetails. It may be best to start with a two column combo
box containing ProductID and ProdDescription, similar to the method already
described for adding the Supplier to the Order.

Thanks BruceM worked like a charm. In your 2 replies I have already learnt
a
lot about Access, you explain it very well and easy to understand (to a
novice). I will try and do the same with the products and then try and
progress to the order details.
--
Thanks
WoodyAccess

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.

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

 

[Guest]

Hi there Bruce, me again. I think my Database is progressing well now.

I'm not sure if this is correct but my Order and Order Details forms are
both based on queries(?)

frmOrders - is based on the query when selecting SupplierID (from tblOrders)
the tbl Suppliers details (Address etc) come up and the OrderID, OrderDate
and TotalOrderCost (from tblOrders) are also on the form.

The fsubOrdersDetails is based on a query where selecting the ProductID
field (from tblOrderDetails) the relevant tblProducts (ProductCode, UnitType,
UnitPrice, Dicontinued) all appear as well as the tblOrderDetails info (Order
ID, Quantity and SubTotal).

Everything seems to work fine. Thanks for all the help. I am now going to
try to create and A4 document/Report that will print up the Individual Orders.

Wish me luck and thanks again for all your help.

p.s. How do I put a tick on this post to say my question has been answered?
(to help out others)
--
Thanks
WoodyAccess

Glad to hear it helped.

Before you get too far with OrderDetails, consider this possible scenario:
Each order may have many products, and each product may be associated with
many orders. That is a many-to-many relationship, as I mentioned. Since
you can't make a many-to-many relationship directly between two tables, a
third table, often known as a junction table, is needed to resolve the
relationship. That third table is OrderDetails. As you can see from my
first reply, there are two FKs in that table: OrderID and ProductID (which I
mistakenly wrote as ProductCode). I should mention there is no requirement
that joined fields have the same name, but it simplifies explanations. You
may wish to call the FK field ProductID_OD, as it can help to keep things
straight if both ProductID fields (PK and FK) are referenced in code or
included in a query.

In a typical situation frmOrder will contain a subform (fsubDetails) bound
to tblOrderDetails. You select the product from a combo box (cboProd) on
fsubDetails. The combo box Row Source is tblProduct (the other table in a
one-to-many with tblOrderDetails). Its bound field is ProductID (the FK
field), and its visible column is ProdDescription. Its row source has a
column for UnitPrice, and may have other columns for ProductCode, etc.

Your actual situation may differ, so I won't go into a lot of details. For
instance, if you are purchasing commodities the price may vary from
day-to-day, so you will probably want to enter the price as part of the
OrderDetails record. In other cases the price may change infrequently, so
it would be better as a field in tblProducts, which is the situation I
assumed. In any case, you will probably want to store the UnitPrice in
tblOrderDetails. The other Product fields, such as ProdCode, should not be
stored in tblOrderDetails. It may be best to start with a two column combo
box containing ProductID and ProdDescription, similar to the method already
described for adding the Supplier to the Order.

Thanks BruceM worked like a charm. In your 2 replies I have already learnt
a
lot about Access, you explain it very well and easy to understand (to a
novice). I will try and do the same with the products and then try and
progress to the order details.
--
Thanks
WoodyAccess

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.



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

 

[BruceM]

Glad I could help. There is something you may want to consider, though.
Are you storing ProductCode, UnitType, and other fields from tblProducts in
tblOrderDetails? If so, there's quite a bit of redundancy there. It would
probably be better if you store only the ProductID and the UnitPrice, and
just link to the other fields. If on the other hand you are linking to all
of the fields, what do you plan to do when the price changes?
In general you can use a query instead of a table as the record source for a
form or report. It is a good way of sorting the records, can be used for
such things as combining first and last names, and is used to define
parameters such as a date range or product category or whatever. For
various reasons, some queries cannot be modified (you cannot add or edit
data), but as long as that isn't happening a query is fine.
You can build a report just as you built the form, with a main report and a
subreport. Have a look at the report's Sorting and Grouping dialog, which
can be very useful.
The tick you would add must be something in the web-based newsreader. Since
I use Outlook Express as my newsreader I can't offer any insight into the
web-based version.
Good luck with the project.

Hi there Bruce, me again. I think my Database is progressing well now.

I'm not sure if this is correct but my Order and Order Details forms are
both based on queries(?)

frmOrders - is based on the query when selecting SupplierID (from
tblOrders)
the tbl Suppliers details (Address etc) come up and the OrderID, OrderDate
and TotalOrderCost (from tblOrders) are also on the form.

The fsubOrdersDetails is based on a query where selecting the ProductID
field (from tblOrderDetails) the relevant tblProducts (ProductCode,
UnitType,
UnitPrice, Dicontinued) all appear as well as the tblOrderDetails info
(Order
ID, Quantity and SubTotal).

Everything seems to work fine. Thanks for all the help. I am now going to
try to create and A4 document/Report that will print up the Individual
Orders.

Wish me luck and thanks again for all your help.

p.s. How do I put a tick on this post to say my question has been
answered?
(to help out others)
--
Thanks
WoodyAccess

Glad to hear it helped.

Before you get too far with OrderDetails, consider this possible
scenario:
Each order may have many products, and each product may be associated
with
many orders. That is a many-to-many relationship, as I mentioned. Since
you can't make a many-to-many relationship directly between two tables, a
third table, often known as a junction table, is needed to resolve the
relationship. That third table is OrderDetails. As you can see from my
first reply, there are two FKs in that table: OrderID and ProductID
(which I
mistakenly wrote as ProductCode). I should mention there is no
requirement
that joined fields have the same name, but it simplifies explanations.
You
may wish to call the FK field ProductID_OD, as it can help to keep things
straight if both ProductID fields (PK and FK) are referenced in code or
included in a query.

In a typical situation frmOrder will contain a subform (fsubDetails)
bound
to tblOrderDetails. You select the product from a combo box (cboProd) on
fsubDetails. The combo box Row Source is tblProduct (the other table in
a
one-to-many with tblOrderDetails). Its bound field is ProductID (the FK
field), and its visible column is ProdDescription. Its row source has a
column for UnitPrice, and may have other columns for ProductCode, etc.

Your actual situation may differ, so I won't go into a lot of details.
For
instance, if you are purchasing commodities the price may vary from
day-to-day, so you will probably want to enter the price as part of the
OrderDetails record. In other cases the price may change infrequently,
so
it would be better as a field in tblProducts, which is the situation I
assumed. In any case, you will probably want to store the UnitPrice in
tblOrderDetails. The other Product fields, such as ProdCode, should not
be
stored in tblOrderDetails. It may be best to start with a two column
combo
box containing ProductID and ProdDescription, similar to the method
already
described for adding the Supplier to the Order.

Thanks BruceM worked like a charm. In your 2 replies I have already
learnt
a
lot about Access, you explain it very well and easy to understand (to a
novice). I will try and do the same with the products and then try and
progress to the order details.
--
Thanks
WoodyAccess


:

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.



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.

message
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

 

[Guest]

I'm am now storing OrderID, ProductID, UnitPrice(from tblProducts) Quantity
and Discount in tblOrderDetails. I also have a subtotal control box with
equation on the fubOrderDetails and in the footer a OrderTotal control.

What I would like to do though is that when i select the SupplierID cbo in
frmOrders for the ProductID in fsubOrderDetails to only show selected
Products from that supplier.

Is this possible?
--
Thanks
WoodyAccess

Glad I could help. There is something you may want to consider, though.
Are you storing ProductCode, UnitType, and other fields from tblProducts in
tblOrderDetails? If so, there's quite a bit of redundancy there. It would
probably be better if you store only the ProductID and the UnitPrice, and
just link to the other fields. If on the other hand you are linking to all
of the fields, what do you plan to do when the price changes?
In general you can use a query instead of a table as the record source for a
form or report. It is a good way of sorting the records, can be used for
such things as combining first and last names, and is used to define
parameters such as a date range or product category or whatever. For
various reasons, some queries cannot be modified (you cannot add or edit
data), but as long as that isn't happening a query is fine.
You can build a report just as you built the form, with a main report and a
subreport. Have a look at the report's Sorting and Grouping dialog, which
can be very useful.
The tick you would add must be something in the web-based newsreader. Since
I use Outlook Express as my newsreader I can't offer any insight into the
web-based version.
Good luck with the project.

Hi there Bruce, me again. I think my Database is progressing well now.

I'm not sure if this is correct but my Order and Order Details forms are
both based on queries(?)

frmOrders - is based on the query when selecting SupplierID (from
tblOrders)
the tbl Suppliers details (Address etc) come up and the OrderID, OrderDate
and TotalOrderCost (from tblOrders) are also on the form.

The fsubOrdersDetails is based on a query where selecting the ProductID
field (from tblOrderDetails) the relevant tblProducts (ProductCode,
UnitType,
UnitPrice, Dicontinued) all appear as well as the tblOrderDetails info
(Order
ID, Quantity and SubTotal).

Everything seems to work fine. Thanks for all the help. I am now going to
try to create and A4 document/Report that will print up the Individual
Orders.

Wish me luck and thanks again for all your help.

p.s. How do I put a tick on this post to say my question has been
answered?
(to help out others)
--
Thanks
WoodyAccess

Glad to hear it helped.

Before you get too far with OrderDetails, consider this possible
scenario:
Each order may have many products, and each product may be associated
with
many orders. That is a many-to-many relationship, as I mentioned. Since
you can't make a many-to-many relationship directly between two tables, a
third table, often known as a junction table, is needed to resolve the
relationship. That third table is OrderDetails. As you can see from my
first reply, there are two FKs in that table: OrderID and ProductID
(which I
mistakenly wrote as ProductCode). I should mention there is no
requirement
that joined fields have the same name, but it simplifies explanations.
You
may wish to call the FK field ProductID_OD, as it can help to keep things
straight if both ProductID fields (PK and FK) are referenced in code or
included in a query.

In a typical situation frmOrder will contain a subform (fsubDetails)
bound
to tblOrderDetails. You select the product from a combo box (cboProd) on
fsubDetails. The combo box Row Source is tblProduct (the other table in
a
one-to-many with tblOrderDetails). Its bound field is ProductID (the FK
field), and its visible column is ProdDescription. Its row source has a
column for UnitPrice, and may have other columns for ProductCode, etc.

Your actual situation may differ, so I won't go into a lot of details.
For
instance, if you are purchasing commodities the price may vary from
day-to-day, so you will probably want to enter the price as part of the
OrderDetails record. In other cases the price may change infrequently,
so
it would be better as a field in tblProducts, which is the situation I
assumed. In any case, you will probably want to store the UnitPrice in
tblOrderDetails. The other Product fields, such as ProdCode, should not
be
stored in tblOrderDetails. It may be best to start with a two column
combo
box containing ProductID and ProdDescription, similar to the method
already
described for adding the Supplier to the Order.

Thanks BruceM worked like a charm. In your 2 replies I have already
learnt
a
lot about Access, you explain it very well and easy to understand (to a
novice). I will try and do the same with the products and then try and
progress to the order details.
--
Thanks
WoodyAccess


:

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.



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

 

[BruceM]

When you say "selected Products from that supplier" I assume you mean you
want to see only products sold by that supplier, rather than you want to see
a selection of products from that supplier. Either can be done, but I will
assume the former. You need to adjust the row source for the combo box.
Here is a sub I added to my PO forms code module:

Public Sub ProdRowSource()

On Error GoTo ProcErr

Dim strRowSource As String

strRowSource = "SELECT ProdDescr, ProductID, ProdCode, ProdUnit,
UnitPrice, SupplierID " & _
"FROM tblProduct " & _
"WHERE SupplierID = Forms!frmPO!SupplierID " & _
"ORDER BY ProdDescr, ProdUnit"

Forms!frmPO!fsubPO_Items.Form!cboDescription.RowSource = strRowSource

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error #" & Err.Number & ", " & Err.Description & " -
ProdRowSource"
Resume ProcExit

End Sub

You will need to adjust the field names, etc. as needed to match your actual
names. My Purchase Order form is frmPO, and the line items subform is
fsubPO_Items. Supplier ID is the FK in both the PO table and the Product
table. The combo box on the subform from which the product is selected is
named cboDescription. If you have spaces in your field names you will need
to use square brackets around them. If you need to select a subset of
records you can modify the WHERE part of the SQL as needed. I won't go into
details because I suspect it is not the case.

Add this line of code:

Call ProdRowSource

to the PO forms's Current event and to the After Update event for the combo
box on the PO form from which the Supplier is selected. In other words,
when you select a supplier to start writing a PO, or when you navigate to a
PO record, the row source for the Product combo box on the subform will
change so that it includes only those records in the Products table that
have the same SupplierID information as on the current PO record.

I pieced together this approach from things I have learned here, but the
actual system is the product of my experience and understanding rather than
a borrowed technique. I'm not saying it's unique (it is very likely that
somebody else has done something of this sort, but I'm just not aware of
it), and I most certainly *am not* claiming any sort of ownership, but
rather am saying that while it seems to be sound, effective, and efficient,
it has not been extensively tested.

I'm am now storing OrderID, ProductID, UnitPrice(from tblProducts)
Quantity
and Discount in tblOrderDetails. I also have a subtotal control box with
equation on the fubOrderDetails and in the footer a OrderTotal control.

What I would like to do though is that when i select the SupplierID cbo in
frmOrders for the ProductID in fsubOrderDetails to only show selected
Products from that supplier.

Is this possible?
--
Thanks
WoodyAccess

Glad I could help. There is something you may want to consider, though.
Are you storing ProductCode, UnitType, and other fields from tblProducts
in
tblOrderDetails? If so, there's quite a bit of redundancy there. It
would
probably be better if you store only the ProductID and the UnitPrice, and
just link to the other fields. If on the other hand you are linking to
all
of the fields, what do you plan to do when the price changes?
In general you can use a query instead of a table as the record source
for a
form or report. It is a good way of sorting the records, can be used for
such things as combining first and last names, and is used to define
parameters such as a date range or product category or whatever. For
various reasons, some queries cannot be modified (you cannot add or edit
data), but as long as that isn't happening a query is fine.
You can build a report just as you built the form, with a main report and
a
subreport. Have a look at the report's Sorting and Grouping dialog,
which
can be very useful.
The tick you would add must be something in the web-based newsreader.
Since
I use Outlook Express as my newsreader I can't offer any insight into the
web-based version.
Good luck with the project.

Hi there Bruce, me again. I think my Database is progressing well now.

I'm not sure if this is correct but my Order and Order Details forms
are
both based on queries(?)

frmOrders - is based on the query when selecting SupplierID (from
tblOrders)
the tbl Suppliers details (Address etc) come up and the OrderID,
OrderDate
and TotalOrderCost (from tblOrders) are also on the form.

The fsubOrdersDetails is based on a query where selecting the ProductID
field (from tblOrderDetails) the relevant tblProducts (ProductCode,
UnitType,
UnitPrice, Dicontinued) all appear as well as the tblOrderDetails info
(Order
ID, Quantity and SubTotal).

Everything seems to work fine. Thanks for all the help. I am now going
to
try to create and A4 document/Report that will print up the Individual
Orders.

Wish me luck and thanks again for all your help.

p.s. How do I put a tick on this post to say my question has been
answered?
(to help out others)
--
Thanks
WoodyAccess


:

Glad to hear it helped.

Before you get too far with OrderDetails, consider this possible
scenario:
Each order may have many products, and each product may be associated
with
many orders. That is a many-to-many relationship, as I mentioned.
Since
you can't make a many-to-many relationship directly between two
tables, a
third table, often known as a junction table, is needed to resolve the
relationship. That third table is OrderDetails. As you can see from
my
first reply, there are two FKs in that table: OrderID and ProductID
(which I
mistakenly wrote as ProductCode). I should mention there is no
requirement
that joined fields have the same name, but it simplifies explanations.
You
may wish to call the FK field ProductID_OD, as it can help to keep
things
straight if both ProductID fields (PK and FK) are referenced in code
or
included in a query.

In a typical situation frmOrder will contain a subform (fsubDetails)
bound
to tblOrderDetails. You select the product from a combo box (cboProd)
on
fsubDetails. The combo box Row Source is tblProduct (the other table
in
a
one-to-many with tblOrderDetails). Its bound field is ProductID (the
FK
field), and its visible column is ProdDescription. Its row source has
a
column for UnitPrice, and may have other columns for ProductCode, etc.

Your actual situation may differ, so I won't go into a lot of details.
For
instance, if you are purchasing commodities the price may vary from
day-to-day, so you will probably want to enter the price as part of
the
OrderDetails record. In other cases the price may change
infrequently,
so
it would be better as a field in tblProducts, which is the situation I
assumed. In any case, you will probably want to store the UnitPrice
in
tblOrderDetails. The other Product fields, such as ProdCode, should
not
be
stored in tblOrderDetails. It may be best to start with a two column
combo
box containing ProductID and ProdDescription, similar to the method
already
described for adding the Supplier to the Order.

Thanks BruceM worked like a charm. In your 2 replies I have already
learnt
a
lot about Access, you explain it very well and easy to understand
(to a
novice). I will try and do the same with the products and then try
and
progress to the order details.
--
Thanks
WoodyAccess


:

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.



message
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

 

[Guest]

Where do I input the sub? Everytime I do it by trying to go to the rowsource
on properties it directs me into a query?

 

[BruceM]

I don't know if it is possible to assign a variable row source by using the
combo box Row Source property. If it is possible, I don't know how. What
need to do is to assign the Row Source programatically. With the form is
open in design view, click View > Code; a window will open up with all of
the code that is associated with that form. This is the form's code module.
Click Insert > Procedure. Choose Public and Sub, give it a name such as
ProdRowSource, and click OK. This will bring you to something like this:
Public Sub ProdRowSource()

End Sub

Insert the code between those two lines, adjusting as needed to accomodate
your field names and other details.

Now, with the form open in design view, click View > Properties. Click the
Events tab, and click next to Current. Click the three dots, click Code
Builder, and click OK. Between the two lines:

Private Sub Form_Current()

End Sub

add:

Call ProdRowSource()

The whole thing, with error handling:

Private Sub Form_Current()

On Error GoTo ProcErr

Call ProdRowSource()

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error #" & Err.Number & ", " & Err.Description & " -
ProdRowSource"
Resume ProcExit

End Sub

Follow the same general procedure to call ProdRowSource from the After
Update event for the combo box on the PO form from which you select the
Supplier. The advantage of using the named procedure is that you can use it
form both locations, but don't need to copy it to both places.

You could place this code into its own code module. It may be easier to
work with that way. Rather than clicking Insert > Procedure you would do
Insert > Module. After that you would do Insert > Sub and proceed as
before. The Sub call is the same in either case.

You could also write the code as a function rather than as a sub. One
advantage to this is that it simplifies the function call if there is no
other code associated with the event. For instance, in the combo box After
Update event, rather than opening a code window you could just type the
function name in the white space next to the After Update in the Properties
window:
=ProdRowSource()

If other code is needed (in the form's Current event, for instance) you can
still call the function as you would a sub.

This last part about using a function is from memory, and I wonder if I have
left out something, but I think it is accurate.