Follow-on help with reminders and recurring events

[cpocpo]

Hello all!

Still having a fundamental problem making my database work. (I
received alot of help in earlier posts, and this is the last thing to
make this thing workable.)

Basically, I have 40 employees, some military, some civilian. As well
as tracking their basic personal information (SSN, address, spouse
name, etc.), I am keeping track of their "recurring items" (birthdays,
anniversaries, medical exams, dental exams, EEO training, HR training,
etc.).Some of these are every 1 year, some are every 3 years, etc.
When the database is opened, a report should pop up with "The
following events are due this month: Mr. X dental exam due ________,
Mrs. J has a birthday on _______. Also, the following items are
overdue: Mr. P's dental exam was due on _________."

So, each employee can have many different "events", each having their
own date of accomplishment.

Setup:
1) tblEmployee with 3 fields: EmployeeID (auonumber, PK), LName
(text),
and FName (text).
2) tblEventType with 2 fields: EventType (Text, PK) and Periodicity
(Number)
3) tblEvents with 3 fields: EmployeeID (number), EventType (Text),
and
EventDate (Date/Time). No PK assigned for this table.

Relationships:
1) EmployeeID (from tblEmployee) to EmployeeID (from tblEvents)
one-to-many, referential integrity, no cascades, join type 1
2) EventType (from tblEventType) to EventType (from tblEvents)
one-to-many, referential integrity, no cascades, join type 1

Created a form with the wizard pulling LName and FName from the first
table,
EventType from the second table, and EventDate from the third. This
gives me a form with the FName and LName and a subform with the other
two.


Now, I can get the name to update but when I enter the date in the
subform,
I get an error "ding" with every keystroke. I also get the "Changes
not
successful because they would create duplicate values in the index"
error
and the "field cannot be updated" error. There is also errors with
letting
me enter an eventtype..

It seems that the tables aren't "talking" to each other. And this
probably has to work for the popup reminder report to work (which I
will need help with also.)

??

Lost for sure Guy

 

[Guest]

Maybe this will help you......

Setup:

------Tables
tblEmployee

lngEmployeeID Autonumber PK
txtLName text
txtFName text


tblEvents-

lngEventId Autonumber PK
lngEmployeeID_FK long - FK to tblEmployee.lngEmployeeID
lngEventTypeID_FK long - FK to tblEventType.lngEventTypeID
dteEventDate Date/time


tblEventType-

lngEventTypeID Autonumber PK
txtEventType text
intPeriodicity integer


------SUBFORM
Subform recordsource:

SELECT tblEvents.lngEventId, tblEvents.lngEventTypeID_FK,
tblEventType.intPeriodicity, tblEvents.dteEventDate,
tblEvents.lngEmployeeID_FK
FROM tblEventType INNER JOIN tblEvents ON tblEventType.lngEventTypeID =
tblEvents.lngEventTypeID_FK;


There are three fields bound to controls in the subform: lngEventTypeID,
dteEventDate & intPeriodicity
The subform is set to Continuous Forms.

In the subform, I converted the text box for the lngEventTypeID to a combo
box. The Row source is:

SELECT tblEventType.lngEventTypeID, tblEventType.txtEventType,
tblEventType.intPeriodicity
FROM tblEventType;

Properties -
Column count : 3
Column widths : 0";1";0"
Bound column : 1
Limit to List : Yes


The control for the Periodicity (frequency) has these properties:

Locked.........: Yes
Enabled........: No
Tab stop.......: No
Special Effect : Flat


------MAIN FORM
Main form recordsource:

SELECT tblEmployee.lngEmployeeID, tblEmployee.txtLName, tblEmployee.txtFName
FROM tblEmployee;


Link child fields: lngEmployeeID_FK
Link Master fields: lngEmployeeID



HTH