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

Show message on certian conditions

P: 10
I have an Access 2003 database that I'm building. In part of this DB I am tracking repair parts being ordered and used. I have a field called ReorderLevel that tells me when a parts on hand quantity drops to this level or below it needs to be reordered. I then have a UnitsOnHand unbound text field that is pulling the number of units on hand from an unbound text field also called UnitsOnHand in a subform (TblPartsTransaction subform). The UnitsOnHand text field in the subform is the field that calculates the amount of units on hand. What I want to do is have a message box pop up if the units on hand amount drops to or below the reorder level amount. I am not exactly sure where to start for this one. I'm a little confused on the code as I'm not very proficient with that. So far this is what I have for the message box, I believe I have this correct.
Expand|Select|Wrap|Line Numbers
  1. MsgBox("This part needs to be reordered. Please do so as soon as possible.",576,"Reorder Parts")<>1
Also once I have the code put together I'm not sure where to put it?

Any help would be greatly appreciated.
Dec 20 '07 #1
Share this Question
Share on Google+
5 Replies


P: 68
I have an Access 2003 database that I'm building. In part of this DB I am tracking repair parts being ordered and used. I have a field called ReorderLevel that tells me when a parts on hand quantity drops to this level or below it needs to be reordered. I then have a UnitsOnHand unbound text field that is pulling the number of units on hand from an unbound text field also called UnitsOnHand in a subform (TblPartsTransaction subform). The UnitsOnHand text field in the subform is the field that calculates the amount of units on hand. What I want to do is have a message box pop up if the units on hand amount drops to or below the reorder level amount. I am not exactly sure where to start for this one. I'm a little confused on the code as I'm not very proficient with that. So far this is what I have for the message box, I believe I have this correct.
Expand|Select|Wrap|Line Numbers
  1. MsgBox("This part needs to be reordered. Please do so as soon as possible.",576,"Reorder Parts")<>1
Also once I have the code put together I'm not sure where to put it?

Any help would be greatly appreciated.
Within the field that's calculating the current total in stock, you need to evaluate that number in the Afterupdate or Change event for that control, i.e.

Expand|Select|Wrap|Line Numbers
  1. If UnitsOnHand < 10 Then
  2. MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Parts"
  3. End If
I imagine you have the part no as a field somewhere on your form. Was this what the 576 is?You can incorporate that in your message like this (for example)...

Expand|Select|Wrap|Line Numbers
  1. MsgBox "Part " & me.PartNo & " needs to be reordered. Please do so as soon as possible."
....where my 'me.PartNo' is a field on the form containing the part number.

HTH

Kevin
Dec 20 '07 #2

P: 10
Within the field that's calculating the current total in stock, you need to evaluate that number in the Afterupdate or Change event for that control, i.e.

Expand|Select|Wrap|Line Numbers
  1. If UnitsOnHand < 10 Then
  2. MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Parts"
  3. End If
I imagine you have the part no as a field somewhere on your form. Was this what the 576 is?You can incorporate that in your message like this (for example)...

Expand|Select|Wrap|Line Numbers
  1. MsgBox "Part " & me.PartNo & " needs to be reordered. Please do so as soon as possible."
....where my 'me.PartNo' is a field on the form containing the part number.

HTH

Kevin
Thanks for all of the help so far!

The ReorderLevel is set different for different parts so I need this to popup the Message Box when the UnitsOnHand is equal to or less than the value entered in the ReorderLevel Field. I can't set the ReorderLevel for everything the same.
Dec 20 '07 #3

jaxjagfan
Expert 100+
P: 254
Within the field that's calculating the current total in stock, you need to evaluate that number in the Afterupdate or Change event for that control, i.e.

Expand|Select|Wrap|Line Numbers
  1. If UnitsOnHand < 10 Then
  2. MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Parts"
  3. End If
I imagine you have the part no as a field somewhere on your form. Was this what the 576 is?You can incorporate that in your message like this (for example)...

Expand|Select|Wrap|Line Numbers
  1. MsgBox "Part " & me.PartNo & " needs to be reordered. Please do so as soon as possible."
....where my 'me.PartNo' is a field on the form containing the part number.

HTH

Kevin
Modify what Kevin sent you slightly:

Expand|Select|Wrap|Line Numbers
  1. If UnitsOnHand <= ReOrderLevel Then
  2. MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
  3. End If
or if including part number as Kevin suggests (I would also - spent 16 years as marine engineer)

Expand|Select|Wrap|Line Numbers
  1. If UnitsOnHand <= ReOrderLevel Then
  2. MsgBox "Part " & partno & " needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
  3. End If
If it is a busy operation I would probably generate a report and send it to print as well.
Dec 20 '07 #4

P: 10
Modify what Kevin sent you slightly:

Expand|Select|Wrap|Line Numbers
  1. If UnitsOnHand <= ReOrderLevel Then
  2. MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
  3. End If
or if including part number as Kevin suggests (I would also - spent 16 years as marine engineer)

Expand|Select|Wrap|Line Numbers
  1. If UnitsOnHand <= ReOrderLevel Then
  2. MsgBox "Part " & partno & " needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
  3. End If
If it is a busy operation I would probably generate a report and send it to print as well.

I have tried putting the code into the Afterupdate as well as the Change Events for the UnitsOnHand control on the Form as well as the Subform. No matter where I put the code it doesn't do anything. I have changed the values to be equal and less than ReorderLevel but there is still no change. I also tried adjusting the code when I put it in the subform UnitsOnHand contrel
Expand|Select|Wrap|Line Numbers
  1. If UnitsOnHand <= FrmParts.ReorderLevel Then
  2. MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
  3. End If
This still didn't do anything.


Here is the control source for the UnitsOnHand control on the form (FrmParts)
Expand|Select|Wrap|Line Numbers
  1. =[TblPartsTransaction subform].Form!UnitsOnHand
Here is the calculation that is being made by the UnitsOnHand control on the subform
Expand|Select|Wrap|Line Numbers
  1. =Sum(nz([PartsReceived])-nz([PartsShrinkage])-nz([PartsUsed]))
I am also assuming that this code goes in between the corresponding Private Sub and End Sub tags.


I hope this additional information helps explain everything thats going on. Thanks for all of your help so far.
Dec 20 '07 #5

P: 68
I have tried putting the code into the Afterupdate as well as the Change Events for the UnitsOnHand control on the Form as well as the Subform. No matter where I put the code it doesn't do anything. I have changed the values to be equal and less than ReorderLevel but there is still no change. I also tried adjusting the code when I put it in the subform UnitsOnHand contrel
Expand|Select|Wrap|Line Numbers
  1. If UnitsOnHand <= FrmParts.ReorderLevel Then
  2. MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
  3. End If
This still didn't do anything.


Here is the control source for the UnitsOnHand control on the form (FrmParts)
Expand|Select|Wrap|Line Numbers
  1. =[TblPartsTransaction subform].Form!UnitsOnHand
Here is the calculation that is being made by the UnitsOnHand control on the subform
Expand|Select|Wrap|Line Numbers
  1. =Sum(nz([PartsReceived])-nz([PartsShrinkage])-nz([PartsUsed]))
I am also assuming that this code goes in between the corresponding Private Sub and End Sub tags.


I hope this additional information helps explain everything thats going on. Thanks for all of your help so far.
I think you probably need to put the msgbox routine within whichever control reduces the stock level, i.e. if for instance you have a field where you enter the number of units you are now going to take out of stock, or a command button that (metaphorically) reduces the stock level, use the relevant event of that control to evaluate and then run the msg if appropriate, i.e. something like this;

Expand|Select|Wrap|Line Numbers
  1. If Sum(nz([PartsReceived])-nz([PartsShrinkage])-nz([PartsUsed])) <= 500 then
  2. 'message box bit here
  3. end if
To have the a contextual minimum stock level instead of the 500, one way or another you need to pass the items minimum level to the equation. If you can include that field on your form, perhaps as a hidden field, you'd just change the string to:

Expand|Select|Wrap|Line Numbers
  1. If Sum(nz([PartsReceived])-nz([PartsShrinkage])-nz([PartsUsed])) <= me.MinPartLevel then
  2. 'message box bit here
  3. end if
... where MinPartLevel is your field name. Alternatively you could use a DLookup function to return the variable, then include that in the calculation string

I hope that gets you closer
Kevin
Dec 21 '07 #6

Post your reply

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