By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,383 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

Automated calculations in Access.

100+
P: 118
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
May 19 '07 #1
Share this Question
Share on Google+
7 Replies


P: 32
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?
May 19 '07 #2

100+
P: 118
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
May 19 '07 #3

ADezii
Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE tblStock SET tblStock.[Stock Level] = [Stock Level]+[Re-Order Quantity] " _
  4.           & "WHERE tblStock.[Stock Level]<[Re-Order Level];"
  5.  
  6. DoCmd.SetWarnings False
  7.   DoCmd.RunSQL strSQL
  8. DoCmd.SetWarnings True
May 19 '07 #4

100+
P: 118
'Execute this code wherever you like in order to replenish your Stock Level when it falls below the Re-Order Level:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE tblStock SET tblStock.[Stock Level] = [Stock Level]+[Re-Order Quantity] " _
  4.           & "WHERE tblStock.[Stock Level]<[Re-Order Level];"
  5.  
  6. DoCmd.SetWarnings False
  7.   DoCmd.RunSQL strSQL
  8. 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
May 21 '07 #5

ADezii
Expert 5K+
P: 8,638
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.
May 21 '07 #6

100+
P: 118
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
May 21 '07 #7

ADezii
Expert 5K+
P: 8,638
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:
    Expand|Select|Wrap|Line Numbers
    1. If Me![Stock Level] < Me![Re-Order Quantity] Then
    2.   Me!cmdReorderReport.Enabled = True
    3. Else
    4.   Me!cmdReorderReport.Enabled = False
    5. 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:
    Expand|Select|Wrap|Line Numbers
    1. 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.
May 22 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.