Date Reminders

[giant56]

Date Reminders???

IS there any way I can get pop-up date reminders in Access 2000. Like when something is due then right when I open up my database a pop up date reminder appears. If so I would really appreciate to know how.
Thank you for your time.

 

[D-Fresh]

Hey, you can always create a table(tblDates) with two columns for date and activity. Then create a module with a function like this....

public function datecheck()

dim MyDB as database
dim MyRecs as recordset
dim MySQL as string

'Make SQL statement to see if any dates in table match current day

MySQL = "SELECT * FROM tblDates WHERE actDate=#" & Date & "#"

set MyDB = currentdb
set MyRecs = MyDB.openrecordset(MySQL)

if not MyRecs.eof then
while not MyRecs.eof
msgbox ("Don't forget to do" & vbNewline & myrecs("Activity") & " today!")

MyRecs.movenext
wend
else
msgbox ("You have nothing to do today.")
end if

'Clean Up
MyDB.close
set MyDB=nothing
set MyRecs=nothing

end function

Then create a macro called "Autoexec" and use runcode and call the function "datecheck" that you just created. When you open up the Database, it'll run Autoexec and let you know what to do for the day. Hope this helps.

Doug

 

[giant56]

Thanks Doug for your reply. I think we're on the right track. I put in the objects like you specified, but for some reason when I open up my database it tells me that "Microsoft Access can't find the name "datecheck" like I entered into the expression. I named the module "datecheck" and I saved it in the module section. I probably messed up the code somewhere, but if you can think of anything I might have done please respond. You are doing me a huge favor and I really appreciate it. Thank you.

 

[D-Fresh]

alright, your problem is that you have to name the function datecheck(). It doesn't matter what you name the module, it just has to have a different name than the function. Hope that helps..

Doug

 

[giant56]

Sorry to be a burden but I appreciate your help. I tried what you said and now I get that "The expresion that you entered has a function name that Access can't find. Then the macro action failed box comes up and it says.
Macro Name: AutoExec
Condition: True
Action Name: RunCode
Arguements: datecheck
If you can think of anything else I would sincerely appreciate it. Thank you in advance.

 

[D-Fresh]

Okay, two things... First make the function public by defining it like this in the module..

public function datecheck()

Second, make sure there are () around the datefunction in the RunCode action of your AutoExec macro.. like datecheck()

These two solutions should work for you.

Doug

 

[giant56]

Doug- I tried that and it is still saying access can't find my function in the expression. I went back and double, triple checked everything. I don't know what is wrong. Thank you for all your help though.

 

[D-Fresh]

okay, I have no idea what is wrong there, try this then...

Create a form. Create a code in the Open event of the form. Put "Call DateCheck()" as the code(without the quotes). Then run the code to make sure it works. If it does, then change your AutoExec macro to read OpenForm to open the form. Then Close Object, and close the form.

This should work to test your function and see if it's working. I'm sure there is some small thing somewhere that you're missing, that's usually what happens to me. Just as a doublecheck, you saved the module itself as a different name, The function is DateCheck() and the Module Name is different, right? Again, hope this helps.

Doug

 

[giant56]

OK I got the code to start running at start up, but now it is telling me that it can't find the project or library and highlighting MyDB=database.

 

[D-Fresh]

The correct syntax should be ...

either the declare as
dim MyDB as database

or the assignment as
set MyDB = currentdb

The code that I posted worked for me, so make sure you didn't change the variables around...

Doug

 

[giant56]

I think something is messed up with my access program. The database I am trying to set up the date reminder on is stored on a network. I made a new databse on my hard drive using access and all my codes work. Whenever I input any functions on the network, even something like "date()", it doesn't work. So I don't know what is wrong. Thank you so much for all of your help though.

 

[Rich]

Missing references?

 

[Talismanic]

You can check for missing references by starting a new module and then checking Tools > References.

There will me a missing next to the reference, just check it and retry your function.

 

[giant56]

It is saying I am missing Microsoft DAO 2.5/3.5 Compatibility Library. If this is my problem then how do I fix it.

 

[Robert Saye]

Hello,

It may be that your references are pointing to ADO instead of DAO. The code you are using is all DAO. You can change this by opening the module window, click on tools -> references. A list of all reference libraries pops up, scroll down to "Microsoft 3.6 (for access 2k) Object Library" and put a check mark next to that. This way when you have a builtin DAO function or property it can find it.

Here is the ADO version (It works I tried it)


Public Function datecheck()

Dim MyRecs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim MySQL As String

'Make SQL statement to see if any dates in table match current day

MySQL = "SELECT * FROM tblDates WHERE actDate=#" & Date & "#"

Set cnn = CurrentProject.Connection
Set MyRecs = New ADODB.Recordset
MyRecs.Open MySQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
With MyRecs
.MoveFirst
If .EOF = False Then
Do Until .EOF
MsgBox ("Don't forget to do" & vbNewLine & MyRecs("Activity") & " today!")
.MoveNext
Loop
Else
MsgBox ("You have nothing to do today.")
End If
End With
'Clean Up
Set cnn = Nothing
Set MyRecs = Nothing

End Function


HTH
Robert

[This message has been edited by Robert Saye (edited 06-07-2001).]

 

[giant56]

Thank you for all your guys' help. You are real lifesavers, and a whole lot better at this *@$% than I am. Thank you once again.

 

[TClark14]

Hi - I was searching for something similar to this and have tried it, but I am getting an error:
Run Time Error
Too Few Parameters. Expected 1.

and the following line of the code is highlighted.

Set MyRecs = MyDB.openrecordset(MySQL)

Did I do something wrong? My table is tblComments, and has two fields DATE and COMMENTS. I want it to look to see if there is todays date and give me a reminder mesage and if there is no todays date, then continue on with autoexec macro.

Thanks
Terry

 

[bauer]

I would suggest that you shck over your SQL statement, that's usually the clincher.
I'd have to see the SQL statement to find out for sure what it is that is bugging out.

bauer