Alert Message

[BlankReg]

Hi all, wondering if anyone can help me.

I have an access database, with a form called "Stock Maintenance", the controls on the form are QTY & Min QTY.

What i would like if possible, is once the Min QTY is below (10) an alert message/flag appears, saying QTY LOW Re-order Stock!!

I hope I have expressed what im trying to do.

any help on this matter would be greatly appricated.

 

[DCrake]

Simple Software Solutions

If you are using a bound form then you can control the alert easy.

On the oncurrent event of the form, say something one the lines of..

If Me.QtyInStock <=10 Then
    Me.QtyInStock.BackColor = vbRed
Else
    Me.QtyInStock.BackColor = vbWhite
End If

CodeMaster:

 

[BlankReg]

Hi There,

i added the code to the OnCurrent and i keep getting this error:

Compile Error:
Method or Data Member Not Found

any ideas on how i can solve it? Many thanks

 

[georgedwilkinson]

I hope you changed the control name to whatever your control name is. CodeMaster said: "along the lines of", not, "use this code verbatim." Big difference.

 

[BlankReg]

Hi Georgedwilkinson, i've managed to get it working and it looks great. any ideas how i could add a message box to the code, saying "Stock Low".

 

[fearoffours]

At it's simplest you could do this:

If Me.QtyInStock <=10 Then
    Me.QtyInStock.BackColor = vbRed
    MsgBox("Stock Low!")
Else
    Me.QtyInStock.BackColor = vbWhite
End If

Again, remember to change the control name!

 

[BlankReg]

Fearoffours,

Thank you for your reply. is there a way that I could create a message box, that says Stock Low..Send Email..Yes?No?

Once Yes is selected, Microsoft Outlook opens then I can send an email to the admin team asking them to purchase new Stock.

thank you once again!

 

[fearoffours]

I can only help a little bit I'm afraid. The code to send the email will include DoCmd.SendObject() , but I'm a newbie too and haven't quite deciphered how to send NoObject but still set the To, Subject and message text. But look into the help files and search the forum I'm sure the answers there somewhere!

However I can get you started by replacing the existing code with this:

Dim intAnswer as Integer

If Me.QtyInStock <=10 Then
    Me.QtyInStock.BackColor = vbRed
    intAnswer = MsgBox("Stock low!" & vbCrLf & "SendEmail?", vbQuestion + vbYesNo)
    If intAnswer = vbYes Then
        ' Code to send email here
    EndIf
Else
    Me.QtyInStock.BackColor = vbWhite
End If

Hope this helps a bit and any errors or bad formatting is picked up by someone with more experience!

 

[BlankReg]

Thank you for your help FearOffOurs, I really do appricate it. I've mananged to figure out the DoCmd.SendObject and it opens up Microsoft Outlook, which is exactly what i wanted.

The main purpose of my database is to edit the Stock Qty, the user selects from two combo boxes, 1 called Part No the other called Description. the problem that keeps on occuring, everytime the user selects from either one of the combo boxes, the msgbox appears, saying "Stock Low, Send Email" i got told earlier on in the thread to set the event procedure to the OnCurrent, perhaps im using the wrong one?

Maybe, there's a way around that problem, that you may know off? thanks again

 

[fearoffours]

Not sure without seeing the database, and I won't be able to help with that for a while seeing as I'm away from my Access computer... but if you can upload the database here, or elsewhere I'll take a look.

 

[BlankReg]

fearoffours i have upload the database. thank you for all your help

 

[fearoffours]

Ah sorry you're using a more recent version of access than me (I'm using 2000).

 

[BlankReg]

i have changed the file to access 2000. thanks again

 

[fearoffours]

Hi BlankReg.
Because the only way that a new form is loaded is through those combo boxes, I've added the code given above to the code that you already have in AfterUpdate of each of the 2 comboboxes. (After DoCmd.FindRecord is called of course!)

This has the desired effect, although wouldn't trigger on first opening the form, or if you opened the form from some other way, so it hardly feels like the best solution. I had a quick look at when each of the different events trigger, and I tried the code in a variety of different events, but none seemed to work... I'm sure the answer is out there somewhere! I've attached the database with the code inserted. remember to add some testing values for the QTY field (I added some but removed them so as not to ruin your data!)

 

[BlankReg]

Hi Fearoffours,

Thank you very much, the database works a treat and is exactly what I was after.

One slight problem, which i hope you'll be able to help with me. When the message box appears saying "Stock Low, Send Email?" When i Click On Yes, another box appears asking me what format I want to send the attachement in, I then have to Enter the To.../ Cc.. and so on.

Do you think there could be an easier away around this, so the email knows straight away who to send it too. eg: admin@example.co.uk

Thanks once again

 

[fearoffours]

The code to send the email will include DoCmd.SendObject() , but I'm a newbie too and haven't quite deciphered how to send NoObject but still set the To, Subject and message text. But look into the help files and search the forum I'm sure the answers there somewhere!

Well, I looked for it myself.
Replace the If intAnswer part of your code with this:

        If intAnswer = vbYes Then
            ' Code to send email here
            DoCmd.SendObject acSendForm, "Stock Maintainence", acFormatRTF, _
                "you@yourmail.com", , , "Stock low on " & Me.cboHorizonPartNo, _
                "There are just " & Me.QTY & " of part " & Me.cboHorizonPartNo & " left. you may want to reorder", True
        End If

This sends the attchment in RTF format. Check the Help file on SendObject for the other options. You'll see I've made it add the part number and quantity to the message text and the part no to the subject. You can change these texts if you want to. Obviously you need to put in the correct email address!
The 2 blank strings (where there are just commas) are for cc and bcc fields if you want to fill them in. Change the last 'True' to 'False' if you want the email to send automatically without the user being able to edit it.

Hope this helps, there's loads more info in the help files, though I know they can be difficult to decipher!

 

[BlankReg]

hi again.

the code works an absolute treat and looks fantastic.

just a quickie really, where you've made it add the QTY & part no. how would i add in the description?

thank you once again

 

[fearoffours]

The & symbol concatenates (joins together) strings and variables. A string (length of text) needs to be enclosed in within quotes, but variable (eg field value from a form) must not be.

So the line
"There are just " & Me.QTY & " of part " & Me.cboHorizonPartNo & " left. you may want to reorder",

Needs to become:
"There are just " & Me.QTY & " of part " & Me.cboHorizonPartNo & "(" & Me.cboDescription & ") left. You may want to reorder.",

You can see that I've added brackets around the description. It's quite easy to change this to anything you want.

 

[BlankReg]

I have learnt so much from you, im truly greatful.

I seem to be occuring some sort of problem though, i typed in;

"There are just " & Me.QTY & " of part " & Me.cboHorizonPartNo & "(" & Me.cboDescription & ") left. You may want to reorder.",

but this error message keeps on appearning,

Compile error: expected: expression

any idea what that means? thank you for all your help

 

[fearoffours]

I couldn't recreate that error message but I did realise that I was using the wrong field - Me.cboDescription should actually be Me.txtDescription . That might solve your problem.