Hi Engels,
Tim (see Tim Ferguson's post) is correct in describing the 'best practice'
design. In fact to my mind a full design has a Course table with a
CourseType table qualifying a foreign key value in Course. Then there is a
Person table that contains details of student/delegates (and trainers if you
wish). There is also a CourseInstance table that is a child of the Course
table giving the dates, location, fees and whatever else you need to record
about a training session. There could be many CourseInstances for each
Course. Finally there is an associative table relating CourseInstances with
Persons. The benefit of first designing a full detailed entity relationship
is that as you reduce it to a more practical level that suits the
circumstances you know exactly what features and functions your design cannot
provide and what assumptions you are making. If you want to take this
further try and find some texts on Data Normalization (3rd Normal Form is
sufficient) and Entity Relationship Diagrams. The Access Relationships
window is in fact an Entity Relationship Diagram (ERD) when populated with
relationships. The fact that it also shows attribute listings inside the
entity boxes makes it a Bachmann Diagram (think I'm right - seem to remember
that name from eons ago).
But for let's assume you are going ahead with your current design. You say
Year Planner is based on (bound) to Training Course yet the latter is the
table for which you need to compose a primary key. Thus you cannot save a
new record until you have inseterted the new key in the field bound to the
table attribute (or column) for that primary key. I therefore recommend you
use the form's BeforeUpdate event to insert this key. However as the event
fires for all updates, not just new records, it is important to first test
for the existence of a key, supplying a new one only if it is missing.
A prerequisite is that you must include the primary key attribute/column in
your bound result set - expressed less formally: Course ID must be implicitly
selected by use of "*" in the SQL (or wizard) or specifically selected by
name. It may be included as a field on the form and this is probably best as
you have a meaningful key to which users can refer. If it is I suggest you
make the field/control Disabled and Locked. Also turn off the Tab Stop. I
will assume this control is named Course ID.
Don't use the name Date by itself for anything. Date is a function that
return the system date. Most of the time you will get away with it but
sooner or later there will be an instance where you get an unpredictable
error. I assume you rename this Course Date.
You mention that the user enters Course Type. I won't enquire how the user
selects this but just assume a valid course type is available in a
field/control on the form.
In the form's BeforeUpdate procedure code something like the following. (I
have used the underscore concatenation symbol to split an otherwise long
statement across several lines.)
If Len(Trim([Course ID])) = 0 then
[Course ID] = [Course Type] & _
Format(DatePart("d",[Course Date]),"00") & _
Format(DatePart("m",[Course Date]),"00") & _
Format(DatePart("yyyy",[Course Date]) Mod 100, "00")
End If
If all my assumptions are correct Access takes care of the rest.
Regards,
Rod
Engels said:
Hi Rod
I think you understand most of what i am saying. You are right i want to
concatenate.
I had forgotten i needed to turn the date into a string i think i wil be
able to work this out.
The course abbreviation is in the "Course Types" table although it is not
the PK. The [Training Course] primary key needs to be a concatenation of the
abbrevation of (a text field "Course Code") and the date of the course to
make a unique identifier (there cant be two types of the same course on the
same day)..
The database is fairly well developed so far with manual entry of the Course
ID, hence it wil be a pain to redevelop by changing the way the primary key
works.. hence the fact i am trying to work around this by creating this
"automatic" primary key.
I'm still a newbie with the coding but i guess i need the help most with the
code fragment that will copy the data from one table to another and then
concatenate it with the date field from the same table that i am "pasting" to.
Thanks