Form/Subform problems

  • Thread starter Thread starter TP
  • Start date Start date
T

TP

I am trying to create a form with a subform for data entry and I cannot get
it to work, even when using the form wizard. Here are my Fields:

Employee Information Table: EmplNum, FirstName,LastName

EmployeeWorkTracking Table: WDate, EmplNum, JobCode, StartTime, EndTime

From the main form, I want to enter the WDate and EmplNum, then tab to the
subform and enter the JobCode, StartTime & EndTime. For each day, each
employee will have numerous Jobs with Start and End Times. I want to keep
entering them until I advance the main form.

The first problem with the wizard-generated form is if I enter a new
EmplNum, it does not synchronize the FirstName & LastName fields. These
are on the form both to visually confirm that we are entering data for the
correct employee, and also so that we could select the agent by name if the
employee number was not known (I'd like to use SELECT DISTINCTROW but the
option doesn't seem available with this setup). The second problem is, I
don't have an option to put WDate on the main form with EmplNum, because
they are not from the same table.

I also took an existing form that has all the fields (with EmplNum,FirstName
& LastName all tied together with SELECT DISTINCTROW), and used
drag-and-drop to pull the EmployeeWorkTracking table in as a subform,
linking the WDate and EmplNum fields. This seemed to work in that when I
enter WDate and EmpNum in the main form, they pop in to the subform. But
when I try to enter a JobCode directly in the subform, I get "the field
employeeWorkTracking.JobCode cannot contain a null value because the
required property for the field is set to true. Enter a value in to this
field. But it won't let me!

I have checked help, I have googled and I can't see what is wrong (although
I am certain I am missing something simple). Any advice would be greatly
appreciated!

Patti
 
you have a parent/child relationship between tblEmployees and tblEmplWork
(my names for the tables you described). the form/subform should mirror
this: main form bound to tblEmployees and the subform bound to tblEmplWork.
[suggest you set the tables' relationship in the Relationships window (see
the database window toolbar), on field EmplNum; and enforce referential
integrity.]
create a form bound to tblEmployees, as frmEmployees. create a form bound to
tblEmplWork, as frmEmplWork.
in frmEmployees design view, add a subform control, which i'll call Child1.
in the Properties box, set the SourceObject as frmEmplWork. set both
LinkChildFields and LinkMasterFields to EmplNum. now the subform records are
linked to and synchronized with the main form form records. each time you
enter a new record on the subform, Access will automatically enter the
EmplNum from the current record on the main form.
if you want to enter a date once, on the main form, and have it carry over
to field WDate on the subform, you can. add an unbound text box to the main
form (which i'll call MyDate), where you will enter the date. in the
subform's *form* event BeforeUpdate, or event BeforeInsert, add an event
procedure, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!WDate = Me.Parent.MyDate

End Sub

hth
 
Thanks Tina,

I'll try it & post back if I have any problems.

Patti


tina said:
you have a parent/child relationship between tblEmployees and tblEmplWork
(my names for the tables you described). the form/subform should mirror
this: main form bound to tblEmployees and the subform bound to tblEmplWork.
[suggest you set the tables' relationship in the Relationships window (see
the database window toolbar), on field EmplNum; and enforce referential
integrity.]
create a form bound to tblEmployees, as frmEmployees. create a form bound to
tblEmplWork, as frmEmplWork.
in frmEmployees design view, add a subform control, which i'll call Child1.
in the Properties box, set the SourceObject as frmEmplWork. set both
LinkChildFields and LinkMasterFields to EmplNum. now the subform records are
linked to and synchronized with the main form form records. each time you
enter a new record on the subform, Access will automatically enter the
EmplNum from the current record on the main form.
if you want to enter a date once, on the main form, and have it carry over
to field WDate on the subform, you can. add an unbound text box to the main
form (which i'll call MyDate), where you will enter the date. in the
subform's *form* event BeforeUpdate, or event BeforeInsert, add an event
procedure, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!WDate = Me.Parent.MyDate

End Sub

hth


TP said:
I am trying to create a form with a subform for data entry and I cannot get
it to work, even when using the form wizard. Here are my Fields:

Employee Information Table: EmplNum, FirstName,LastName

EmployeeWorkTracking Table: WDate, EmplNum, JobCode, StartTime, EndTime

From the main form, I want to enter the WDate and EmplNum, then tab to the
subform and enter the JobCode, StartTime & EndTime. For each day, each
employee will have numerous Jobs with Start and End Times. I want to keep
entering them until I advance the main form.

The first problem with the wizard-generated form is if I enter a new
EmplNum, it does not synchronize the FirstName & LastName fields. These
are on the form both to visually confirm that we are entering data for the
correct employee, and also so that we could select the agent by name if the
employee number was not known (I'd like to use SELECT DISTINCTROW but the
option doesn't seem available with this setup). The second problem is, I
don't have an option to put WDate on the main form with EmplNum, because
they are not from the same table.

I also took an existing form that has all the fields (with EmplNum,FirstName
& LastName all tied together with SELECT DISTINCTROW), and used
drag-and-drop to pull the EmployeeWorkTracking table in as a subform,
linking the WDate and EmplNum fields. This seemed to work in that when I
enter WDate and EmpNum in the main form, they pop in to the subform. But
when I try to enter a JobCode directly in the subform, I get "the field
employeeWorkTracking.JobCode cannot contain a null value because the
required property for the field is set to true. Enter a value in to this
field. But it won't let me!

I have checked help, I have googled and I can't see what is wrong (although
I am certain I am missing something simple). Any advice would be greatly
appreciated!

Patti
Click to expand...
Click to expand...
 
Back
Top