For those that like complications. I am trying to keep track of licenses issued to the skilled trades for work in our Town.
Our fiscal year begins July 1st and ends June 30th of the next year.
We issue licenses to Electricians, Plumbers, and Mechanics. The payment is variable, depending upon whether they want to pre-pay for the next year when they get a license or when they renew.
$35 paid, month of June or July, expires June 30th of next year
$70 paid, Month of June or July, expires June 30th year after next
$100 paid, if before June, expires June 30th of the current year
$100 paid, June or later, expires June 30th of next year
$135 paid, before June, expires June 30th of next year
$135 paid, June or later, expires June 30th year after next
When a person pays for a license, the relevant information is entered into my database, along with the amount and the date of payment.
Here is what I would like to have take place:
1) When I enter the information into the form frmTrades (for the tblTrades) I want the textbox labeled "expires" to show the expiration date.
2) When an Inspection is requested by one of the licensees, and I enter the request in the "Inspections" form (for the tblInspections) I want to choose the business from a drop-down and have the status for that business automatically popup and display the message "Expires ddmmyy" or "Expired ddmmyy" so that I know whether they are current with the City.
What would be really neat, is if the license has expired, is to have the pop-up flash!
Thanks for your suggestion. I have backed off trying to do the whole thing for now, and instead I am going to take an incremental approach to the problem. In that regard I have done the following: I created a Calculated Control on my form that I use to input data into the tblTrades, The Calculated Control is labeled "Status" and it uses the "Expires" date that I input manually. This is what I came up with "=IIf([Expires]<Date(),"EXPIRED!"," ")." Now when I callup the form, I see the status for each company as I browse the records.
Now when I use another form I would like to link (maybe not the right term) a field in the table with another field in the same record. Example: I choose a name from a dropdown (I know how to do that) and the date "Expires" for that record fills into a textbox on the form. Once that happens I can create a Calculated Control that alerts or gives me the status for that company.