macro to compare 2 dates and give a info mess if less than 1 wk

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a newbee, I have two date fields in a subform "Mailing List". One is
Event Date and the Other is Mailing Date. I need to generate an Info only
message that warns the user that there is only 1 week or less left before
the customers event and they may want to send a rejection letter.

I have never written a macro and could use some help. Any help would be
greatly appreciated.
Elaine
 
What you really need for this is some VBA code to compare the dates and
display a message box. First, here is the code that will do this for you:

Dim intDaysLeft As Integer

intDaysLeft = DateDiff("d", Date, Me.EventDate)
If intDaysLeft <= 7 Then
MsgBox "There Are Only " & intDaysLeft " Days Before This Event"
End If

I don't know the name of the text box where the Event Date is, so you will
have to replace EventDate with the name of your text box.

Now, to get it where you need it:
Open your sub form in design view.
Select Properties
Select Form
Select the Events tab
Select the OnCurrent event
Click the small command button to the right of the text box with 3 dots ...
Select Code Builder.

The VBA editor will open looking like this:

Private Sub Form_Current()
|
End Sub

The cursor ( | ) will be flasing on the empty line
Past the code here.
Change EventDate to the name of your control.
Close the VBA editor
Save the sub form

Now put on you safety goggles, ear plugs, leather gloves, and try it.
 
Thank you soo much I am going to give it a try. Do you know are there any
books out there that teach macro writing and or how to write VBA code?
 
I don't know of one I can specifically recommend for a beginner. If you have
access to a book store that has a large technical section, you might try
browsing through some books on Access and see if one makes sense to you. You
might also try browising the Microsoft web site and see what training tools
they have available.

Macros and VBA are very different creatures. Macros make it easy to execute
basic commands witihin Access, but do have some limitations. VBA is the
programming language that gives you complete control of everything.

Good Luck and post back if you have problems using the code I posted.
 
Back
Top