Locking in a new record in a subform

[RichKorea]

I have a subform (service tasks) that when I first create a new record on the
main form (service reports), the subform shows just a blank record (the
subform’s ID is still reading AutoNumber), which is fine, since there won’t
always be a task for every service report.

The first thing the users will want to do in the subform is to enter the
name of the service engineer, but because it’s possible for more then one
engineer to work on a task, I was thinking I’d use a separate table to keep
track of the service engineers that would tie back to the tasks via the task
ID, but until the record from the subform is firmly stored in its table, the
enforced integrity won’t let me create the service engineer record, I’m
assuming because the autonumber for the task record isn’t locked in yet.

I tried entering temporary text into one of the bound fields, and
DoCmd.GoToRecord , , acNewRec, but nothing seems to let me then use the task
ID (which is getting a number for a moment, but the number goes away since I
can’t complete the record storage) as part of the engineer table.

I thought about storing the “first†engineer in the task record (which was
how it was previously setup when the user was thinking just one engineer per
task), but it doesn’t seem right to store one engineer in one table and the
rest in a separate table.

Is there a way to tell a record it’s real and get it locked in?

Thanks,
Rich

 

[Michael Gramelspacher]

I have a subform (service tasks) that when I first create a new record on the
main form (service reports), the subform shows just a blank record (the
subform’s ID is still reading AutoNumber), which is fine, since there won’t
always be a task for every service report.

The first thing the users will want to do in the subform is to enter the
name of the service engineer, but because it’s possible for more then one
engineer to work on a task, I was thinking I’d use a separate table to keep
track of the service engineers that would tie back to the tasks via the task
ID, but until the record from the subform is firmly stored in its table, the
enforced integrity won’t let me create the service engineer record, I’m
assuming because the autonumber for the task record isn’t locked in yet.

I tried entering temporary text into one of the bound fields, and
DoCmd.GoToRecord , , acNewRec, but nothing seems to let me then use the task
ID (which is getting a number for a moment, but the number goes away since I
can’t complete the record storage) as part of the engineer table.

I thought about storing the “first” engineer in the task record (which was
how it was previously setup when the user was thinking just one engineer per
task), but it doesn’t seem right to store one engineer in one table and the
rest in a separate table.

Is there a way to tell a record it’s real and get it locked in?

Thanks,
Rich

Your form and subforms is actually dependent upon the structure of your tables.
If a service task can have more than one engineer then you need a table linking
service tasks to engineers.

CREATE TABLE ServiceTasks (
service_task_id IDENTITY (1,1) NOT NULL PRIMARY KEY
);

CREATE TABLE Engineers (
engineer_id IDENTIRY (1,1) NOT NULL PRIMARY KEY
);

CREATE TABLE ServiceTaskEngineers (
service_task_id LONG NOT NULL
REFERENCES ServiceTasks (service_task_id,
engineer_id LONG NOT NULL
REFERENCES Engineers (engineer_id),
PRIMARY KEY (service_tasl_id, engineer_id)
);

Now you can see that you need still another subform linked to the service tasks
subform. Just a guess, of course.