Results 1 to 11 of 11
  1. #1
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19

    Automatic alerts at a certain volume

    Hello,



    Back again with a noob request

    I have a subform within a form that I use to track volume data for individual part numbers based on codename, date, and volume count.

    Each month I receive data and record it in the subform table.

    It is my wish to have an automatic alert (msgbox I imagine), to alert me when a certain volume is reached for a particular codename. Ideally, I would have an alert for multiple codenames, each with their respective unique volume count trigger.

    The idea is that at certain volumes we experience a price reduction, and I would like to alert myself when we should apply that cost reduction.

    I hope this makes sense! Thanks in advance for any guidance.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd have a table of codenames and volumes (or add a volume field to an existing codenames table). Depending on your table structure, you can use that to test if existing volumes exceed that amount. Depending on the desired interval of testing, you can test on startup, during your monthly import, or with the timer event of a form.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Thank you for your reply!

    The data source of the subform in which I track volume is a table with codenames and volumes.

    Can you explain in more detail how I might write the code to test if the volume levels have been met? On startup seems like a good frequency/interval.

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Hard to say without seeing how the table(s) are designed and what the data looks like. First thought is a query that can compare the current volume with the threshold and returns any over the threshold. You could present that in a form, or use a recordset to create a message box, or...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I've done a couple of something similar things to this before. One was a simple query that was run on startup (based on the logging in user) that if the query returned any records, they were advised they had x non-compliant items. The message box gave them the option to review them at that time or not. If yes, the records were presented as a locked datasheet. If not, it was a constant nag until they reviewed them (well, at least opened the query) which flagged them as having been reviewed.

    The other was more complex. A pc not used by anyone but always left on ran Task Scheduler every night and updated a whole bunch of local copies of ODBC data; most of them not mine. My particular update involved a query run by the startup routine that if it found a different type of suspect data built an html email message with all of the particulars and sent an email using CDO. My db knew if the routine was being started by a regular user or by this unmanned pc.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Your first solution sounds like it might work for my scenario.

    The problem is, I am not experienced enough to understand how to write such a query.

    How do I write parameters that return data in a query, and then present that query on startup, based on a certain volume point?

  7. #7
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19

    Partially Solved

    So I have some VBA code based on From_Load and it works, almost.

    The code is as follows:

    Private Sub Form_Load()

    If (DSum("Volume", "tblVolume", "[CodeName]='CODENAME'") >= 353150) Then
    MsgBox ("Update CODENAME to NEWPRICE")
    Else
    MsgBox ("Placeholder")

    End If

    End Sub

    This all works perfectly well. My next problem is getting the message to STOP displaying after I update the price and the volume is above that level.

    I tried adding an "AND" statement, but the price is in another table and I couldn't get it to work correctly.

    Any ideas?

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Stop displaying what? As written, if the IF returns False, you'll get a message "Placeholder".
    So you're summing all the values in a field from a table where another field has the text value CODENAME. If that works and you don't want the follow up message I pointed out, then add a line before Else - Exit Sub
    Not that it matters, but in a simple case like this, you don't even need the Else part:
    Code:
    If (DSum("Volume", "tblVolume", "[CodeName]='CODENAME'") >= 353150) Then
       MsgBox ("Update CODENAME to NEWPRICE")
       Exit Sub
    End If
    
    MsgBox ("Placeholder")
    
    End Sub

  9. #9
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    I'm sorry, I did a poor job of explaining.

    The "else" is a placeholder for further nested Ifs that will trigger at other volume counts.

    The point of this function is to alert me to change the price of a certain part at a certain volume.

    My issue is, the msgbox will keep prompting me even after I update the price after the first alert. In other words, as I continue to add volumes, the volume count will continue to exceed the specified number (353150) and continue alerting me ("Update CODENAME...")

    I need to add additional parameters so that the msgbox will cease after I change the price. The most logical (to me) would be to add a parameter that targets the price. Therefore, once I update it, the msgbox will no longer alert me because only one of the parameters would be met (the volume).

    I am attempting to do it in the following way:

    Private Sub Form_Load()

    If (DSum("Volume", "tblVolume", "[CodeName]='CODENAME'") >= 353150) And (DLookup("Price", "tblPrice", "[CodeName]='CODENAME'") = OLDPRICE) Then
    MsgBox ("Update CODENAME to NEWPRICE")
    Else
    MsgBox ("placeholder")
    End If
    End Sub

    The problem with this attempted solution is that this, too, will always return TRUE. I input pricing data monthly, so I need to find a way to target only the most-recent price input. That way, once I am alerted by the original msgbox, I will update the price and the second parameter will now pull the most recent price, thereby returning FALSE and no longer displaying the msgbox.

    I hope this makes more sense

  10. #10
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK. Without seeing a data sample and knowing the business at hand, it's difficult to suggest anything. I think you're correct in that you need more than one parameter, or a different data structure. Not knowing the details, I cannot imagine what that parameter might be. If you time stamp or otherwise flag the update, depending on the test applied, it may never arise again. Is that what you want?

    Seems to me that if you had a table of threshold amounts paired with a discount amount it would probably be much simpler. For order volume x, you'd apply the discount for the threshold that is one level lower than x. If you can't do that, then you'll have to come up with a flag or pairing of values that makes the If statement True after the update or apply some other test. In short, I think we're back to what was recommended in post #4.

    As for the Else, a Select Case block is usually better than a whole bunch of If Then Else's.

  11. #11
    bsegge is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Solved:

    So this is the code I used to solve my problem. While it isn't along the lines of the suggestions given above, the help provided guided me in the right direction and I thank everyone for their input!

    the code:

    Private Sub Form_Load()
    Dim Msg As String
    Dim Msg2 As String
    Dim Msg3 As String
    Dim Msg4 As String
    Dim Msg5 As String
    Dim Style
    Dim Target As String
    Dim Ptype As String

    Style = vbOK + vbCritical
    Target = DMin("Price", "tblPrice", " [CodeName]='CODENAME'")
    Msg = "MSGMSGMSG"
    Msg2 = "MSGMSGMSG"
    Msg3 = "UMSGMSGMSG
    Msg4 = "MSGMSGMSG"
    Msg5 = "MSGMSGMSG"

    If (DSum("Volume", "tblVolume", "[CodeName]='CODENAME") >= 353150) And Target = 41.17 Then
    MsgBox Msg, Style
    ElseIf (DSum("Volume", "tblVolume", "[CodeName]='CODENAME'") >= 700000) And Target = 40.48 Then
    MsgBox Msg2, Style
    ElseIf (DSum("Volume", "tblVolume", "[CodeName]='CODENAME'") >= 703150) And Target = 39.48 Then
    MsgBox Msg3, Style
    ElseIf (DSum("Volume", "tblVolume", "[CodeName]='CODENAME") >= 1000000) And Target = 38.9 Then
    MsgBox Msg4, Style
    ElseIf (DSum("Volume", "tblVolume", "[CodeName]='CODENAME'") >= 1500000) And Target = 38.4 Then
    MsgBox Msg5, Style

    End If
    End Sub

    I've replaced some data with generic variables just in case.

    This is also probably not the very best solution, but it does work exactly how I need it to and I VERY new to writing code. So I am proud of it!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-15-2015, 05:50 PM
  2. Replies: 7
    Last Post: 11-19-2014, 05:20 PM
  3. Send automatic email alerts from access
    By RERM in forum Programming
    Replies: 5
    Last Post: 09-03-2013, 03:49 PM
  4. DoCmd.Beep Volume
    By cbrsix in forum Programming
    Replies: 2
    Last Post: 06-22-2012, 01:30 PM
  5. Duplicate value Alerts?
    By Delta223 in forum Access
    Replies: 2
    Last Post: 01-12-2012, 11:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums