What To Do

[PaleRider]

Hi Everyone,

What I would like to do is mark specific records where an amount is overdue
at the end of each month. For any record that is overdue, I want a hidden
TextBox to appear on the form that say's "OVERDUE". Once the bill is paid
and I change the DueDate to a different date, I want the hidden TextBox to
hide again. The hidden TextBox should only appear on the form when a bill is
overdue, but disappear after the bill is paid. Can someone please help.
Thank you ahead of time.

Here is my table and form setup:

Table Name: ClientTable
Client_ID = PK
ClientName = Text
Phone = Text
AmountDue = Text
DueDate = Date
PastDue = Boolean


Form Name: ClientForm
ClientName = TextBox
Phone = TextBox
AmountDue = TextBox
DueDate = TextBox
PastDue = Hidden TextBox

 

[Mr. B]

One way to do what you want would be to have code in the On Open event of
your form that would run an Update Sql statement that would set the PastDue
field to true for all records where the DueDate is greater that the current
day's date.

The code to do this might look something like the following 'air code':

Dim strSql as string
strSql = "UPDATE tblQuestions SET tblQuestions.Answer = True " _
& "WHERE (((tblQuestions.duedate)<Date()));"
Currentdb.Execute strSql

Then in the On Current event of your form have code that would check the
value of the PastDue control in your form for the value of the the PastDue
field and display a label that has the "OVERDUE" in bold red letters.

The code to do this might look something like the following 'air code' (the
Label control should have the visible property set to false) :

if me.NameOfPastDueControl = -1 then
me.NameOfLabelControl.Visible = True
else
me.NameOfLabelControl.Visible = false
end if

Using this method, you would also need code in the After Update event of
your DueDate control that would set the PastDue field to False.

The code to do this might look something like the following 'air code'
if me.NameOfDueDateControl > Date then
Me.NameOfPastDueControl = 0
Me.NameOfLabelControl.Visible = false
else
Me.NameOfPastDueControl = -1
Me.NameOfLabelControl.Visible = true
end if

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[PaleRider]

Mr. B,

That looks like it might work. I'll give it a try. Thank you too for
responding so quickly.

 

[PaleRider]

Mr. B,

Oh my god, it works perfectly. I am completely blown away here and can't
believe what I am seeing. I don't know how to thank you except by saying
that you are absolutely awesome. I thank god for you because I would not
have been able to do this myself. God bless you As someone else wrote
in this forum:

U ARE D MAN!!!

-PR

 

[Mr. B]

You are quite welcome, PaleRider.

Glad to help.

Good luck with your project.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[PaleRider]

Mr. B,

One last thing, I promise. Out of curiosity in case I ever have yearly
accounts, what would I need to change in the code to test for end of year
instead of end of month?

-PR

 

[Mr. B]

I would think that no matter what the length of the term that any account
might have, the due date would be set as appropriate. If so then those
account that have a due date that was actually a year from the start of the
billing cycle would stil only show up when the due date is less than the
current date.

If I have misunderstood, please explain a little more what you are wanting
to do.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[PaleRider]

Mr. B,

Yep, that sounds about right to me. Don't know what on earth I was
thinking. Many thanks again for your reply and your solution as well

-PR