Full Version: Yet Another Conditional Formating Question
UtterAccess Forums > Microsoft® Access > Access Forms
I've looked through this site and Microsoft's stuff about conditional formating, but have yet to discover a way of doing what I need done.
I am working on a database for our dog boarding business, and want to highlight a field if the pet is over due for his shots for easy reference. There are two fields in each record, one stating the last time the shot was given and the other stating when the shot is due again. So for the conditional formating I need Access to compare the date listed in the Due field with the current date and highlight in red if the Due date is past the current date.
Any ideas? If ya have any, make them idiot proof, heh.
Jack Cowley
Welcome to Utter Access!
I am not sure how you have your shot records set up but they should be in a table related to the dogs record. This would show as a subform and you can filter the data so that it shows only shots that are due as of today or are past due. No shots due then the subform is blank. With the click of a button you can change the Record Source and show the shot history for the animal.
In the shot record I would have the shot type and shot frequency and then the date the shot was administered. You do not need the NextDueDate as that is something that you calculate as you need it.
Just my 2 cents worth...
i know it's been a long time since i posted that message, but i'm creating the database as a side project to make my job easier and the business more easy to run. so that leaves little time to sit and ponder code. frown.gif
nyway, i will give a better telling of what i have set up thus far and this seemingly simple that i want to do. heh
the database has 3 tables, one deals soley with customer information, the second deals with just the pets, and the third contains the boarding information and money coming in etc. it is the pet database that contains the shot records. there are 6 fields in the database realting to the shot records. (we only require the 3 types of shots) 3 of the fields list the date in which the shot was last given, as came from the vet, and the other 3 are when the shot is due again.
now ideally i would like to simply input the last given date and have access insert the due date back in to the table. (i know lazy, but again all to make this part of the job easier) then the conditional formating part comes in ... simply check to see if the due date for the shot has already passed. i simply want the form field to show up in red as an alert to either remind the customer or call their vet to see if they have updated records.
if you have any thoughts or examples please share them. lol. but do make it simple. frown.gif i've managed to get access to do a lot that i wanted using suggestions from this site ... ms' own site is usually either too technical or just otherwise useless.
just another thought before i close. it would be even better if someone had an idea on how to make access not only do the red tintin but also pop up a message stating the records were out of date.
if you need more information about the database structure just ask, and i will do my best. i would be willing to strip all of the information from it and post a version of it as an attachment.
thanks a million.
Jack Cowley
If your PetShot table has 6 records then it is not normalized. Here are how the tables and the customer table should look. I will assume that a customer can have more than one pet:
CustomerID (PK and auto)
...other necessary fields...
CustomerPetsID (PK and auto)
CustomerID (FK)
...other necessary fields...
ShotRecordID (PK and auto)
CustomerPetsID (FK)
ShotTypeID (FK)
ShotTypeID (PK and auto)
Required (enter how long, in months, is this shot good for)

tblCustomersPets is a subform to a Customer form. tblShotRecords is a subform to tblCustomerPets. This way you can select a customer, select the appropriate pet and then see the shot record(s) for that pet. Here you can update, edit or review the shots record. The records can be filtered so you do not see old records or you can see them all. (This setup is shown perfectly in the "Customer Orders" form in the Northwind sample db that comes with Access.) The tblShotTypesRequired is merely a lookup table of required shots. If at some point in time you require a 4 shot you can easily add it to the list. This table would be used to append shot types to new pets so that all shot types are recorded with the pet information.
This may seem much more complex than necessary but it is the way to do it if you want your db to be easy to use. You will notice that I did not put in due dates as you want to calculate those. The ShotTypes table has a time, in months (my guess, but you can use years if that is more realistic), that you can check against ShotRecords everytime the db starts and if any pets are due shots then you will get a list of those pets.
Good luck with your project!
Your condition would be
e.DueDate.FormatConditions.Add acFieldValue, acLessThan, & Me.Datenow
Me.Datenow and Me.DueDate would be records on the form
Me.DueDate.FormatConditions.Delete ' may not be necessary
Me.DueDate.FormatConditions.Add acFieldValue, acLessThan, & Me.Datenow
Me.DueDate.FormatConditions(0).ForeColor = overduecolor
Me.DueDate.FormatConditions(0).BackColor = ---
I get where you are going, well somewhat. lol
I have attached a copy of the database to this post to show you what I have.

HAs you will see when you click on the customer list, then view the customer record, it gives all of the customers information and then has 2 subforms on the page. The first is liked to the pet's detail and the second is the boarding history/future boarding information.

As I said I have 6 fields that pertain to the shots. 3 different types of shots, and a field with the date last given and the due date. (I did it this way because I simply had no clue on how to make access calculate the due date and then alert me when the due date was passed.) So in reality, I would like to delete the due date section from my table and simply use the conditional formatting to highlight the last given field in red, and pop up an alert that the shots are past due, when the particular pet is shown.

So if you would kindly take a look at what I have done, and see how poorly I made my database. lol Hey you have to love your first time building one and all the fun that goes with it. frown.gif
Edited by: JoshEH on Sat Oct 9 19:06:19 EDT 2004.
Jack Cowley
JoshEH -
I am sorry but I do not have version 2003 of Access. If you will save your db in version 2002 I will take a look at it. I will be gone all day today (Sunday) so I will not get a chance to look at what you have until Monday.
Hey thanks for the offer, but I figured it out. So the lesson is that after a week or so come back to something and figure it out, then smack yourself for it being so simple.
Is I had said at the time I had 6 fields dealing with shots, 3 shots each with a last given and due date. Well I redid my tables creating a shots table holding just the last given date. In my form I set it up to calculate the next do date by using DateAdd and adding a year. I next set up conditional formattng to see if the field value was less then the current date. And works fine now. So it was pretty simple.
Thanks for the views, ideas, and offers.
Jack Cowley
You are welcome and continued success with the project....
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.