Setting Reminders in a form (1 Viewer)

I am trying to set reminders for a couple things but they require a time after the dates that are entered in the form. For Example, I need a reminder to send a survey 2 weeks after the customer is invoiced. The invoiced column has a date format 00/00/0000 and the survey box has a combo Yes/No drop down. I need to figure out the code to look up how many need a survey sent based on these two criteria.

This is what I have

Private Sub Form_Load()
Survey = DCount("Survey", "Database", [Survey] = No And [Date] >= d14)

If Survey > 0 Then
Me.SurveyReminder.Caption "You have" & Survey & "survey Reminders"
End If

End Sub

You're going to need the specific records as well right? That is going to take a query, right? Make that query, apply your criteria there and do the DCount into it without criteria.

Additionally [Date] is a horrible field name. The reason you have to use brackets there is because its a reserved word and shouldn't be used for a field name. Instead prefix it with what it represents (e.g. InvoiceDate, StartDate, etc.). Lastly, you're table is named 'Database'? That's like naming a cat 'Dog'.

DCount("[RecDate]", "MyRecordsTable", "[Survey] = 'No' AND [RecDate] + 14 <= date()")

Cheers
Goh

Plog,

I am trying to avoid queries as the ones using this are not very familiar with Access so I am making it as point-and-click as possible.

"Date" is not the actually field name I was using it as an example; furthermore, "Database" is not the name of the table, I also used it as an example in order to remove the company name from anything.

GohDiamond,

Thank you very much, that should help immensely. I will mark solved as long as that works and let you know. Have a wonderful day!

So that isn't working for me, sorry for the trouble. Let me try to explain it better..

I am trying to create a reminder form that will come up when my database form is opened. I need the form to search through the table for two criteria, if the Survey collumn is not check (= No) and the Last Date Sent (Changed the name) is older than 2 weeks, which I need to pull up even if it is past the exact two week point. I have been brain storming ways to look this up and every time it comes back with an error.

Private Sub_Form Load ()

Dim SurveyReminder as Integer

SurveyReminder= Dcount("[Survey]", "Database Information", [Survey] = No AND [Last Date Sent]+14 <=Date()")

If SurveyReminder > 0 Then
Me.Survey_Reminder.Caption "You have " & SurveyReminder & "Survey Reminders over due by 2 weeks!"
End If
End Sub


This won't work in my form on load and I am not sure what I am doing wrong. Any chance someone can look at it and see what I might be missing or typing incorrectly? Also, I don't think it will pull anything over 14 days only exactly 14 days. If there is another way to code this that would be wonderful too. Thank you!

its not working because is i think is a boolean field, so change it to:

SurveyReminder= Dcount("[Survey]", "Database Information", "[Survey] = False AND [Last Date Sent]+14 <=Date()")

also this will only trigger on the first record on your form.
if you want this to be triggered on each record as you browse, use the current event of the form.

Jennesa said:

..I have been brain storming ways to look this up and every time it comes back with an error.

Click to expand...

What is the error, show the number and the message?

Jennesa said:

..SurveyReminder= Dcount("[Survey]", "Database Information", [Survey] = No AND [Last Date Sent]+14 <=Date()")
..
...Also, I don't think it will pull anything over 14 days only exactly 14 days.

Click to expand...

It will, because of the <= sign.
If the field [Survey] is a text field, the No has to be surrounded by ''.
If it is a "Yes/No"/"True/False" field type, then 0="No"/"False" and -1= "Yes"/"True".
You're also missing a ".
If [Survey] is a text field:
Else

At first the debugger didn't bring anything up but now it highlights the line for LastDateSent and brings up the error:

Run-time Error ‘2465’:
Microsoft Access Can’t find the field ‘|1’ referred to in your expression.

This is all of the code that I have, the dcount wasn't working the way I needed it to so I was hoping this would work but it still isn't. :banghead:
Any help would be appreciated.

Private Sub Form_Load()

Dim SurveyReminders As Integer
Dim WeekReminder1 As Integer

LastDateSent = DLookup("[Last Date Sent]", "REFDatabase", [Last Date Sent])
WeekReminder1 = DateDiff(d, "LastDateSent", "Date()")
SurveyReminder = DCount("Survey", "REFDatabase", [Survey] = Yes)

If WeekReminder1 > 14 Then
If SurveyReminder > 0 Then
Me.lblSurveyReminder.Caption = "You Have " & SurveyReminder & " 2 week over due surveys!"
Me.lblSurveyReminder.BackColor = vbRed
Me.lblSurveyReminder.FontSize = 700
ElseIf SurveyReminder = 0 Then
Me.lblSurveyReminder.Caption = "You have no 2 week survey reminders."
Me.lblSurveyReminder.BackColor = vbBlack
Me.lblSurveyReminder.FontSize = 500
End If
End If
End Sub

LastDateSent = DLookup("[Last Date Sent]", "REFDatabase", [Last Date Sent])
WeekReminder1 = DateDiff(d, "LastDateSent", "Date()")

Are these the same or different fields?

Different in my code. I looked up the field Last Date Sent in my database because it couldn't find it when I put it in the WeekReminder1. So I did the look up and then inputted what the look up was called into the DateDiff code. I wasn't sure if it would work but I gave it a try. I have another error coming up now though.

In this line:
LastDateSent = DLookup("[Last Date Sent]", "REFDatabase", [Last Date Sent])

It give me an error code:
Run-time error '2465':
Microsoft Access can't find the field '|1' referred to in your expression.

And I am even more confused now!

that error i believe is usually a spelling error.
maybe we should step back a little. what is the exact name of the table and the fields and what are their datatypes.
also check to see that none of the controls on your form have the same names as your fields.

Janessa

You've been shown in posts #3,6 and 7 that the criteria part of DCount has to be a string and is a condition that is evaluated to true or false.

You should use something like
DLookup("[Last Date Sent]", "REFDatabase", "[Last Date Sent] + 14<Date()")

Moke,

I will get back to you tomorrow but I unfortunately do not think that this will do what I need it to. Thank you for your help!

Cronk,

Yes, that much is obvious. However, it is not working in my database. It continues to come up with an error no matter what way I type it. The problem is not the quotation marks as it comes up with the error with or without them. The problem lies somewhere else in the code and if you do not have any other new information to attempt to assist me with my issue then I would kindly ask you to refrain from responding as if I do not know anything about access and treat me as if I am just ignoring the advice I have received because I can assure you I have attempted to use the advice, there is just another issue within my code. Thank you, have a wonderful day.

At least the below 2 code lines is wrong:
Here is a link to some samples for DLookup:
http://www.techonthenet.com/access/functions/domain/dlookup.php
Here is a link to some samples for DCount:
http://www.techonthenet.com/access/functions/domain/dcount.php
Then I'll refer to my earlier post regarding the Yes/No!

Jennesa said:

Cronk,

Yes, that much is obvious. However, it is not working in my database. It continues to come up with an error no matter what way I type it. The problem is not the quotation marks as it comes up with the error with or without them.

Click to expand...

Your criticism is extremely unjustified. You have used incorrect syntax in virtually every post and should not blame Cronk for pointing it out when you continue to get it wrong after being advised.

I would kindly ask you to refrain from responding as if I do not know anything about access and treat me as if I am just ignoring the advice

Click to expand...

Saying that you have tried with and without the quotes makes it quite clear that you do not understand the syntax of domain aggregate function. Moreover on some posts, including the last one before Cronk's, you have not even used a third argument that constitutes a condition. And you didn't have the quotes as you had been advised to do.

You show a DateDiff function where none of the parameters are the correct datatype, putting quotes around the date expressions and omitting quotes on the datepart parameter, the only one that should be a string.

So in fact, it is perfectly reasonable that you should be treated as though you don't know anything about Access.

I would not be surprised if the problem is that you have used a reserved word in a critical role. The cannot find field '|1' is typical of errors thrown under such circumstances.

Hi Jennesa,

I hate syntax, do you hate syntax? MS Access LOVES syntax, in fact it DEMANDS syntax be 100% correct or it will do screwy things to you on purpose, especially give you errors that are so cryptic that they are hard to decipher. The only way you get used to such antics from MS Access is to persevere.

The Folks here that have been posting for years, helping others to navigate the marshes of SQL and VBA and best practices for building your interactions in MS Access, by and large they know what they are talking about. And in the midst of trying to take advice anyone can stumble and fall on the syntax grater, coming out shredded by the VBE compiler, thinking that you've entered it correctly but on deeper reflection finding that you have missed a ' or a " or even a space, or messed up the order of operations, etc.

Don't give up, and don't let your frustrations push you to vent in the forum, that will not get you extra help, unfortunately, but true. So I have re-read the advice given and your post-advisory posts. I have noted that SYNTAX has been the great demon that you appear to be struggling with. Please take a closer look at your code or SQL statements which you are using. Copy and paste EXACTLY as they are currently written and quote the Error information verbatim. I'm sure you will find it more effective in resolving your issue.

Cheers!
Goh

JHB,

I appreciate all of your help and I will most definitely look into the references you sent me. Thank you very much, hopefully I can figure out what I am doing wrong, it may be something as simple as my table names are not a good fit for VBA code. I will continue trying to understand the way the program works to fix the database I am working on. Have a great day!

Galaxiom,

I have not once claimed I was supurb at coding. In fact I have had to teach myself all of this coding in a few short weeks and I do not intend to pursue it beyond this project. The problem lies in that I do not appreciate my intelligence being questioned and I don't appreciate the condescending tone from some people on this site simply because I do not know anything about coding. If I did, I wouldn't be on here asking so many questions. Also, it would quite obviously show that what has been suggested isn't working either when I have repeatedly explain that I have tried what has been suggested.

I would also like to point out as I am not a programmer I have no idea what syntax is. Hence the reason I have asked what is wrong and done every single example and answer I have been given in order to fix the problem, unfortunately none of them have worked. I have not said that what anyone has told me has been wrong just that the problem is somewhere else at this point. It could be in every other line of code I have typed, I don't know. My issue with the way the messages have come across has nothing to do with what has been said, but in the condescending way the information has been relayed. I am new to coding, I have only done the most basic forms of html and that was over three years ago, I also haven't touched it since then. Explaining the issue in the language of the program that I already do not understand only further confuses me. I apologize I am not intelligent enough to know all you know of coding in order to understand what you have suggested.

Thank you to those on this site that actually want to help people, I will use your advice as best as I can. Unfortunately, I will just have to try and figure the rest out for myself as it seems only programmers are fully welcome on this site. I hope everyone has a wonderful day and thank you again for those being patient with me as I know nothing about this subject.

Thank you Goh, it has not been what anyone has suggest that has pushed me past my limits. I am also not frustrated with the program as I know it is completely my fault because I don't know how to use it. I am irritated with those who act like I should know all of this when I repeatedly explained and showed that I am new to this.

I unfortunately cannot copy and paste what I need because of the obvious issue of the names being different but I have copied the format the exact same and I come up with the same error. It could very well be a problem with something else in my code but I do not understand what syntax is so I have the additional problem of I don't know what else to try and fix it. I appreciate your help and concern and I really appreciate your words of encouragement as they have been the nicest thing I have read on this site.

I unfortunately will not be getting back on as much as I have found this site to be a great help with all of the experienced and kind users I just have too much stress creating this database from scratch to add on the stress of users who are less than helpful with spiteful and rude remarks.

Thank you again to you and the many others who have been increasingly patient and helpful in the past couple weeks. I wish all of you well! =-]