# Sending a date alarm\alert to outlook.



## peter1969 (Dec 11, 2010)

Hello everyone..
Very new to access 2007. What i'm trying to do is this... All the employees now need to have a hot works certificate updated every 6 months. Is there a way i can set up a table\form in access that sends out an email alert in outlook to the foreman?
I have no idea on coding or anything so any help would be appreciated.
The table would need:
Workers number
last name
first name
certificate
renewal date
Thank you for taking time to read my mail..


----------



## karlhaywood (Jan 17, 2010)

Have you created a table with those fields in?


----------



## peter1969 (Dec 11, 2010)

No i have not yet. But these are the basic fields needed.


----------



## peter1969 (Dec 11, 2010)

Ok have created a table and a quick wizard form......


----------



## karlhaywood (Jan 17, 2010)

Ok first of all create a table with the fields you want. Make the first field "ID" and make that field the primary key


----------



## peter1969 (Dec 11, 2010)

Ok that is done..


----------



## karlhaywood (Jan 17, 2010)

Ok now create a query based on that table with all the fields in


----------



## peter1969 (Dec 11, 2010)

Ok that is done... Thanks for the help btw...


----------



## karlhaywood (Jan 17, 2010)

Now create a form based on that query, then comes the coding


----------



## peter1969 (Dec 11, 2010)

Ok thats complete... Now comes the headache i suppose )


----------



## karlhaywood (Jan 17, 2010)

Ok i am just creating some code i should be back shortly


----------



## peter1969 (Dec 11, 2010)

No probs ty )


----------



## karlhaywood (Jan 17, 2010)

Ok i have just tested my version and it works perfect. On your form create a new text box and call it text0


----------



## peter1969 (Dec 11, 2010)

Ok ive done that. I also added to query and the table.


----------



## karlhaywood (Jan 17, 2010)

No No you dont need that in the table or query


----------



## peter1969 (Dec 11, 2010)

Ok sorry ive deleted it from the table and query. Now its just in the form )


----------



## peter1969 (Dec 11, 2010)

Ive deleted from the table and query


----------



## karlhaywood (Jan 17, 2010)

OK call workers Number text1
Lastname text2
firstname text3
certificate text4


----------



## peter1969 (Dec 11, 2010)

Where do i do this?


----------



## karlhaywood (Jan 17, 2010)

Right click on the text boxes in design view go to the "All" tab and change the name


----------



## peter1969 (Dec 11, 2010)

Ok all done )


----------



## karlhaywood (Jan 17, 2010)

Now create a blank button on the form


----------



## peter1969 (Dec 11, 2010)

Ok done


----------



## karlhaywood (Jan 17, 2010)

This might be a dumb question but you are using Outlook to send the emails right?


----------



## peter1969 (Dec 11, 2010)

Yes i want access to send outlook an alert email when the date is reached


----------



## karlhaywood (Jan 17, 2010)

Ok the code i have just wrote will actually send the details of what is on the form, is that ok? otherwise i dont think i can help you.


----------



## peter1969 (Dec 11, 2010)

Yes what i want is when the date is reached in the form an email is sent


----------



## karlhaywood (Jan 17, 2010)

Ok i can help you with the email part now and i will have to sleep on the rest and try and dream something up about the auto sending

put this code on the buttons OnClick event

Dim subject As String, Body As String
subject = [Text0]
Body = [text1] & Chr$(13) & [Text2] & Chr$(13) & [Text3] & Chr$(13) & [Text4]
DoCmd.SendObject , "Send an email", acForm, "youremailsaddressgoeshere", , , subject, Body, False

Makesure you put your email address in the correct part of the code and click save.


----------



## peter1969 (Dec 11, 2010)

Ok no problem. Again ty for all your help )


----------



## karlhaywood (Jan 17, 2010)

Put a dummy record on the form and press the send button see what happens


----------



## karlhaywood (Jan 17, 2010)

I can think of a way of doing it manually listen to this. When you open the database if a record is coming up for renewal access will tell you which one(s) then you can search for that record and just press the send button


----------



## peter1969 (Dec 11, 2010)

#Name? this appears in the text0 box


----------



## karlhaywood (Jan 17, 2010)

Ok zip the database up and post it on here i will take a look.


----------



## peter1969 (Dec 11, 2010)

I need to remove all the work details first.. I will do this tonight so its on for tomorrow...


----------



## karlhaywood (Jan 17, 2010)

Take a look at my version you select the name of the person from the drop down box, there details appear on the form then you click send. easy as that.


----------



## peter1969 (Dec 11, 2010)

That works perfectly )I really hope you can get your head around getting an email to send at a certain date....Thank you so much for your help )


----------



## karlhaywood (Jan 17, 2010)

Was that my version or yours?


----------



## peter1969 (Dec 11, 2010)

Your versio not mine........I will check mine to see where i went wrong... Im very new to this..


----------



## karlhaywood (Jan 17, 2010)

Just received peter carpenter email. like i said i can do it manually by just putting this in the query <=Date() and abit of code of course, i also think the database and outlook would need to be open permanently for it to be done automatically. I will sleep on it and get back to you tomorrow, if not OBP should pick this up and he will definately be able to do it for you (he's great)


----------



## peter1969 (Dec 11, 2010)

Thank you so much...... Hope i didnt ruin your Saturday evening to much. Look forward to reading some more tomorrow....


----------



## karlhaywood (Jan 17, 2010)

No you didnt ruin it im glad to be of assistance i have received alot of help on here im glad i have passed some of it on! I will post tomorrow. Have a good night. p.s are you in the UK?


----------



## peter1969 (Dec 11, 2010)

No im in Norway...


----------



## karlhaywood (Jan 17, 2010)

Oh ok i will have a think and get back to you tomorrow. Ha en god natt


----------



## karlhaywood (Jan 17, 2010)

Take a look at this

http://www.tek-tips.com/viewthread.cfm?qid=1165838&page=3

This sends a 6 month notification by email. Perhaps OBP will be able to alter the code to work in your application.


----------



## OBP (Mar 8, 2005)

Karl & Peter, I would do as you suggested and put the date Filter in the Query that supplies the form with it's data.
Then the only records on the form will be those that require an email.
It will just be a case of adding a loop to got through those records.
Peter needs to make the decision, does he send them one at a time, which requires ClickYes if you have lots of them to overcome the Outlook Security question, or send one email with multiple addressees?
In fact now that Karl has shown you how it works I probably would not have any fields or records on the form, but use a Recordset based on the query and have an Autoexec macro open the form (hidden if required) and send any required emails.

Well done Karl.


----------



## karlhaywood (Jan 17, 2010)

Thanks OBP


----------



## peter1969 (Dec 11, 2010)

Ok this is where im getting confused. Ive not dealt with recordsets or macro's before. Im very very new to all this.
What i want to do is send one email with mulitple addresses, 5-10 max i would think. Just to inform the formann that certain staff need there certificates updated. Hopefully i could get the staff names and work numbers displayed on the email for ease of use for the foreman. I would like this sent maybe 5 days before expiry and then 2 days just incase they have not got there fingers out..


----------



## OBP (Mar 8, 2005)

Peter, that is quite do-able, is there more than one Foreman?
ie. more than one email could be generated per day?

I did not see a "Foreman" field listed in post 1.

You do not need to worry about the macros/VBA, Karl & I will sort that out for you.


----------



## karlhaywood (Jan 17, 2010)

Peter, i am new to all this aswell, it is great learning for me 2


----------



## peter1969 (Dec 11, 2010)

Sorry this is due to my poor English...
Staff now need to have there certificate renewed every 6 months. What i want is to be able to send to the foreman, 10 forman max an automated email saying the staff on this email need there certificates renewd. One email is fine with multiple adresses. The email addresses are all outlook.
So it would be an automated email every 6 months.


----------



## OBP (Mar 8, 2005)

Do they all expire at the same Time?


----------



## peter1969 (Dec 11, 2010)

No they wouldnt.


----------



## OBP (Mar 8, 2005)

You need a Foreman table with the Foreman's name and email address in it, plus an Autonumber key field.
This would be related to the Workers table where you should ahve a ForemanID field type number linked to the Foreman table key field.
Can you post a screen print of your table relationships please?


----------



## peter1969 (Dec 11, 2010)

Ok i will go and do this..


----------



## OBP (Mar 8, 2005)

Karl, have you created a similar version to Peter's in 2007, if so could you convert it to Access 2003 so that if necessary I can work with it?


----------



## karlhaywood (Jan 17, 2010)

Yes i have a copy


----------



## peter1969 (Dec 11, 2010)

Ive made 2 tables but am cofused how to link them. Sorry my head is not working today


----------



## OBP (Mar 8, 2005)

OK, relationships are hard.
For each Foreman (who has a Key field) there will be some workers (who should have a ForemanID field type Number). 
Click on the Foreman Key field and drag it to the worker's ForemanID field, 
Click "Enforce referential integrity".
That should create a 1 to many (infinity symbol) from the Foreman to the Worker table.


----------



## peter1969 (Dec 11, 2010)

Ok i have uploaded the tables...
Each formann is in charge of a certain avdeling, department. Sometimes there are more than one foreman per department. is this a problem?


----------



## OBP (Mar 8, 2005)

If 2 foreman can be in charge of the same worker, then that is a problem, because you can't set it up with 2 tables.
You will need a third table that "Links the 2 tables together. It is called a Many to Many table.


----------



## peter1969 (Dec 11, 2010)

Ok now im really confused. What i will do is cut it down to the main Formann and take any asistant forman away.


----------



## OBP (Mar 8, 2005)

Peter, I am not sure that is a good idea. We can work with a Many to many table, I can talk you through setting it up.


----------



## peter1969 (Dec 11, 2010)

Ok i have cut it down


----------



## peter1969 (Dec 11, 2010)

Ok we can try but im becoming more and more confused......... Thank you for being so paitent


----------



## OBP (Mar 8, 2005)

Create a new table, call it foremanworkers, it just needs 2 fields in it, WorkerID type Number and ForemanID type number.
Delete the Relationship from the FormenID to the Worker table. (right click on the join line and click delete)
Delete the FormenID from the Worker table.
Now in the relationships add the new table and create the join from the ForemanID in the foreman table to the foremanId in the new table and establish referential integrity.
Now create the join from the WorkerId in the worker table to the workerID in the new table and establish referential integrity.

You should now have a new table with 1 to infinity on both sides, that will allow you to put in there as many Foreman/Worker combinations as you like.
If you want you can add another field to the new table called Comments, type text where you could write "main Foreman" and "Assistant Foreman" if you want, it would just be for clarity and does not affect the way that the emailing will work.
I would suggest that the email be sent to the main foreman with a "cc" to the assistant if there is one.


----------



## karlhaywood (Jan 17, 2010)

I have my version ready


----------



## OBP (Mar 8, 2005)

Karl, I will need you to change peter's version to Access 2003 for me, unless you have reproduced what he has done?


----------



## karlhaywood (Jan 17, 2010)

I dont have 2007 i have just done what you have said in post #65


----------



## peter1969 (Dec 11, 2010)

Sorry have to take the wife to casulalty, will look at it later. thanks for the help so far


----------



## OBP (Mar 8, 2005)

Peter, I hope it is not too serious.

Karl, can you post your version please?


----------



## karlhaywood (Jan 17, 2010)

Hope shes ok. There you go OBP.


----------



## peter1969 (Dec 11, 2010)

Yup she is ok... Needed an injection after the cat had bitten her while trying to give her medicine. This is after the cat had eaten rat poion last week..... She is turning out to be a expensive cat.... 
So will have some food and have a look this evening... Thanks again for all your help


----------



## OBP (Mar 8, 2005)

I am glad she is alright.

OK, I am taking this slowly so lets forget the emailing for the moment, I have modified Karls database. I have added the following items
A Foreman's Query
A Formanworker query
A Foreman MainForm called forman and workers
A subform that is on the above form called formanworkers, this is to show you how to put the data in the many to many table.The foreman's ID is taken from the main form and automatically put in any records on the subform. The workers ID is put in when you select a worker in the Worker Number Combo box.

I have also added some output queries that brings the data from the 3 tables together.
The first just shows all the records and is called "forman and workers Output".
The second is called "forman and workers 2 day Output" and shows the records for only those workers whose renewal date is within 2 days of the current date.
The last one is the same as forman and workers 2 day Output but is called "forman and workers 5 day Output" and shows workers within 3 to 5 days of the current date.

These will be used to send the emails.

Have a look and see if it makes sense to you.


----------



## karlhaywood (Jan 17, 2010)

Great work OBP thats excellent!!


----------



## OBP (Mar 8, 2005)

This version has 2 more queries that group the Forman that allow us to count how many there each day.
I have added a form called autostart that is started by an Autoexec macro.
When it runs it uses recordsets to open the 2 new queries and tells you in message boxes how many forman will get emails.
I will continue with it tomorrow.


----------



## peter1969 (Dec 11, 2010)

Yes im starting to get my head around it i think...You two make it seem so easy...


----------



## peter1969 (Dec 11, 2010)

It seems perfect. I cant thank you enough... Im going to sit tonight and try and work out how you have done this.


----------



## karlhaywood (Jan 17, 2010)

Do you like it? I think it's brilliant!


----------



## peter1969 (Dec 11, 2010)

Superb, You are geniouses


----------



## karlhaywood (Jan 17, 2010)

I think OBP is going to pick this up tomorrow. Have you got your head round how it works yet?


----------



## peter1969 (Dec 11, 2010)

Im hoping to sit later and look at it later.. it does look complicated, but i will get there..


----------



## OBP (Mar 8, 2005)

What is the name of the Certificate? 
Is there more than 1 type?

What do you want to do about Certificates that have not been renewed by the renewal date?


----------



## peter1969 (Dec 11, 2010)

Certificate is called Varm Arbeid. Roughly translated means hot works. For example welding, grinding.
At the moment its just one type. 
Good question. They have to be renewed otherwise they are not able to work. So it needs to stay open until renewed.


----------



## peter1969 (Dec 11, 2010)

I have also been speaking to the foreman today. They have said it is only important to get it sent to the main foreman and not the assistant foreman. This is because the assistant foreman is hands on working and not office based.


----------



## OBP (Mar 8, 2005)

But what if the main foreman is on holiday, off sick etc?
Perhaps a CC copy to the assistant?


----------



## peter1969 (Dec 11, 2010)

Yes that would be perfect.


----------



## OBP (Mar 8, 2005)

What language does the email subject and body need to be in?
I may need a table where you can write them.
I had something like this in mind for the subject

Subject = "There are " & rs1.RecordCount & " Employees requiring Certificate renewal in the next 2 days."


----------



## peter1969 (Dec 11, 2010)

This is where i have translation problems. I dont know all the technical words.
The email would just be in the standard email language. Does this make sense to you?
What is the information needed in the table?


----------



## OBP (Mar 8, 2005)

Well as wrote in the last thread basically what do you want to tell them
the subject line example I gave just says
"There are x number of Employees requiring Certificate renewal in the next 2 days."

Now that the Assistant foreman are going to get a cc copy we can simplify the database by going back to your original design, with just the addition of an Assistant Foreman table and assistant foreman field in the workers table, if that is OK.


----------



## peter1969 (Dec 11, 2010)

Det er X antall ansatte som krever sertifikate fornyelse i de neste to dagene. This is the subject line in Norsk. 
Yes that is all perfect to go back to the original design.
I cant thank you enough for all your help and patience.


----------



## OBP (Mar 8, 2005)

Peter, try this version. It will send me an email. When it has done so, close the form and open the Forman table and change the email addresses to your own or a collegue to test it.


----------



## peter1969 (Dec 11, 2010)

It looks and works superb. I will log in all my work information and test away )))
Thank you both so much for all your help...
I hope in the future as i get better i will be able to help others like i have been helped.


----------



## OBP (Mar 8, 2005)

Peter we haven't finished yet.


----------



## peter1969 (Dec 11, 2010)

One thing. Im getting a run-time error 3070 while using the search on the search form..
It say's
The microsoft office access database engine does not recognize ID as a valid field name or expression.


----------



## OBP (Mar 8, 2005)

It works with this version. But the email has reverted back to mine.
It still needs the emergency email for renewal dates that have past.


----------



## karlhaywood (Jan 17, 2010)

Hi, Hope all is ok? Great work OBP i like the way you have done this!


----------



## OBP (Mar 8, 2005)

Karl, can you change the email addresses to your email address please?


----------



## karlhaywood (Jan 17, 2010)

All done, sorry for the emails!


----------



## OBP (Mar 8, 2005)

that's Ok, it shows it is working.


----------



## OBP (Mar 8, 2005)

Peter, new version.


----------



## peter1969 (Dec 11, 2010)

Its looking and working very well )
Thank you so much i'm learning so much from you....


----------



## OBP (Mar 8, 2005)

Do you need it to do anything else that Karl or I can help with?


----------



## peter1969 (Dec 11, 2010)

No its working great ))
One question. If i need to change the emergency emails do i need to go through VB?


----------



## OBP (Mar 8, 2005)

Yes you do, that was what I meant about setting up a table that held the text for the emails, because then anyone could change them without Access design knowledge.

We also haven't addressed the assistant Foreman issue either.


----------



## peter1969 (Dec 11, 2010)

Ok. So what information do you need from me?


----------



## OBP (Mar 8, 2005)

Only what you want to do?


----------



## peter1969 (Dec 11, 2010)

Ok i would like to set up tables so anyone can change the e-mail body and heading, e-mail adresses. Also be able to cc the assitant formenn.
Does this make sense to you?


----------



## OBP (Mar 8, 2005)

OK, it will take a while to set up.


----------



## peter1969 (Dec 11, 2010)

Please dont put this infront of your own work... I feels so bad taking so much of your time.


----------



## OBP (Mar 8, 2005)

Latest version. Please note do not change any Field Names, unless you change them in the VBA as well.
It has assistant formenn and new queries and forms.


----------



## peter1969 (Dec 11, 2010)

Hello J

Hope you had a good Christmas.
I just have a few questions I hope you could help me with. 
Is it possible for me to add a login screen? I was just worried that this would cause the automated email problems.? 
Could I use a master login then a read only one for the rest? I only want one maybe two who can add, edit or delete records.

Wishing you Happy New Year


----------



## OBP (Mar 8, 2005)

Yes Christmas was nice & quiet as we like it.
What version of Access is the current database in?


----------



## peter1969 (Dec 11, 2010)

Hi it's in 2007. But i have read that i can change the user level security in 2007 if it was made in 2003 or earlier. 
Just wondering if you had any login page code that i could look at and play about with...


----------



## OBP (Mar 8, 2005)

Peter, Karl has just posted some here - http://forums.techguy.org/business-applications/971543-solved-access-2003-vba.html

Although normally it would be in a start up form.

Access 2003 has full User Level security and as you say you can change it ib Access 2007. But databases saved as accb instead of .mdb do not support it as far as I know.


----------



## peter1969 (Dec 11, 2010)

Oh ok i understand i will scan the net and see how to do it in 2007.....


----------



## OBP (Mar 8, 2005)

I cna provide you with a 2007 login form with VBA that I helped develop for a guy in the USA.


----------



## peter1969 (Dec 11, 2010)

That would be brilliant... Thank you. My head is not working again at all today ( 
All i need is a master and a read only.. But im so dumb in the head that i cant even work out how to do that today...


----------



## OBP (Mar 8, 2005)

Split the database, copy the front end, make one Read Only and the other Editable.

I will send you a copy of the database with the Passwword form in it, if you need it.


----------



## peter1969 (Dec 11, 2010)

It amazes me how you make everything sound so easy....


----------



## peter1969 (Dec 11, 2010)

Oh yes and would love a copy of the database


----------



## OBP (Mar 8, 2005)

Peter, there is another alternative to using passwords, that is using the Windows or network ID, just a thought.


----------



## peter1969 (Dec 11, 2010)

I'm not sure if i understand that. I will try anything though. The easy the better..


----------



## OBP (Mar 8, 2005)

Well if you want to restrict users you can use their network ID to identify who can do what.
It would mean setting the Form's Allow Edits, Allow Additions and Allow Deletions properties based on who the user is.


----------



## peter1969 (Dec 11, 2010)

Ok that sounds like a good idea  But does this mean i need to find out all the network id's of the people who will be using this? Because at the moment its a max of three or four..


----------



## OBP (Mar 8, 2005)

You only need the ones allowed to enter or edit the data.


----------



## peter1969 (Dec 11, 2010)

ok thats not a problem.
But im not sure how this all gets set up...


----------



## peter1969 (Dec 11, 2010)

What i meant to say is i know how to Do all the allows on a form, not sure how you join it to the network id


----------



## OBP (Mar 8, 2005)

OK, in the form's On Load Event Procedure you would use something like
if environ("username") = "yourID" or environ("username") = "another userID allowed to enter data) then
me.allowedits = true
me.allowadditons = true
else 
me.allowedits = false
me.allowadditons = False
end if

where yourID is the your user ID and "another userID allowed to enter data" is the user ID of another user allowed to ente data etc.
You can get the Form to provide those for you by using
msgbox environ("username")
You could even put them in a table for later use if necessary.


----------



## peter1969 (Dec 11, 2010)

Ok so i when i go into the event procedure it opens the vba window. Do i then add the network id where you put ("username")? Do i enclose the network id's in the brackets?


----------



## OBP (Mar 8, 2005)

No you enter it exactly as it is, that should give you your Id when you open the form.


----------



## peter1969 (Dec 11, 2010)

Ok i will have to wait until Monday so i can get on the network. 
But if i want to add another who is allowed to edit etc where do i add there name?


----------



## OBP (Mar 8, 2005)

In the VBA code, or you could have a table of IDs with who is allowed and who isn't, but that takes more coding.


----------



## peter1969 (Dec 11, 2010)

The table sounds much better because of new staff, people leaving and as the database grows more will need to view it


----------



## OBP (Mar 8, 2005)

It is also better because once set up any additions or deletions do not need "Programming" skills.


----------



## peter1969 (Dec 11, 2010)

But this is all done through vba yes?


----------



## OBP (Mar 8, 2005)

Well the User Table, Query & Form need setting up now so that the users can be put in.
The control of the Form is by VBA code.


----------



## peter1969 (Dec 11, 2010)

I have made a table, query and a form based roughly on what I think it should be and changed it to access 2000. 
Tillet = Allowed
Ikke Tillet = Not allowed
And called it ULS 
Is there any other information that needs to be on there?


----------



## OBP (Mar 8, 2005)

No that will do for now, I will take a look and post tomorrow.


----------



## peter1969 (Dec 11, 2010)

Tusen takk


----------



## karlhaywood (Jan 17, 2010)

Hi you 2, Great work OBP the database looks briliant!


----------



## OBP (Mar 8, 2005)

Peter, I have used your ULS Form as an Example, when it is "Loaded" it checks who is Opening the form using the ULSQuery. The form has it's AllowAdditons, AllowDeletions & AllowEdits set to No. The VBA code check if the user's Tillet is ticked, if it is it resets the Form's properties.
If you have subforms they may need to set also.

You will need to change your Network ID to yours to get it to work.


----------



## peter1969 (Dec 11, 2010)

You are to fast  
So from what i understand is that i put my windows login in the network ID? and that is it. Also add the other ID's in the table for either read only or master?i have opened it up and it came up with an error 3085. Undefined function `Environ`in expression. Is this because i need to add the correct network ID?

Im trying to understand how the VBA works. Does this run in the background of the database and read who is accessing the database?

Hello Karl wishing you and family a happy new year


----------



## karlhaywood (Jan 17, 2010)

Hi Peter hope your ok? Wishinh you an Happy New Year.

Here is the solution to your error problem.

This error is caused by the MACRO Security Settings within Access. To resolve the issue on the machine where it occurs...:
1) Open MS Access and from teh menu bar, select Tools --> Macro --> Security. 
2) Check the "Low" checkbox on the Security Levels tab. 
3) Close MS Access.
4) Open the database that had the error. It should now be removed.

Let me know how you get on.


----------



## peter1969 (Dec 11, 2010)

To be honest ive had the flu and cant seem to get rid of it...
Its different on 2007, i have gone into the macro settings and enabled all macro's but still getting the same error..


----------



## karlhaywood (Jan 17, 2010)

You need to disable the macros.

1) Open "Customize Quick Access Toolbar" menu to the right of the office button
2) Select "More Commands"
3) Click "Trust Center" menu button on sidebar
4) Click "Trust Center Settings" button on main pane
5) Click "Macro Settings" menu button on sidebar
6) Select "Disable all macros"
7) Click "OK" to close Trust Center
8) Click "OK" to close Access Options


----------



## OBP (Mar 8, 2005)

Peter & Karl, here is wishing you a Healthy, Happy & Prosperous New Year.
ps Karl, did you enjoy your time in Swansea?


----------



## karlhaywood (Jan 17, 2010)

Hi OBP, Happy and Healthy New Year to you too. Swansea was brilliant, we went to the Aspers Casino, Liberty Stadium also the National Waterfront Museum. The hotel was amazing i definitely recommend, we are actually thinking of going back in the summer! Hope you and your wife had a good christmas.


----------



## peter1969 (Dec 11, 2010)

And a happy new year to the both of you )


----------



## peter1969 (Dec 11, 2010)

Its strange because i still cant get rid of the error message... Will look again Sunday... I hope you didnt drink as much as i did (


----------



## OBP (Mar 8, 2005)

Peter, check that the Library Refereences are all correctly ticked and not missing.
Alt + f11
Main Menu>Tools>References

I am tee total so I never drink.


----------



## peter1969 (Dec 11, 2010)

Only the first 4 are checked and there are loads that are not....Not sure which to allow or not...I did check all of them and try and it still came with the same error..


----------



## OBP (Mar 8, 2005)

Peter, you shouldn't need to click them all. As that didn't work anyway you should only need the original 4.
I am not on the network and it still gives me an Environ("username"). So I am not sure if it is an Access 2007 problem.


----------



## karlhaywood (Jan 17, 2010)

Hi OBP, how do you actually find your Network ID?


----------



## OBP (Mar 8, 2005)

Add this to any forms "On Load" or On Current event
msgbox environ("username")


----------



## karlhaywood (Jan 17, 2010)

Thanks OBP, i like how you have done that. What is it for?


----------



## OBP (Mar 8, 2005)

Exactly what Peter wants to do, identify the user and make a decision on what they can do.


----------



## karlhaywood (Jan 17, 2010)

Briliant i actually want that 2, i will try and alter the code and put it in my database. Thanks OBP.


----------



## OBP (Mar 8, 2005)

Did you look at the ULD database?


----------



## karlhaywood (Jan 17, 2010)

yes i did. i have created the table, but what are the check box field names?


----------



## OBP (Mar 8, 2005)

Tillet is "Allowed", youi do not actually need the other one.


----------



## karlhaywood (Jan 17, 2010)

OBP i have altered the code and i can get the recordset to work ok but, everytime i open the form i get "Karl True"

If i take the line of code below out, would that get rid off the msgbox? or would it ruin the code?

MsgBox rs.FirstName & " " & rs.Allowed


----------



## OBP (Mar 8, 2005)

That is only there to tell you that it has done something.
Don't forget to set the Form's Properties to "No".


----------



## karlhaywood (Jan 17, 2010)

I dont need to because im using the code to check whether the user has admin rights or not, if yes the 3 extra text boxes are shown if no there not

Here is the altered code:

Private Sub Form_Load()
Dim rs As Object
On Error GoTo Eventerror
Set rs = CurrentDb.OpenRecordset("QryNetwork")
If rs.RecordCount = 0 Then Exit Sub
MsgBox rs.FirstName & " " & rs.Allowed
If rs.Allowed = -1 Then
[Funded By].Visible = True
[Daily Rate].Visible = True
[Weekly Rate].Visible = True
End If
rs.Close
Set rs = Nothing
Exit Sub
Eventerror:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub


----------



## peter1969 (Dec 11, 2010)

Good evening OBP and Karl hope you are well.. 
I have given up on this network login. I thought i had it but then saw there was no code in VBA )
Has anyone got any idea's on a good user level security?


----------



## OBP (Mar 8, 2005)

Peter. what does "then saw there was no code in VBA" mean?
What do you want to try instead, Access 2003 security or some login form, which is not very secure but may be OK?


----------



## peter1969 (Dec 11, 2010)

Sorry my English again. No the error msg was gone so i thought it was working. But it wasnt it was the code that was missing.
I just want something nice and easy use. Becuase this is really showing how bad i am at all this.


----------



## OBP (Mar 8, 2005)

OK, I will provide you with a Login Form.


----------



## peter1969 (Dec 11, 2010)

Thank you again.
It must be conflicted in 2007 thats all i can think...


----------



## OBP (Mar 8, 2005)

Peter, you will need a Password Field instead of a Network ID. I will change it on your ULS database.
What is Password in Norwegian?


----------



## peter1969 (Dec 11, 2010)

Passord i Norsk 
I cant thank you enough again. I must be getting on your nerves


----------



## OBP (Mar 8, 2005)

Is that "Passord i Norsk"?
What does that Translate to in English?

No you are not getting on my nerves, just look at my motto.


----------



## peter1969 (Dec 11, 2010)

Sorry Passord is Norwegian for password..
Thanks for being Patient


----------



## peter1969 (Dec 11, 2010)

Going to take this up in the Morning, so please dont rush  and enjoy your evening )


----------



## karlhaywood (Jan 17, 2010)

Peter dont give up easily. OBP and i will always be here to help you. Good luck with the database. :up: p.s OBP i have the code working brilliantly restricting controls and form access. Thank you.


----------



## peter1969 (Dec 11, 2010)

Thank you for those words... I just wish the company was using the same version as you two


----------



## OBP (Mar 8, 2005)

Try this version, your current password is tonyosborn.


----------



## peter1969 (Dec 11, 2010)

Yup that looks great 
Can i can add infront of my database?


----------



## peter1969 (Dec 11, 2010)

Hi OBP i have added the code to some other forms and its working great )


----------



## OBP (Mar 8, 2005)

Peter, there is one other thing you need to do for security and that is to hide the tables, queries & forms etc.
I know how to do it in Access 2000-2003, but not in 2007.
It should be 
Office button (upper left)> Access Options button> Current Database
section> Navigation subsection> Display Navigation Pane. Untick it.


----------



## peter1969 (Dec 11, 2010)

Hi OBP yes it looks like it's roughly the same way  Then im going to look at the back-up system. 
Just one more question if i were to change the 5 day email warning to 10 day, I need to go into the respective query and change the date?


----------



## OBP (Mar 8, 2005)

You need to change the the Criteria where it says "Between Date()+3 And Date()+5" to
Between Date()+3 And Date()+10


----------



## OBP (Mar 8, 2005)

I have an automated backup system provided by another helper.
See this thread 
http://forums.techguy.org/business-applications/936539-access-2003-macro-backup-database.html


----------



## peter1969 (Dec 11, 2010)

Ok OBP i will try that Thursday. I'm going to have the night off 
Thank you again for all the help. Im getting really hooked


----------



## OBP (Mar 8, 2005)

Carefull, you might end up like me, still working with it 20 years later.


----------



## peter1969 (Dec 11, 2010)

Lol never say never....
I sent you an email ref the backup database. When you have a minute can you check to see if i have changed the code correctly.


----------



## peter1969 (Dec 11, 2010)

Hi Tony Could you please take a look at the database i sent you via email. Its all working fine apart from the password. Type in password or press exit database and nothing happens....


----------



## OBP (Mar 8, 2005)

Peter, it exits the database OK.


----------



## peter1969 (Dec 11, 2010)

Hi OBP what i have just noticed is that the password form has the small cross in the right hand top corner to close the form. This is after i move the file.


----------



## OBP (Mar 8, 2005)

Peter, not in the version you sent me running Access 2003.
Does the cross work on your version?


----------



## peter1969 (Dec 11, 2010)

Hi OBP
Going back a little to the auto email. It's sending the mails to the foreman but not CC to the Assitants. Any ideas?


----------



## peter1969 (Dec 11, 2010)

Ok i have just seen that it cc's 5 day warning emails and emergency emails, but it wont CC the 2 day warning


----------



## peter1969 (Dec 11, 2010)

Sorry one more question. When all the proper dates are entered onto the system and there are no dates within the 5 day or 2 day warning i get a window pop up saying:
Object variable or with block variable not set.
I press ok ans the system works fine...Will this cause a problem later on?


----------



## OBP (Mar 8, 2005)

Peter, looking at the code I am not sure that it is picking up the assistant foreman for the cc address, i think it is repeating the Foreman's email address.
Can you email me an Access 2003 format database with your real table and your Autostart form plus the 2 & 5 day forman only queries in it and I will take a look at it with some real data.

How did the demonstartion go?


----------



## peter1969 (Dec 11, 2010)

Hi OBP Hope you are well. The software has gone very well. Now three sub divisions are using it. 
One would love the automated email System. When you have time could you please look at the two day cc email. 
On it they said can it be 14 day, 5 day and the emergency email? If you have some spare time could you please look at this I have enclosed the original database in 2000.


----------



## OBP (Mar 8, 2005)

Peter, try this version I have changed 
the email table & email table query
Both 2 day queries, which are now 14 day queries 
the Autostart 
Use a different email for the Assisitant Foreman.

Do you want this to send the 14 day email ever day between 14 and 5 days, because that is what it will do at the moment.


----------



## peter1969 (Dec 11, 2010)

Morning
Ok just tried that.. !4 day working ok, no 5day emails. pop up window saying unknown message recipients message was not sent... 
Also no i wouldnt want it to send it out everyday, just once...between 14 and 5 days...


----------



## OBP (Mar 8, 2005)

OK, this one fixes the ccaddress, which I forgot to reset after the first email.
I will need to add another field to the Workers table to register that the 14 day email has been sent and then reset it when the new Certificate date is entered.
I think that you will have to transfer it to your database.


----------



## peter1969 (Dec 11, 2010)

Ok i will look at this today or tuesday..
It will run inside the personal database. This will will be kept up to date by one person. So hopefully the rest will take note.


----------



## peter1969 (Dec 11, 2010)

Hi.. OBP hoping you could give some advice... 
Have a new database with 3 pages, general info, next of kin and certificates. On certificates i have a combo with all the certificates that can be selected and an expiry date. Can i use that date within my database to use your auto emailing system? What I need it to do is send out one email to the employee and his foreman 4 weeks before renewal, then the other email 1 week before. Is this possible?
All of the employees with certificates will have email addresses. This is only a very small division with maybe 20 staff so there will be no need for emergency emails.


----------



## OBP (Mar 8, 2005)

Yes you can use that date. I would add an "email sent date" to that table as well.


----------



## peter1969 (Dec 11, 2010)

would that be on the certificate table? and would it need to have the date put in manually?


----------



## OBP (Mar 8, 2005)

It would be with the other date and it would be set automatically when the email is sent, to prevent it being sent again. You may even need 2 date fields, one for each email.


----------



## peter1969 (Dec 11, 2010)

Hi OBP
Ok i have made a database to the standard they wanted. I think 
but now its beyond me. Not sure if i said but i need 1 email to sent out 4 weeks before, then a reminder 1 week before certificate expiry. This goes to the foreman and the employee. Maybe the assistant. I have been going through the auto email you made and am getting confused.. I have combined the two but am having trouble with relationships and all the querys.. 
Was hoping you could take a quick look for me becuase im not sure what info i need to keep and what to get rid of....


----------



## OBP (Mar 8, 2005)

Sure, I will take a look.


----------



## peter1969 (Dec 11, 2010)

Thank you OBP
I have done as much as i can at the moment. I finding it hard to get some peace today. My Son has lots of friends around


----------



## OBP (Mar 8, 2005)

OK, what is the Contacts and Kin tables for?


----------



## peter1969 (Dec 11, 2010)

Contacts will be for all the employees and the kin table is for all the next of kin for the employees. Is that best to put all in one table?


----------



## peter1969 (Dec 11, 2010)

Another quick question is using the attachment function better than using hyper links?


----------



## OBP (Mar 8, 2005)

It is best to have seperate tables.
I can't help with the attachments vs Hyperlink as attachments are an Access 2007 feature. 
I do not use hyperlinks either, I use a text field and VBA Follow Hyperlink.

From a previous post you said you wanted to use the Ans nr to link the tables, the Workers table does not have an Ans nr field to link to. But the Kin & Contacts tables do not have the ContactID field to link to either.
So you need one or the other.


----------



## OBP (Mar 8, 2005)

This has the emails working, it also has a pair of queries that reset the emials sent fields after the renewal date has passed.
But I haven't done anything with the other tables.


----------



## peter1969 (Dec 11, 2010)

Thank you OBP i will look at that when I get home


----------



## peter1969 (Dec 11, 2010)

Not sure if the last post was sent.. But TY and i will look at this when i get home


----------



## peter1969 (Dec 11, 2010)

Hi OBP Sorry but i can,t seem to get the assistant foreman cc email to work... Also how would i incorperate the employee email address into this?


----------



## OBP (Mar 8, 2005)

Where should the Employee email address go?

Is the AF cc working in my version, it was hard to tell because I used your email address.


----------



## peter1969 (Dec 11, 2010)

Im not sure how im going to structure it yet.. What i will do is make a little database tonight with everything i need. Then i will ask what is the best way to link it all together.. Is that ok?


----------



## OBP (Mar 8, 2005)

Sure.


----------



## peter1969 (Dec 11, 2010)

Hi OBP This is the kind of database they want. Is it possible to take the employee email from the general page of the database? 
I think i have linked everything up ok using the ans nr.


----------



## OBP (Mar 8, 2005)

Peter, what warning periods is that database going to use?
As I notice it is not based on the last version that I posted.


----------



## peter1969 (Dec 11, 2010)

Sorry i didnt realise it wasnt the same.. It will be the same 28 day and 7 day.


----------



## OBP (Mar 8, 2005)

It does not have some of the data it requires, I would be better using my version I think.


----------



## peter1969 (Dec 11, 2010)

Ok i will look at that tonight. Not sure what ive done with your version


----------



## OBP (Mar 8, 2005)

Peter, don't worry, I will use mine and add anything necessary from yours.


----------



## peter1969 (Dec 11, 2010)

Sorry ive not replied earlier. My Laptop has gone into some kind of sleep mode.. So im using a friends.. Ty 4 all the help, im just hoping we can get it up and running at work monday.


----------



## OBP (Mar 8, 2005)

That might be a problem as I am working on 2 other databases at the moment.


----------



## peter1969 (Dec 11, 2010)

Sorry I meant i hope i get my laptop working by Monday... This is no rush job.


----------



## peter1969 (Dec 11, 2010)

Hi good news we have the laptop up and running.... So if there is any info you need let me know OBP..


----------



## OBP (Mar 8, 2005)

I should get a chance to look at it today.


----------



## OBP (Mar 8, 2005)

Peter, this version has the Employee email fields added to the table, queries and VBA.
You will need to put in some email addresses for the employees.


----------



## peter1969 (Dec 11, 2010)

Thank you OBP im out of the office until next week. Im looking forward to seeing it. I have tried to build one so it will be good to see where i went wrong... Have a good weekend


----------



## peter1969 (Dec 11, 2010)

Hi just to confirm should i be adding the emails to the workers table? or should i be working through the form?


----------



## peter1969 (Dec 11, 2010)

unknown message recipient(s) the message was not sent error comes up..


----------



## OBP (Mar 8, 2005)

If you look at the email what do the email addresses look like?


----------



## peter1969 (Dec 11, 2010)

it will not send any emails...


----------



## OBP (Mar 8, 2005)

They should be in the outbox if they are not sent.


----------



## peter1969 (Dec 11, 2010)

Should it be in the outbox of outlook?


----------



## OBP (Mar 8, 2005)

If it is Outlook that you are using.


----------



## peter1969 (Dec 11, 2010)

Because outlook doesnt work properly unless im contected to the network at work for some reason.


----------



## peter1969 (Dec 11, 2010)

This might sound dumb to you but how do i find out which email its using?


----------



## OBP (Mar 8, 2005)

Don't worry I will try and send you an email using it.


----------



## peter1969 (Dec 11, 2010)

Ok ty I will let you know the outcome


----------



## peter1969 (Dec 11, 2010)

Ok i recieved the 7 day email to outlook and the cc address...


----------



## OBP (Mar 8, 2005)

OK, try this version, it comes up with a "no current record" for one of the emails, but the other one it sends.


----------



## peter1969 (Dec 11, 2010)

Ok Tony it has now shown up in my outlook outbox... For some reason i can only send via outlook while connected through the network at work..


----------



## OBP (Mar 8, 2005)

I have made a change that has removed the error message.


----------



## peter1969 (Dec 11, 2010)

Ok thank you tony.. Can i ask what is the best way of linking this to my database? or should i leave it as a stand alone database which works within mine?


----------



## OBP (Mar 8, 2005)

You can import the whole thing in to your database as long as the form names are different.
You can just leave it as it is.
It depends on how much work you want to do.


----------



## peter1969 (Dec 11, 2010)

Hi OBP Ok now im at work the 7 day email works good  you said you had made a change for the error msg?
But the 28 day email will not send. I have looked through the code and cant see any reason why it shouldnt..


----------



## OBP (Mar 8, 2005)

Ok, I have found it. This line
If Not IsNull(rs1![EmailAddress]) Then ccaddress = EmailAddress & rs1![EmailAddress] & ";"
had the 1 missing form the rs1![EmailAddress]


----------



## peter1969 (Dec 11, 2010)

Hi OBP

It seems like the 7 day and 28 day to foreman works ok
The cc isnt to the assistant foreman
employee 7 day email works but 28 day doesnt.

it is coming up with errors while trying to send..


----------



## OBP (Mar 8, 2005)

Are you sure that they are errors, as I left messages in the code to show where abouts it gets to.
I am sure I have fixed this once, so I am doing the same work again, that is probably due to not keeping only the latest version. 

I have just sent you, me & the wife an email using this version.


----------



## peter1969 (Dec 11, 2010)

Hi sorry OBP im all over the place at the moment.......


----------



## OBP (Mar 8, 2005)

Remove the 2 lines that say

MsgBox EmailAddress & " " & ccaddress

to stop the messages.


----------



## peter1969 (Dec 11, 2010)

Hi OBP doing some testing after joining it to my database.. All good so far 
Is it possible for me to delete the 2 warnings,, the first one says "You are about 
to run an append query that will modify data in your table... 
are you sure you want to run this type of action query?" 
Sorry for all the harressment (is that the correct word) Work has got me doing so many different jobs at the same time


----------



## OBP (Mar 8, 2005)

Yes you can, I am not sure where that code that runs the query is now, but you add
DoCmd.SetWarnings False
before the line that runs the query and
DoCmd.SetWarnings True
after the line


----------



## peter1969 (Dec 11, 2010)

Ok will go look in the VB code...


----------



## peter1969 (Dec 11, 2010)

Ty that worked great


----------



## peter1969 (Dec 11, 2010)

Hi last question for today i promise :/
How do i remove the msg boxes that say the e-mail adresses?


----------



## peter1969 (Dec 11, 2010)

Ok I lied have one more problem... If it needs to send out 7 day emails and 28 day emails it has an error after trying to send the 28 day email... Saying Unknown message recipient(s) the message was not sent.
But if i shut down the database and open it again it sends the 28 day email


----------



## OBP (Mar 8, 2005)

Either delete the line of code or put a ' in front of ther line, I pointed out the lines in my post #252.


----------



## OBP (Mar 8, 2005)

Find these lines of code
secondemail:
EmailAddress = ""
Body = ""
Set rs = CurrentDb.OpenRecordset("forman only 28 day")
between body = "" & Set rs =
Insert a line
ccaddress = ""


----------



## peter1969 (Dec 11, 2010)

Ok will do


----------



## peter1969 (Dec 11, 2010)

Thanks that worked great  Thank you for your patients


----------



## peter1969 (Dec 11, 2010)

Hi
Is there a way of showing the employees names alongside the certificates in the emails?


----------



## OBP (Mar 8, 2005)

Peter, it already does, the one that I sent says
28 day28 daydoe, jane Sertifikat - 33333333 Utgår om 9 dager.
There should really only be one 28 day though.


----------



## peter1969 (Dec 11, 2010)

Yup im having a problem linking it to my database. Everything is fine sending emails and relationships, but it doesnt show the name on the email... Not sure what to do really.


----------



## OBP (Mar 8, 2005)

Check that the field names that you have in your tables are the same as the field names in the one that I posted, also check the queries in the one that I posted and ensure that yours are the same.
What do you actually get?


----------



## peter1969 (Dec 11, 2010)

Lol i dont know what ive done... Now it's linked with my database but it wont send the emails


----------



## peter1969 (Dec 11, 2010)

It's really beyond me... Ive not touched any of the code..... Can you please take a look... I have added the workers form to mine and linked it. So as you can see it picks up name, number and epost. But for some reason no emails now...


----------



## OBP (Mar 8, 2005)

Peter, sorry it is an Access 2007 database, no can do.


----------



## peter1969 (Dec 11, 2010)

sorry forgot that.. this is 2000 now


----------



## OBP (Mar 8, 2005)

Peter, the reason that no emails are being sent is because the 7 day query does naot have any records.
It does not have any records because the Employees you are using do not have Assistant Foremen chosen.


----------



## peter1969 (Dec 11, 2010)

Ok they're being stupid saying they didnt want a Foreman so i took it out.. I will add it when i get back to work tomorrow. Is that all thats the problem?


----------



## peter1969 (Dec 11, 2010)

HI but what if they do not have an Assistant Foreman can it be left blank?


----------



## OBP (Mar 8, 2005)

If they do not have AFs then we need to change the Queries.


----------



## peter1969 (Dec 11, 2010)

Yes they are saying none have AF  Sorry about yesterday im getting a little stressed with everything.. Now i have taken a step back and looked today it all seems good...


----------



## OBP (Mar 8, 2005)

In that case we needf to remove all of the references to the AFs.


----------



## peter1969 (Dec 11, 2010)

Is it possible to leave it on and just leave it blank. I know what will happen when they get bigger they will say can you put the AF back on....


----------



## OBP (Mar 8, 2005)

This should do it.


----------



## peter1969 (Dec 11, 2010)

Thank you its working perfectly


----------



## peter1969 (Dec 11, 2010)

OBP can i ask what would be the best way to link the database with scanned copies of peoples certificates? There ia an attachment option and hyper link option on 2007. The hyper link option comes up with a warning box everytime and the attachments will just make the database bigger in size..
They have told me all the certificates are scanned and in folders in personells computer.. Was hoping you have a good idea on this


----------



## OBP (Mar 8, 2005)

If they are in Folders on Personell's computers only those users would be able to see them, unless they are on a sharing network.
All you need to do is store the "full Path" to the Certificate and then use the "Follow Hyperlink" method to open them, they will open in the Program that normally opens them.


----------



## peter1969 (Dec 11, 2010)

Ok OBP ty for that. I will find out who needs to view and work it out accordingly


----------



## OBP (Mar 8, 2005)

Let me know when you need the VBA code.


----------



## peter1969 (Dec 11, 2010)

Ok is it like a button u press? because ive looked at the hyper link and it its not very professional looking......


----------



## OBP (Mar 8, 2005)

There are 2 ways to do it, a Button or Double click the field, a browser opens, find the document click on it click Ok, it puts it in the field. You then have the opposite, either a button or double click to open the document.
I have just done the same thing for Vanessa on this thread http://forums.techguy.org/business-applications/954425-customizing-primary-key-46.html see post 680.


----------



## peter1969 (Dec 11, 2010)

Ok thank you i will look at that on Saturday... Thank god its weekend


----------



## peter1969 (Dec 11, 2010)

Hello OBP Thank you i have read through the thread regarding the documents link... So far i have made a table, query, and a quick form... 
Just one thing the memo option in comments is this where the links will be?


----------



## OBP (Mar 8, 2005)

No, in the Document Address, the comments is just if you want to make notes about the Certificate.


----------

