Auto-notes

[Homegrownandy]

I would like for some events to trigger the creation of a 'note'.



A note in my system is contained in a table of its own and is any information relating to a job. Currently the user enters all notes but there are certain circumstances where this could be automated.

I would like a check box for job completion. When selected I want a note to be generated to say:

"Job completed" (by default any note added would have todays date in the current setup)

So the user finds the record and clicks complete and that's it.

Similarly I would like an event to occur when the start date is changed. something like:

"Job start date changed to (whatever the user input)"

The start date in this case would exist twice. Once in the notes and once on the jobs listed start date. This is how I want it because the start date in jobs can be changed and I want all of the information available at a later date.

In both cases it would be useful to also force the user to enter additional notes. Even if there are none and they click through (the field can remain empty).

How would I go about this?

thanks, Andy.

[ranman256]

in the form, when the user clicks the check box, the AFTERUPDATE event
run a query that updates the record. similar to:
sql = update table set [msg] = 'Job start date changed to (input)' where [keyID] = forms!frmEdit!txtID

sub chkComplete_afterupdate()
   if chkComplete.value then 
        docmd.setwarnings false
        docmd.openquery "quUpdMsgComplete"
        docmd.setwarnings true
   end if
end sub

[Homegrownandy]

I'll try your suggestion mate, hopefully I can get this working

[orange]

Job Completed is not something that would would be identified by a Note.
In your processing if all required fields in Job have valid data values, and the processing of the Job is complete --you could/would set JobStatus to Completed" (or similar) as part of th process.

You may have a logfile to show all sorts of info

JobStart, JobOnHold, JobCompleted, Job ParametersChanged, JobReviewdBy. JobAssignedTo....
this would give you many statistics to be used for Quality Control/Performance measurement etc.

I would use Notes for descriptions/issues/comments that may be used as documentation/reference.
Log and/or status files can be used for many purposes - audit, QA/QC, standards...

But I don't know your environment or requirements as well as you do.

[Homegrownandy]

sorry to be an idiot but could you explain this:
sql = update table set [msg] = 'Job start date changed to (input)' where [keyID] = forms!frmEdit!txtID

I get a syntax error when I paste it in.. I'm aware I need to change bits around to suit my form name, but what is the keyID here and the txt ID?

[Homegrownandy]

How would you go about implementing your suggestion orange?

I don't understand fully what you mean when you say part of the process, what you are saying there is exactly what I'm trying to do.

The main reason for this part of the database is... our clients change days we can work all the time. we need to document when the changes are made.. by who .. and why.

we need to use this information later if we have any disputes. (we can often get the blame for circumstances they have created.) so the notes in this sense are worth while, and the automation of date changed from "X"[today] with additional notes helps.

I'm open to suggestions providing I can understand them.

[orange]

Depends on your set up.
Lets say your job goes through stages/statuses.

Proposal
Initiation
Assembly
Inspection
Shipping

You have criteria for moving form one stage to another.
In the last stage (all required fields valued and vetted) -job is done so flag as Completed.

Key issue is What exactly is each stage.
What signifies that "this stage" is complete, so you can update the status?
We had applications involving government programs, funding conditions,...

announcement,preapplication, application, confirmation, project approval, project funding start,
project phase review, project phase acceptance,.....

each stage had conditions to be met, each condition has related error/info messages, there was a series of possible "next stages possible" based on current stage and conditions.

Just saw you have posted while I was typing.

Yes notes/comments to be used in any review dispute would be appropriate. For ease of reference and use, you may want to list the kinds of things that could arise/be relevant, then codify them so the messages could be more uniform-- and you could still use notes/comments as details/additional facts.
If dispute resolution is the primary use, I would recommend clarifying the terminology and "associated factors" and using a table of common terms ( to be monitored and adjusted as necessary). Clarification of terminology of any personal comments/notes would also be helpful. You really don't want 50 misspellings of terms, words etc when trying to solve an issue.

You have to adapt to the reality of your situation, but the above is intended as a guide.

You may get some ideas from
Allen Browne audit

or Martin Green

[Homegrownandy]

We don't have that much to track to be honest. I guess we could have:

Order received
Documents prepared
work carried out
O&M produced/approved.

Sometimes we may need to track additional data. if we need parts from the client for example. The only thing that signifies a completed stage is just if its been done or not to be honest. Maybe I'm missing something here.

But from the four listed, you are right, each stage is dependent on the last. Do you have something I could read as an example of how to set this up?

[AccessMastery]

for you needs i'll assume there is a table called tbl_Notes with the following fields (Date, Note, Record_ID)
where record ID is the unique record number for the table being edited


create a subroutine to update the table


Sub update_Notes(sNote as String, Rec_ID as Long)
Dim sSQL as string

sSQL="Insert Into tbl_Notes (Date, Note, Record_ID) values (#" & Date() & "#, '" & sNote & "', " & Rec_ID & ")"
currentdb.execute sSQL

end sub


to use the code add the AFTERUPDATE event to the Start date field


Update_Notes "Job start date changed to " & Me.txtStart_Date, Me.Mec_ID


This line will call the Update_Notes subroutine and pass the values to sNotes and Rec_ID adding a new note to the table with todays date
and the note that was passed to the Sub

[orange]

@AccessMastery

Why are you using Access 64bit? just curious.

Also, Date is a reserved word in Access.

[AccessMastery]

I'm using 64 bit because that is what the company provided, and you are right about the date reserved. Note_Date would be a better field name

[orange]

Andy,

Draw up a flow chart along these lines:

Start new job, assign stage 1
Are all required fields valued with valid data
Yes --- do X
No --- go back

You may also find, that listing the current status and possible next status depending on "meeting/not meeting your conditions".

Current Status -- Possible Next Status

100                       101 102 105  200
101                       102  500
102                       105...

[Homegrownandy]

Ill get back to this tomorrow, I have a more pressing issue as you may see in my new thread.

[Homegrownandy]

Hey orange thanks for the replies, we do have a flow chart on a white board here. How we operate revolves entirely around the install date. Documents are produced for upcoming work and then after the work is complete.

So in that sense there is no "state" for a job other than confirmed or not. Confirmed jobs do get moved around a lot and its helpful to track which documents have been made for each order (or similar).

From what you are saying this is what I'm thinking:

Have a status table... every job requires a status because we only have confirmed jobs (currently).

Then stage one: produce documents (Test plan, Risk assessment, survey)
Stage 2: carry out work on pre defined date
stage 3: produce Manuel.

In this case we need to meet three criteria before the stage 2 date. I could make a documents dashboard with a list of jobs needing documents in date order for when we are to carry out work, have some radio buttons on the form to I can filter just the risk assessments or test plans for example.

I don't know if what I just said matches your suggestion, but its what I'm taking from it. I have a list of a hundred things I would like to do to the database and how it can help us in the future, but for now I need to just use it for scheduling. The scheduling process is where the data is added, so I need to get that right so other people are at least putting in the raw data to work with.

So, all I really need right now is a way of seeing if a job is complete and when it was completed. where would you recommend storing the date? and should I create the status table for complete? so complete = PK_complete 1 rather than a note like you said.

Thanks for the help, I'm learning a lot from you guys.

[Homegrownandy]

@ access mastery

Thanks for the advice there mate, That will be useful. I'll have a go implementing this shortly.