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.
Here is a sample of the relationships and the fields used:
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 all the help you can give me 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.
Here is a sample of the relationships and the fields used:
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 all the help you can give me for these
problems.
Thankyou.
Daniel Crook.