AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Question QUESTION: Hi I am trying to set up a training database, some of the courses dont expire but some do. i have entered an expiry field on the course form, which i am going to either put years or months. So if a course expired after 3 years in this field i will either put 3 or 36 depending on which ever is easiest.
my question is though how can i get ms access to record the expiration date using the StartDate field and the Expiry date field, on the attendees subform??
ANSWER: As a general rule we do not store calculated values. So there is no need to store this information. But you do need to display it. So what you would do is set the ControlSource of the Expiry control on the subform like this:
I would use months for the expiration period so you don't need to try and determine whether the period is months or years.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: I'm sorry about the last question,
i've noticed the only time it will give me the correct information is when i open the events form and then the attendee form. if i close the events form though i just get #ERROR, is there a way in which i can get the information without having to open the events form all the time?
Answer First I think we need to get our terms straight. I believe that's the problem here. A subform is a form EMBEDDED on a main form, which is what I assumed you were referring to since you referred to it as a subform. They way you are referring to the attendee and events form they sound like separate forms, that may be synchronized (so the attendees on show for the event selected on the events form. The code I gave you that refers to a value on another form (Forms!mainformname!Expiry) ONLY works if the form is open.
Frankly, I would create an events mainfrom, with an attendees subform. Makes it easy to see the attendees for any event.
But, if you really want to close the events form when you open the attendees form, you will have to pass the value to the attendees form. You can do this in one of two ways; either using the OpenArgs argument of the Openform method or setting an unbound contolr on the attendees form before you close the evetns form. then reference that unbound control in the DateAdd expression.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
