Date calculation and send email in Access 2013

[ceebee]

Hello

I am trying to do the following:

I have a column in my table and a field on my form called Renewal Date. I would like to use that field and send an email out 7 days before the renewal date. (I guess something like [Renewal Date] - 7)?!

I would then like to send an email to the user borrowing the software, plus the group responsible for loaning it out.



Where would this code go? I am not sure where I would put this code.

PS. I am a newbie and have no programming experience!

Thank You for your time.

[June7]

Could have code behind a button on a form. All depends on your business process and how user interacts with the data.

Suggest you start learning how to program. Need to understand basic programming concepts, Access functionality, and macro and/or VBA language. Here's one source http://www.functionx.com/vbaccess/Lesson01.htm

[ceebee]

Thank you for your reply.

But this is not part of my normal job duties. I just took it on to help out.

There is no button...I would like the system to just calculate the date -7 to send out the email.

[June7]

So you want us to do your work for you? I can help analyze code you have issue with but not going to try and write a procedure for you, especially since I have no knowledge of your database structure and business processes. We are volunteers here, not paid consultants. If you do not have the time nor inclination to learn how to do this, then hire someone.

There are many examples of code to send emails. Do some research on SendObject method as well as Outlook automation. Search forum - here is one https://www.accessforums.net/showthr...ght=sendobject.

Code must be behind some event. Most likely a button Click or form Open.

An expression for date calculation to subtract days is simply:

Date() - 7

or

[YourFieldName] - 7

For other time units use DateAdd() function.

[ceebee]

Thanks for your reply again.

But I don't recall saying that I want you to do my work for me. I was looking for some friendly pointers, that's all.

[June7]

Didn't mean to come across harsh but you seemed to need a reality check. The best pointer can offer for someone who has zero programming knowledge is to take a class or sit down and spend a solid week with an introductory tutorial book (or a good online course).

You say 'I am trying' - so what have you done? Do you have a query attempting to filter records? Did any of the little pointers I offered mean anything to you? Do you know what an event is? Do you know how to build expressions in a query? Do you know how to open the VBA editor and write code? Can use macros if you prefer and accomplish a lot but VBA is more versatile.

"I would like the system to just calculate the date -7 to send out the email." is a very broad statement. How would the 'system' know when and how to send email? To whom? Code in some event triggers this activity. A button Click is an event. A form opening is an event. Do you want to send a single email or do you want to send 'a batch' of any number of individual emails?

Post attempted query statement or code for analysis. You can even attach database.

[ceebee]

Sure...but no laughing!

Here is my DB design. I tried to follow 1NF, 2NF, 3NF. Not sure how successful I was. Basically this is a system that is used to track software being loaned out to people.

[ceebee]

To answer your questions...I have created forms and reports based on the db diagram above. I am having a problem with passing FK values, also with some of the lookup tables (which is bizarre because some work, some don't. They have the same structure/data types).


I am familiar with expressions, just from what I have read. VBA editor, yes I know how to access that - but the coding is a problem.

[June7]

"Track software loaned" sounds like a library type of operation. There is a MS template for that. In Access 2010 > File > New > Database > Lending Library

And another http://allenbrowne.com/AppLibrary.html

There is also an Assets template.

Some of the relationships appear to be circular, which might be an issue. Review https://www.codeproject.com/articles...atabase-design

Why are Building and Section in both Software and Borrower? Why is Software_ID in Borrower? Shouldn't BorrowDate and ReturnDate be in Software? Can't borrower borrow more than one software at a time?

If you want to maintain history of what was borrowed by whom and when - need a junction table.

Advise not to use spaces in naming convention.