subform default values

[Cheryl]

Hi,
I'm trying to design a database to log our daily production. I have a form
(production) with a subform (employees). We can have up to 4 employees
working on each line. I would like to have it remember the employees that
were listed on the previous production record as the default value. The
after update option in the employee field doesn't work, somehow I need it to
work only when I'm creating a new production record.

These values change from day to day and machine to machine, so I can't just
key in the default values either.

Can anyone help me with this? It's been a long time since I've worked with
Access, so consider me a novice.

Thanks,

 

[Marshall Barton]

I'm trying to design a database to log our daily production. I have a form
(production) with a subform (employees). We can have up to 4 employees
working on each line. I would like to have it remember the employees that
were listed on the previous production record as the default value. The
after update option in the employee field doesn't work, somehow I need it to
work only when I'm creating a new production record.

These values change from day to day and machine to machine, so I can't just
key in the default values either.

Can anyone help me with this? It's been a long time since I've worked with
Access, so consider me a novice.

It's impossible to straighten this out without knowing the
structure of your tables, but it sure sounds ("up to 4
employees") like they are not normalized. From your
description, all I can do is guess that the emplayees table
has four fields for employee entries and a foreign key field
to link to a date entry in the production table.

If so you can use the main form's Current record event to
set the subform employee control's DefaultValue property.
I'm guessing that the code might, maybe look something like:

Set frm = Me.thesubform.Form
With frm.RecordsetClone
.MoveLast
frm.employee1.DefaultValue = """" & .employee1 & """"
frm.employee2.DefaultValue = """" & .employee2 & """"
. . .
End With
Set frm = Nothing

 

[Cheryl]

I'm not quite sure how to explain the way it's set up, but here's a try. The
subform is linked to a table that consists of a primary key (auto number),
the production ID as a foreign key to link to the production table and the
employee name which is a foreign key to the list of employees. The subform
is a continuous form filtered by the production ID.

Also when I enter the code you gave me I get an error message that says No
current record and highlights the .MoveLast line

 

[Marshall Barton]

I'm still struggling to figure out what is in the subform.
You said it was linked on the production ID field, but I was
expecting there to be records from the previous day's run to
be there too. Maybe I don't understand what the production
ID field represents or maybe there is a date field involved
somewhere? If each run has a different production ID, then
I guess the subform will never have data from the "previous"
run and we'll need a query that can retrieve the employee
records from the "previous" run instead of using code like I
posted.

I understand that the subform is bound to the junction table
in a many-many relationship so it probably is normalized and
you just never have more than four employees assigned to a
production run.

However, that also implies that the subform will have up to
four records for a production run and you want each record
to get a different default value depending on the employee
records from the "previous" run. Setting a different
default for each record will be messy at best, even after
you explain how the "previous" production run is identified
(seems like there has to be a date field in the production
table).

Is this default value for a combo box with the employees
table as it's row source?