Event prevents new record addition?

[Elizabeth Swoope]

I have an event that "reads" the contents of the first field in a table (the
key field) and creates values in two other fields based on the key field. (No
lectures on why this isn't a good idea, please. I have my reasons and it's a
small database.)

The event works fine if I tab out of the field (using an Exit event).

However, if I use it as an Exit event, when the table is opened, the cursor
is in the first field of the first records (as it should be), but I cannot
add a new record by clicking on the "add new record" button in the navigation
area at the bottom of the form. If I tab out of the first field, then I can
add a new record, but my cursor is not in the first field of the new record.

If I use a Lost Focus event, I have to click the "add new record" button
TWICE, but I can add a new record.

If I have no events, I can add a record the normal way, by clicking the "add
new record button" once.

Which one of the forty-kazillion events should I use so that 1) the proper
calculations are made when I exit the first field no matter how I choose to
exit it (tab or add new record or enter would be the most likely ways, but
someone might click somewhere in another record), and 2) I can add a record
to the end of the table by clicking the "add new record" button ONCE?

 

[Graham Mandeno]

Hi Liz

Might as well pick up this one too

Presumably the calculation needs to be done only after the key field's value
is entered for the first time, or changed. So you should use its
AfterUpdate event.

If I may be nosy, what are your "reasons"? There might well be a better way
to do this.

 

[Elizabeth Swoope]

Graham,

Gadzooks, you're a genius <g>! That did the trick. My problem is that I do
not have a solid understanding of just what each of those forty-kazillion
even triggers do. I've read about them but haven't absorbed everything.

Reasons? This is a small database used occasionally by one or two people.
I've spent way more time working on it than I should have but the person who
first set it up knew a lot less about databases (in general) than I do.

This first field is a semester code. The user types either "s" (for spring),
"f" (for fall), or "m" (for summer), followed by the two-digit year. Then I
use dlookup to get the spelled-out semester, the four-digit year, and a
sequence code (fall semester of 2006 is actually the first semester of the
2007 academic year, which is 20071) and I actually write them into the table.
I know you're not supposed to do that and you're not supposed to work with a
table (use a form and maybe a query), but I'm the only one who's likely to
tinker with it that way and I don't need the hassle of additional queries and
forms.

Same thing with the course info. The user types some letters and numbers
(department abbreviation and course number) and the code splits it into
department and course number separately and writes that into the table, as
well as looks up the course name and credit hours and writes those into the
table. That's necessary because sometimes course names change and we want the
name of the course when the student took it, not whatever the name happens to
be right now, and sometimes credit hours change or they vary (one student may
take 3 thesis hours and another takes 6, same course).

liz

Hi Liz

Might as well pick up this one too

Presumably the calculation needs to be done only after the key field's value
is entered for the first time, or changed. So you should use its
AfterUpdate event.

If I may be nosy, what are your "reasons"? There might well be a better way
to do this.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have an event that "reads" the contents of the first field in a table
(the
key field) and creates values in two other fields based on the key field.
(No
lectures on why this isn't a good idea, please. I have my reasons and it's
a
small database.)

The event works fine if I tab out of the field (using an Exit event).

However, if I use it as an Exit event, when the table is opened, the
cursor
is in the first field of the first records (as it should be), but I cannot
add a new record by clicking on the "add new record" button in the
navigation
area at the bottom of the form. If I tab out of the first field, then I
can
add a new record, but my cursor is not in the first field of the new
record.

If I use a Lost Focus event, I have to click the "add new record" button
TWICE, but I can add a new record.

If I have no events, I can add a record the normal way, by clicking the
"add
new record button" once.

Which one of the forty-kazillion events should I use so that 1) the proper
calculations are made when I exit the first field no matter how I choose
to
exit it (tab or add new record or enter would be the most likely ways, but
someone might click somewhere in another record), and 2) I can add a
record
to the end of the table by clicking the "add new record" button ONCE?

 

[Graham Mandeno]

Hi Liz

I wasn't really being nosy I asked because I thought you could make
life much easier for yourself, and I think I'm right.

You should create a table of Semesters, with the following fields:
SemesterCode "f06"
AcademicYear: 2007
SemesterNumber: 1
Season: "Fall"
... StartDate, EndDate, etc

Then, create a relation between your Semesters table and your Enrolments
table, so for any enrolment in "f06", all the above related fields are
available automatically via a join in a query.

No DLookups required - in fact, no code at all!

Same thing applies to Courses:
CourseCode: "MUS101"
Department: "Music"
CourseNumber: 101
CreditHours:
... any other data about the course

Of course, as you point out yourself, it is often necessary to duplicate
values from you lookup table fields. An obvious example of this is in an
order entry system, where prices for products vary over time, but you need
to record in the order record the price *at the time of the order*. I call
this "point-in-time" data, and it's what you have here with the credit
hours.

But you don't need dlookups and you don't need much code. All you need, in
the AfterUpdate event for Enrolments.CourseCode, is to copy the value from
the field in the joined Courses table into the new Enrolments record. Then
you can change it if necessary, and it will not be affected by future
changes to the course.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Gadzooks, you're a genius <g>! That did the trick. My problem is that I do
not have a solid understanding of just what each of those forty-kazillion
even triggers do. I've read about them but haven't absorbed everything.

Reasons? This is a small database used occasionally by one or two people.
I've spent way more time working on it than I should have but the person
who
first set it up knew a lot less about databases (in general) than I do.

This first field is a semester code. The user types either "s" (for
spring),
"f" (for fall), or "m" (for summer), followed by the two-digit year. Then
I
use dlookup to get the spelled-out semester, the four-digit year, and a
sequence code (fall semester of 2006 is actually the first semester of the
2007 academic year, which is 20071) and I actually write them into the
table.
I know you're not supposed to do that and you're not supposed to work with
a
table (use a form and maybe a query), but I'm the only one who's likely to
tinker with it that way and I don't need the hassle of additional queries
and
forms.

Same thing with the course info. The user types some letters and numbers
(department abbreviation and course number) and the code splits it into
department and course number separately and writes that into the table, as
well as looks up the course name and credit hours and writes those into
the
table. That's necessary because sometimes course names change and we want
the
name of the course when the student took it, not whatever the name happens
to
be right now, and sometimes credit hours change or they vary (one student
may
take 3 thesis hours and another takes 6, same course).

liz

Hi Liz

Might as well pick up this one too

Presumably the calculation needs to be done only after the key field's
value
is entered for the first time, or changed. So you should use its
AfterUpdate event.

If I may be nosy, what are your "reasons"? There might well be a better
way
to do this.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have an event that "reads" the contents of the first field in a table
(the
key field) and creates values in two other fields based on the key
field.
(No
lectures on why this isn't a good idea, please. I have my reasons and
it's
a
small database.)

The event works fine if I tab out of the field (using an Exit event).

However, if I use it as an Exit event, when the table is opened, the
cursor
is in the first field of the first records (as it should be), but I
cannot
add a new record by clicking on the "add new record" button in the
navigation
area at the bottom of the form. If I tab out of the first field, then I
can
add a new record, but my cursor is not in the first field of the new
record.

If I use a Lost Focus event, I have to click the "add new record"
button
TWICE, but I can add a new record.

If I have no events, I can add a record the normal way, by clicking the
"add
new record button" once.

Which one of the forty-kazillion events should I use so that 1) the
proper
calculations are made when I exit the first field no matter how I
choose
to
exit it (tab or add new record or enter would be the most likely ways,
but
someone might click somewhere in another record), and 2) I can add a
record
to the end of the table by clicking the "add new record" button ONCE?

 

[Elizabeth Swoope]

Graham,

I have to use the semester info in four places in the student table
(semester they entered, semester they are supposed to graduate, semester they
pass a particular exam and semester they actually graduate) plus in each
student course record.

I tried doing all that with a single lookup table (as you describe, which I
already have) and couldn't accomplish it. I've since run across the solution
for doing it, but that ends up making a very complicated relationship among a
whole bunch of tables. This is such a small application, used by one person
and maintained by yours truly (who, as you can tell, is NOT an Access
programmer). If I'm not dealing with it, chances are virtually 100% that
whoever is stuck with it will know even less than I do.

It truly is easier with this particular application to use the two
"reference" tables (the semesters and the course master) with dlookup and
write the appropriate into into the student and studentcourses table.

In the students table, which has the four different semesters, I don't even
write in the semester code. I write in "Fall 2006" (or whatever).

The course title and course credit hours do have to be written into the
student courses table because those are both subject to change. I might as
well write the rubric and course number in...

I've already spent way too much time on this project, but you should have
seen what it replaced! The poor woman doing the data entry had to type in
that stupid code (20071) and the word description, and there was no master
course info. She had to look up EVERY course (because the students didn't
always provide the correct course name). I've made it where she just types a
short semester code that makes sense and is easy to remember and a course
code and everything is filled in automagically.

Oh, and the old program required that she preview the report and scroll
through nearly 200 pages, then remember to print just the one she wanted. In
my system, there's a "print" button on the main student form and that prints
the ONE record she wants, with the signature page (including the committee
members' names where they belong) on the back.

I really appreciate your interest and your help. I think I've resolved all
the problems except the continuous forms one and I see I've gotten a response
on that one. If I have to live with that one, I can.

liz