Automated calculations in Access.

[helraizer1]

Hey, I have a table called TblStock with 10 columns but 3 which need the focus here. These three columns are 'Stock Level', 'Re-order Level' and 'Re-order Quantity'.

The Stock level fields contain a number between 1 and ~4'000. The Re-order level feilds all contain either '1,000' or '1,500'; the Re-order Quantity fields contain the number '3,000'.

What I would like to do is make it, either automated or via a query, when the Stock Level field value falls/is below the value in the Re-Order Level field it should add the 3,000 from the Re-Order Quantity field to the Stock Level value.

So if the value of Stock level is 237 the database should detect that this is below the value of the Re-Order Level field and therefore add 3,000 to it so the Stock level is now 3,237.

Any ideas on how to do this?

Sam

[micksitup]

i have just finished working on something similar. Its a very large piece of code.

the way i worked it is to look at what is sold to customers as a PRODUCT, but what is bought in by you is STOCK.

for example

a can of coke (product) is made up of a can of coke (Stock)

a cheese sandwich (product) is made up of bread, butter and cheese (stock)


Do you have this currently?

[helraizer1]

i have just finished working on something similar. Its a very large piece of code.

the way i worked it is to look at what is sold to customers as a PRODUCT, but what is bought in by you is STOCK.

for example

a can of coke (product) is made up of a can of coke (Stock)

a cheese sandwich (product) is made up of bread, butter and cheese (stock)


Do you have this currently?

This isn't quite like that; it is for a warehouse system so it is listing the products in stock.
The stock levels are fixed, for this particular thing to begin with. So if the stock level is below the certain value (re-order level) it is replenished with (added onto) the amount in the third column, the Re-order Quantity value.

I hope this helps?

Any ideas?

Sam

[ADezii]

Hey, I have a table called TblStock with 10 columns but 3 which need the focus here. These three columns are 'Stock Level', 'Re-order Level' and 'Re-order Quantity'.

The Stock level fields contain a number between 1 and ~4'000. The Re-order level feilds all contain either '1,000' or '1,500'; the Re-order Quantity fields contain the number '3,000'.

What I would like to do is make it, either automated or via a query, when the Stock Level field value falls/is below the value in the Re-Order Level field it should add the 3,000 from the Re-Order Quantity field to the Stock Level value.

So if the value of Stock level is 237 the database should detect that this is below the value of the Re-Order Level field and therefore add 3,000 to it so the Stock level is now 3,237.

Any ideas on how to do this?

Sam

'Execute this code wherever you like in order to replenish your Stock Level when it falls below the Re-Order Level:

Dim strSQL As String
 
strSQL = "UPDATE tblStock SET tblStock.[Stock Level] = [Stock Level]+[Re-Order Quantity] " _
          & "WHERE tblStock.[Stock Level]<[Re-Order Level];"
 
DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

[helraizer1]

'Execute this code wherever you like in order to replenish your Stock Level when it falls below the Re-Order Level:

Dim strSQL As String
 
strSQL = "UPDATE tblStock SET tblStock.[Stock Level] = [Stock Level]+[Re-Order Quantity] " _
          & "WHERE tblStock.[Stock Level]<[Re-Order Level];"
 
DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Cool, that worked! Thanks. =D

There are also columns with the Supplier ID and Address etc.
Is there any way that, using the similar code, if the Stock level is below the order quantity it will create (preferably) a report for that particular item? Listing the Order details, etc. Supplier details from the table.

Any ideas?

Sam

[ADezii]

Cool, that worked! Thanks. =D

There are also columns with the Supplier ID and Address etc.
Is there any way that, using the similar code, if the Stock level is below the order quantity it will create (preferably) a report for that particular item? Listing the Order details, etc. Supplier details from the table.

Any ideas?

Sam

Yes, a Report can be generated for a specific Stock Item if the Stock level is below the order quantity.

1. Is this Report going to be generated from a Form?
2. What Fields will the Report contain, what Tables do they reside in, and what is the Relationships between the Tables?
3. What is the Primary Key Field on the Form?
4. I think you get the idea by now.

[helraizer1]

Yes, a Report can be generated for a specific Stock Item if the Stock level is below the order quantity.

1. Is this Report going to be generated from a Form?
2. What Fields will the Report contain, what Tables do they reside in, and what is the Relationships between the Tables?
3. What is the Primary Key Field on the Form?
4. I think you get the idea by now.

If the TblStock.[stock level] value is less than the value of that in the [Re-order Level] then a report should be produced containing (going by ADezii's list):

1. Form for TblStock.
2. From TblStock: [Stock ID], [Description], [Colour], [Size]; respectively.
   -From TblStockLevel: [Re-Order Quantity].
   -From TblStockSupplier: [Supplier ID], [Supplier Name], [Address L1], [Address L2], [Supplier Town], [Supplier County], [Supplier Post Code], [Supplier Country], [Supplier Telephone]. Respectively.
   - TblStock is joined by a foreign key (in the TblStock) to a Primary key in the Supplier table [Supplier ID]
3. The Primary key in the form is the 'Stock ID'.

Hope that was clear and I wasn't being ignorant or anything? =)

Sam

[ADezii]

If the TblStock.[stock level] value is less than the value of that in the [Re-order Level] then a report should be produced containing (going by ADezii's list):

1. Form for TblStock.
2. From TblStock: [Stock ID], [Description], [Colour], [Size]; respectively.
   -From TblStockLevel: [Re-Order Quantity].
   -From TblStockSupplier: [Supplier ID], [Supplier Name], [Address L1], [Address L2], [Supplier Town], [Supplier County], [Supplier Post Code], [Supplier Country], [Supplier Telephone]. Respectively.
   - TblStock is joined by a foreign key (in the TblStock) to a Primary key in the Supplier table [Supplier ID]
3. The Primary key in the form is the 'Stock ID'.

Hope that was clear and I wasn't being ignorant or anything? =)

Sam

1. I do not see the [Stock Level] Field anywhere, but I'll assume it is on the Form.
2. Create a Command Button on the Form and call it cmdReorderReport.
3. In the Current() Event of your Form, place the following code:
   

   If Me![Stock Level] < Me![Re-Order Quantity] Then
     Me!cmdReorderReport.Enabled = True
   Else
     Me!cmdReorderReport.Enabled = False
   End If

4. Create a Report and name it rptReorder - include all necessary Fields in the Report, (from an underlying Query), especially [Stock ID].
5. In the Criteria Field of [Stock ID] within the Query, set the following Criteria:
   Forms!<Your Form Name>![Stock ID]
6. In the Click() Event of cmdReorderReport, place the following line of code:
   

   DoCmd.OpenReport "rptRoster", acViewPreview

7. cmdReorderReport will not be enabled unless [Stock Level] < [Re-Order Quantity]
8. If thsi Button is enabled then clicking on it will generate the Reorder Report for the specific item as indicated by the [Stock ID] Criteria in the Query.
9. Be sure to set the Query to the Record Source for the Report.
10. I know it is a mouthful but if have any other problems, please let us know and someone will be glad to assist you. Good luck.