Show message on certian conditions

**Kevin Wilcox** · Dec 20 '07, 03:37 PM

Originally posted by BaneMajik

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 (TblPartsTransa ction 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.

Code:

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.

Code:

If UnitsOnHand < 10 Then
MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Parts"
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)...

Code:

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

**BaneMajik** · Dec 20 '07, 05:13 PM

Originally posted by Kevin Wilcox

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.

Code:

If UnitsOnHand < 10 Then
MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Parts"
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)...

Code:

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.

**jaxjagfan** · Dec 20 '07, 07:41 PM

Originally posted by Kevin Wilcox

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.

Code:

If UnitsOnHand < 10 Then
MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Parts"
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)...

Code:

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:

Code:

If UnitsOnHand <= ReOrderLevel Then
MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
End If

or if including part number as Kevin suggests (I would also - spent 16 years as marine engineer)

Code:

If UnitsOnHand <= ReOrderLevel Then
MsgBox "Part " & partno & " needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
End If

If it is a busy operation I would probably generate a report and send it to print as well.

**BaneMajik** · Dec 20 '07, 09:34 PM

Originally posted by jaxjagfan

Modify what Kevin sent you slightly:

Code:

If UnitsOnHand <= ReOrderLevel Then
MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
End If

or if including part number as Kevin suggests (I would also - spent 16 years as marine engineer)

Code:

If UnitsOnHand <= ReOrderLevel Then
MsgBox "Part " & partno & " needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
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

Code:

If UnitsOnHand <= FrmParts.ReorderLevel Then
MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
End If

This still didn't do anything.

Here is the control source for the UnitsOnHand control on the form (FrmParts)

Code:

=[TblPartsTransaction subform].Form!UnitsOnHand

Here is the calculation that is being made by the UnitsOnHand control on the subform

Code:

=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.

**Kevin Wilcox** · Dec 21 '07, 09:58 AM

Originally posted by BaneMajik

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

Code:

If UnitsOnHand <= FrmParts.ReorderLevel Then
MsgBox "This part needs to be reordered. Please do so as soon as possible.", vbInformation + vbOKOnly + vbDefaultButton1, "Reorder Part"
End If

This still didn't do anything.

Here is the control source for the UnitsOnHand control on the form (FrmParts)

Code:

=[TblPartsTransaction subform].Form!UnitsOnHand

Here is the calculation that is being made by the UnitsOnHand control on the subform

Code:

=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;

Code:

If Sum(nz([PartsReceived])-nz([PartsShrinkage])-nz([PartsUsed])) <= 500 then
'message box bit here
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:

Code:

If Sum(nz([PartsReceived])-nz([PartsShrinkage])-nz([PartsUsed])) <= me.MinPartLevel then
'message box bit here
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

Show message on certian conditions

Show message on certian conditions

Comment

Comment

Comment

Comment

Comment