Access VBA Text Box Visible If

0
votes

I have a simple access database that holds contacts. My people table has a column named "Last Contact" that is a date value representing the date of last contact. On my main screen I have a form that displays the person's information, including the "Last Contact" date.

I need to build in an alert system that let's users know when it has been longer than 30 days since the last contact date. I have built a simple text box that accomplishes this by displaying the text "30 Days" in big red letters. The name of this text box is "notice."

My problem comes with having this text box only appear when it has been 30 days or more since the "Last Contact" date. I am using the code below:

Sub notice()
Dim dateone As Date
Dim datetwo As Date
Dim days As Integer
Dim notice As Object

dateone = Last_Contact!
datetwo = Date
days = DateDiff("d", dateone, datetwo)
notice = [notice]

If days >= 30 Then
notice.Visible = True
Else
notice.Visible = False
End If
End Sub

When I run this, I get an "Overflow" error. Can someone please assist me with this?

Thank you in advance.

2
ms-accessvbams-access-2010
Which line causes the overflow error? – SeanC
What happens if you dim Notice as TextBox and not as Object? I would also consider naming your VBA notice as something other than the textbox name to avoid confusion, and potential issues with the code. – guitarthrower

2 Answers

0
votes

Note that if you make the textbox via UI like if you drag a textbox to the form, you do not need to declare it. And also can you be more specific or provide a screenshot of where you getting the overflow?

0
votes

Hmmm, you seem to have a field, a sub, and a variable all named 'notice' - not a good idea.

And presumably the line "dateone = Last_Contact!" is a typo?

Try using the source of field [notice] as:

=iif(DateDiff("d", dateone, Date())>=30,"30 Days",Null)