Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Event based on date

    I have a list of dates for upcoming work. Once that date has passed I would like an event to happen. Basically I need to update the related record with what happened. I'm not sure how to go about it, or how it could work.



    I would need the jobs requiring user input (due to a date passing) to be on their own form. then once they have been updated to leave that form. So a user can open it up, see what needs attention and deal with it.

    Anyone have any thoughts?

    Andy.

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Possibly a query where "job start date" is <today AND "job status" in progress. I'm just answering my own question haha. Still, feedback appreciated.

  3. #3
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    When you open your main form you can let your form remind you that there are jobs not done past their duedate.
    I would do this on the OnCurrent event of your form.

    Im guessing your main form has records on it and and ID for every record.
    There must me a control on your form that holds that record ID for it to work.

    Code:
        If DLookup("DateReminder", "YourTable", "ObjectID = fldObjectID") >= Date Then
        Me.fldReminder.caption = "Jobs not done"
        Else
        Me.fldReminder.caption = "Jobs are done"
        End If
    Im not sure what the setup of your form is, or if you even filter records but tell us more and we will help.

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I would need to make a new form for this so I am open for suggestions, My main form is literally a navigation menu right now.
    Code:
    There must me a control on your form that holds that record ID for it to work.
    Sorry for being stupid here, But what do you mean by this?

  5. #5
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    When users open my form they can then select wich records they want to see.
    I do this using a custom made filter.

    I have a textbox on my form and users can enter wich record they want to search.
    For example if they are looking for the empire state building they would enter "Empire state" and the record would be shown on the form.
    They can now lsee where its located, in wich state, its zip code, etc etc

    This would work fine in itself but when you get more and more features on your form, you would need a unique identifier for such objects.
    So, the Object ID for the empire state building could be any number you assign to it, or an autoID number.
    Acces will then assign each new record with a number.

    Now when the record is shown on my form i will see for example that that object's ID is "1"
    From there on i can refer to that number in my code, rather then referring to "the empire state building"

    When looking for jobs to be done in the empire state building i can refer to that number because its in the table that holds the data, AND its visible on my form.
    Acces now knows what i want to see.

    I hope this explains it a bit.

  6. #6
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Yes thanks. That's what I do anyway, I also have a search form for scheduling jobs. It pulls info from various tables relating to that job. So I understand the process, I was confused as to what a control was. On this form I'm making now then...

    It will show dates before today that are incomplete.

    These jobs either need to be complete or rescheduled for a later date. I might sound like I know what I'm doing, and sometimes I do. But I like to ask because I'm still learning. Often when I post this type of thread people will suggest a better way to do something.

    The on current event you speak of, this would just update the form with the latest information?
    So if I were to use this on my main page, I could have a counter of jobs that need to be addressed for example.

    thanks again

  7. #7
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Yes it does. The codes in the Form_current event are constantly updating when a recordset changes.
    So when your users look up a different record on screen they can see if there are jobs to do (for that specific record).

    You can offcourse also make the form so that your users can see all the items that need attention, rather then for a specific record. Thats up to you.

    Just for your info, a field is in your table, a control is on your form. Im Dutch so i was confused at first. To me they all are fields

    Try to implement my code in your form and let me know how it works out.
    If you want your form to show the number of all jobs in your to do list you need another piece of code. So let us know.

  8. #8
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Thanks for all the help. I have it working. I'm displaying all that need attention now, I had about 30 results. I'm down to 8 now!

    Also, I like the quote in your sig

  9. #9
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    No problem at all mate, nice to be able to help.
    Please mark the thread as solved.

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

Similar Threads

  1. AfterUpdate Event Based on Checkbox
    By lisa071 in forum Forms
    Replies: 3
    Last Post: 03-27-2015, 01:12 PM
  2. Execute based on event and value
    By netguy in forum Programming
    Replies: 11
    Last Post: 09-27-2014, 12:29 AM
  3. Combo Box event based on selection
    By tobydobo in forum Access
    Replies: 52
    Last Post: 01-20-2012, 07:26 PM
  4. Date event e-mail
    By Cran29 in forum Access
    Replies: 5
    Last Post: 06-21-2011, 03:31 PM
  5. Replies: 1
    Last Post: 07-12-2010, 12:00 PM

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