DanielCrook
Technical User
Hi,
I'm in the middle of constructing a database for a sole trader and
am having some difficulties with certain bits. My relationships are
based on 5 main tables: Customers, Products, Categories (of
products), Orders and Sales.
Customers, Products and Categories go straight through to create
forms for these areas. The Order form is constructed of fields
based on a query (Order Information) linking Orders with
Customers, and an Order Sub form based on a query (Order
Details information) linking Order Details with Products.
The Sales forms work in the same way as for the Orders.
The database is nearly finished but I am having the following
problems, which may need the use of Visual Basic of which I have
little knowledge:
1) No warning for stock level attaining a given minimum stock
limit. (eg. 5)
- In the Product form, the only basis for stock control is a field
called Amount in Stock.
- When scrolling through records in Products, how do set up a
pop-up message box / dialogue box to stop at those products with
stock less than 5, and it saying: "Order level is less than 5. Please
re-order", and then simply clicking on OK for it to continue going
through records? It's not the Validation rule and text for any field,
because that's when entering data only!
2) No control of product selection when taking orders
- In my Order form, when selecting the products to be ordered in
the Order sub-form, it allows me to select a product/s and type in
any amount of stock regardless of the fact that there may not be
enough in stock of that product in the Products form (Amount in
Stock field). There is no control. How do I do this?
3) On the decision that stock can be updated, say, at the end of
every week, (i.e. Order ID from 1-8, and Order date from ____ to
_______), the query will be run to update stock for those records
in Orders and then send them via an Append Query to another
table (Orders Archive and Order Details Archive), and finally run a
Delete query to delete them from the Orders form. THIS IS THE
ONLY LOGICAL WAY I FOUND.
Problems: 1) Stock can be updated more than once from a record.
How do I prevent that from happening?
2) What if the customer has not paid for an order? Do I have to
set a block for the field Date Paid : Yes/No, or otherwise stock will
be updated for those records where the customer has not paid, and
hence stock may not have to be updated after all.
4) Based on the database template Northwind, I tried to set up
"Sales by Year", but from orders made. I based it on the Order
Details query, where Order ID and Sub Total will be the only
necessary fields. I set up a Sales by Year dialogue box as a form, as
in Northwind, which would be appear to set the beginning and end
date for the year.
I had to add the criteria: Is Not Null And Between [Forms]![Sales
by Year Dialog]![Beginning Date] And [Forms]![Sales by Year
Dialog]![Ending Date]
For the Sales by Year Report, I created the report, copied the Event
Procedures for OnOpen and OnClose. I had to then set up the
Utility functions module as the other procedures were not
recognising certain parts of the other procedures.
I also copied the event procedures for OnClick for the OK and
Cancel buttons on the Sales by Year dialogue form.
It does run in some way but it doesn't do what the Norwthwind one
does.. the report does not open either…all a bit of a mess. I
checked the procedures, but cannot find any errors. Can you help
me with this?
Sorry for the long message… but I would appreciate help from
anyone for these problems.
Thankyou.
Daniel Crook.
I'm in the middle of constructing a database for a sole trader and
am having some difficulties with certain bits. My relationships are
based on 5 main tables: Customers, Products, Categories (of
products), Orders and Sales.
Customers, Products and Categories go straight through to create
forms for these areas. The Order form is constructed of fields
based on a query (Order Information) linking Orders with
Customers, and an Order Sub form based on a query (Order
Details information) linking Order Details with Products.
The Sales forms work in the same way as for the Orders.
The database is nearly finished but I am having the following
problems, which may need the use of Visual Basic of which I have
little knowledge:
1) No warning for stock level attaining a given minimum stock
limit. (eg. 5)
- In the Product form, the only basis for stock control is a field
called Amount in Stock.
- When scrolling through records in Products, how do set up a
pop-up message box / dialogue box to stop at those products with
stock less than 5, and it saying: "Order level is less than 5. Please
re-order", and then simply clicking on OK for it to continue going
through records? It's not the Validation rule and text for any field,
because that's when entering data only!
2) No control of product selection when taking orders
- In my Order form, when selecting the products to be ordered in
the Order sub-form, it allows me to select a product/s and type in
any amount of stock regardless of the fact that there may not be
enough in stock of that product in the Products form (Amount in
Stock field). There is no control. How do I do this?
3) On the decision that stock can be updated, say, at the end of
every week, (i.e. Order ID from 1-8, and Order date from ____ to
_______), the query will be run to update stock for those records
in Orders and then send them via an Append Query to another
table (Orders Archive and Order Details Archive), and finally run a
Delete query to delete them from the Orders form. THIS IS THE
ONLY LOGICAL WAY I FOUND.
Problems: 1) Stock can be updated more than once from a record.
How do I prevent that from happening?
2) What if the customer has not paid for an order? Do I have to
set a block for the field Date Paid : Yes/No, or otherwise stock will
be updated for those records where the customer has not paid, and
hence stock may not have to be updated after all.
4) Based on the database template Northwind, I tried to set up
"Sales by Year", but from orders made. I based it on the Order
Details query, where Order ID and Sub Total will be the only
necessary fields. I set up a Sales by Year dialogue box as a form, as
in Northwind, which would be appear to set the beginning and end
date for the year.
I had to add the criteria: Is Not Null And Between [Forms]![Sales
by Year Dialog]![Beginning Date] And [Forms]![Sales by Year
Dialog]![Ending Date]
For the Sales by Year Report, I created the report, copied the Event
Procedures for OnOpen and OnClose. I had to then set up the
Utility functions module as the other procedures were not
recognising certain parts of the other procedures.
I also copied the event procedures for OnClick for the OK and
Cancel buttons on the Sales by Year dialogue form.
It does run in some way but it doesn't do what the Norwthwind one
does.. the report does not open either…all a bit of a mess. I
checked the procedures, but cannot find any errors. Can you help
me with this?
Sorry for the long message… but I would appreciate help from
anyone for these problems.
Thankyou.
Daniel Crook.
