Event based on date

[Homegrownandy]

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.

[Homegrownandy]

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.

[JeroenMioch]

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.

    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.

[Homegrownandy]

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.

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?

[JeroenMioch]

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.

[Homegrownandy]

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

[JeroenMioch]

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.

[Homegrownandy]

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

[JeroenMioch]

No problem at all mate, nice to be able to help.
Please mark the thread as solved.