8
I must start this posing by making the point that I am NOT a VB programmer and I'm something of a Newbie to MS Access. I can program in a number of languages (Java, Javascript, PERL,PHP and TCL) but have never actually learnt VB so my request is that you bear this in mind if you plan on replying to this post.
My request for help is as follows:
I have an MS Access database in which one of the tables within it contains two date fields called "StartDate" and "EndDate" respectively. The table in question is designed to hold entity specific data on conferences hosted by a conference hosting company. Each row in the database contains a new record (entity instantiation) of a specific conference hosted by the company.
The field names are self-explanatory in that the StartDate refers to the date at which a conference started or starts and the EndDate fields refers to the either a future date when the conference will end or when it ended some time in the past. The problem I am having is that I need to apply validatory constraints to both of these fields that are not easily achieved with the application and so therefore I suspect that I need to resort to some VB code.
To be clear, there are cerrtain rules that I need to apply (constraints) to these date fields to ensure that any values entered via a conrresponding entry form or indeed via a datasheet view for a completely new or existing record do not violate these rules. The rules are:
START DATE:
- The value entered cannot be today's date - i.e, it must be greater than today's date and therefore some date in the future.
- The value entered can't be after the value entered in the end date as this mean that in effect a conference could never start.
END DATE:
- The value entered cannot be today's date - i.e, it must be greater than today's date and therefore some date in the future.
- The value entered cannot be before the value entered in the start date field as this would imply that the conference had already finished and all data about conferences will always be entered into the database before the actual start of a conference.
I do not know whether I can utilise code that will operate at the datasheet view level or whether it can operate only via a form but either way, I hope what I'm trying to achieve is clear. The question then is how to do this. I suspect that I am going to need to use VB code which is why I mentioned not being a VB programmer at the outset.
I did think of trying to achieve this by means of two additional database tables: - one called, something like: TBLConferenceStartDates and another called: TBLConferenceEndDates. Given that each record in the main conference details table has a unique ID assigned to it as a primary key (of datatype Integer), one could reference the conference start date and conference end date tables via a foreign key whilst maintaining referential integrity. The task then would be to use a the result of lookup query as the basis for enforcing the validation rules. One could look up the specific start date for a given conference ID, and vice-versa for an end date but I do not know whether it's possible to have a query run and then use the result of a query as a validation rule within Access. If it is possible, I'd love to know how this is achieved.
Any ideas with an explanation as to how any code works, would be very gratefully received.
