Results 1 to 10 of 10
  1. #1
    ctoms is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2017
    Posts
    7

    Create a macro that will automatically open a message box when a field in a form is updated...

    and the value is between 2,900 and 3,200. Basically what I want is to enter the information into the form, and if I enter a value for the "Hour Meter" field that is within that range, a message box pops up and says "Lift is due for 3,000 Hour Service". I'm pretty new at using Access, so any help is greatly appreciated! Thanks!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Most (many?) of us here don't use macros, but if you think you can create an AfterUpdate event for your control (from the property sheet Event tab) then this should work.

    Code:
    Private Sub YourTextboxName_AfterUpdate()
         If Me.YourTextboxName > 2900 And Me.YourTextboxName < 3200 Then Msgbox "Lift is due for 3,000 Hour Service"
    End Sub
    Don't duplicate the first and last line, and provide the proper control references. The above is untested and assumes the values being checked are numbers and not text. It seems like a one-off solution though. I wouldn't write it that way for say, 10 different service messages.
    Last edited by Micron; 05-18-2017 at 03:15 PM. Reason: code correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    You will use the After Update event of that field to build a macro that checks the value entered. See the attached database for a little example - enter something between 2900 and 3200 in the form to see it in action. Go into design view of the form, select the HourMeter field and in the Event tab on the property sheet, open up the [Embedded Macro] in the After Update event to see the macro.

    This assumes you actually want to use the Access macro interface to do this, rather than plugging in a bit of VBA.
    Attached Files Attached Files

  4. #4
    ctoms is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2017
    Posts
    7

    Mismatched values

    Quote Originally Posted by Kudos View Post
    You will use the After Update event of that field to build a macro that checks the value entered. See the attached database for a little example - enter something between 2900 and 3200 in the form to see it in action. Go into design view of the form, select the HourMeter field and in the Event tab on the property sheet, open up the [Embedded Macro] in the After Update event to see the macro.

    This assumes you actually want to use the Access macro interface to do this, rather than plugging in a bit of VBA.
    I get an error message that says "mismatched values". I have the hour meter field as a number, so I'm not sure what the mismatched value is?

  5. #5
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    Are you able to strip and post your database? It's tricky to troubleshoot little things like this without poking around. At minimum, try to post screen shots of the various things that are feeding into this end result.

  6. #6
    ctoms is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2017
    Posts
    7

    How do I strip it?

    Quote Originally Posted by Kudos View Post
    Are you able to strip and post your database? It's tricky to troubleshoot little things like this without poking around. At minimum, try to post screen shots of the various things that are feeding into this end result.
    That shouldn't be a problem to do, I just need to know how to strip it. Like I said, I'm really new at this lol

  7. #7
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    By strip I just mean remove any sensitive data that you aren't able/willing to share. If there is none, just attach it as is.

  8. #8
    ctoms is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2017
    Posts
    7
    Stripped.zip
    Here it is. I stripped it, but had to attach as a .zip file because of the file size. It's pretty bare.

    One other question: can I have a message box pop up at certain date intervals? Like every 3 months, a box pops up and says "PM Service Due"? I didn't know if that was a possibility.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    One form field has an invalid control source (Invoice Amount). Not pertinent to your post, but I don't know if you're aware of it.
    There is nothing to compare the hour meter reading to, so for me, your original post is too vague since you mention 2,900 to 3,200 yet there's no data like that. So what would be the point of a macro that provides a message between 100 hours before to 200 after your hour meter values when they're always changing? You need a target (service due date) value.

    If this db represents your true model, I think you need to rethink the design; at least from the point of view that I'm making. Such as, are meter readings only done after a repair, or on a regular/daily basis? If the former, you could be well past the service due date before you know it. If the latter, you haven't set it up that way. You have "type" fields (equip, repair) without supporting type tables. There's no PK (primary key) in your repairs table. You can manage additions/edits to data such as equipment id via relationships, but if you remove a unit from the table, all related data (history) is gone. Are you sure you want that? Using the PK from the parent table is safer in that respect. If the attached mostly represents your final db schema, I'd advise digging a little deeper.

    Then there are other small issues (at this point) such as spaces in names (advisable to have no spaces, no special characters) and maybe bigger ones that I don't know if you're aware of (such as I can alter the unit id, which as I said, will cascade throughout). If all you want is an answer to your original question, I think we need to know what to base the hours comparison against - unless you want exactly what you posted.

  10. #10
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    I don't see the suggested macro in the db file you attached, so I've put it in your file and attached it. When I make an entry in that field, the requested message pops up if the entry is between 2900 and 3200.

    Does the hour meter get reset when it hits 3200? And it looks like you probably need a technician table, so that people can't key in the wrong tech number.

    Regarding your "PM Service Due" question, where would you want this to pop up? Is it due for each unit on its own schedule, or is a single pop up alert every three months all you want?
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 1
    Last Post: 03-17-2017, 11:52 AM
  2. Macro to Open Form Triggers Exclusive Access Message
    By snakatsu in forum Database Design
    Replies: 5
    Last Post: 11-10-2015, 10:46 PM
  3. Replies: 1
    Last Post: 10-29-2015, 07:03 AM
  4. Replies: 1
    Last Post: 08-20-2013, 02:17 PM
  5. Replies: 1
    Last Post: 04-28-2013, 09:43 PM

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