RE: Database Confusion

Datasheets are a little bit weird sometimes to work with if you’re not to
them so (if it was me this is what I’d do).

Create a new query.
Right click the design grid (they blank bit at the top) and select view Show
Table.
Double click the Employees table and the shifts tables.
Close the properties box.
If here is a relationship shown between the tables (a line between the two)
– right click and delete it.
On the Employee Table click and drag the EmployeeID and drag it across to
the EmployeeID that is in the Shift table (if it isn’t then go back to the
table design and put it in)
Bring the fields that you want in your form (when you make it) like Name,
shift, task, etc.
Save the form and close it.

Create a new form. Call it frmShifts. Save.
Click View (at the top of the screen) and then select FieldList
Very Important bit - - - Only bring in the fields that are applicable to the
“shift” not the employees. Add these to the form Header - leave the details
section blank for the moment.
Shift Name.
Shift Date.
Shift Location
Ect.
BUT most importantly you MUST include ShiftID.
Save the form and close it.

Create a new form. Call it frmEmployees
Do the same as you did for the frmShift but this time bring in only those
fields that are applicable to the employees.
All these fields will normally go into the Detail section. Put them in 1
line next to each other. Don't have too much space above or below the fields
in the detail section.

In the “Header Section” you MUST put the ShiftID and the ShiftDate. (you
can add other stuff if you want but the ShiftID is very important). A nice
Lable is a good idea but that one of the things to do after you get the basic
to work.

Again if you don’t have these go back to the table design and put them in.
Right the area outside the design (normally grey) and open the properties
box.
Select Format
Select = Default Format = Continuous Forms
Save the form and close it.

Open the frmShifts in design view
Click view then Toolbox so you can see the toolbox.
Select Subform (click it – it will change color)
Single left click the Details section of your form.
And a box will open – select use and existing form
Select frmEmployees from the list (if there is one).
Click next and select Define My Own (form parent/child)
In both the panes select ShiftID
ClickNext and save (as frmSubEmployees)
Save the form.

When you look at the form now (in form view) the frmShifts will have a
subform showing the employees on that shift.

Next the date.
If it was me designing for inexperienced users I may be temped to use a
calendar control (search this forum for tips on this).
But for now.
Open you frmShifts in design view.
Right the date box and open the properties box.
Go to the data column and put this
Control Source = Your Date Field
Input Mask = 00/00/0000;0;_
Default Value = =Date()

Note that
00/00/0000;0;_ will be used for dates like this 25/12/2006 (this Christmas
day). If you want you can alter this to whatever you find easier.
=Date() will mean that the current date will be shown on new records.

To get the date that want simply open the form in Form View and type in the
date that you want and the employees on shift that day will be shown in the
sub form.

Obviously you need to gig about with the subform size and add fields as you
see fit.

But that is Form and subform making 101.

Enjoy

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



.