Why is my form blank?

A

Aria

Hi,
I don’t want to post such a dumb question but I don’t know what else to do.
I could really use some help. I’m almost finished creating a my db for my
school but a situation that has been an issue all along has taken a turn for
the worse. My db tracks keys and employee info (personal and school related).
I now need to track classroom coverage for urgent/emergency situations (ex.
Teacher A is suddenly ill and needs to leave, who will cover the class?) If I
cannot find a volunteer, an administrator will have to force the issue. This
info will also be used for payroll because staff sometimes makes side deals
without informing the office and then sometimes months later, turns in a
timesheet without enough information.

I added the following tables:

tblClassSchedule
**************
PeriodID
ClassPeriod
TeacherPrep

tblCoverage
***********
PeriodID (FK)*
AbsenteeEmpID (FK to tblEmployees)* *4field PK
CoverageEmpID (FK to tblEmployees)*
CoverageDate *
ReasonID (FK to tblCoverageReasons)
Explanation (if the reason is School/District Business or Work on
Site)

tblCoverageReasons
*****************
ReasonID (PK)
CoverageReason


I based the form on a query. When I switched from design view to form view,
it was blank. Well, the labels were still there but there weren’t any
controls to input information.
SQL:
SELECT tblCoverage.PeriodID, tblCoverage.AbsenteeEmpID,
tblCoverage.CoverageEmpID, tblCoverage.CoverageDate,
tblCoverage.CoverageTime, tblCoverage.Explanation,
tblClassSchedule.ClassPeriod, tblEmployees.EmpID, tblEmployees.Active,
tblEmployees.FirstName, tblEmployees.LastName, tblClassSchedule.TeacherPrep,
tblEmployees.EmployeeType, tblClassifications.ClassID,
tblClassifications.ClassDescription, tblTitlesEmps.TitleID,
tblTitles.TitleDescription, tblCoverageReasons.ReasonID,
tblCoverageReasons.CoverageReason
FROM tblTitles INNER JOIN (((tblClassifications INNER JOIN tblEmployees ON
tblClassifications.ClassID = tblEmployees.ClassID) INNER JOIN
(tblCoverageReasons INNER JOIN (tblClassSchedule INNER JOIN tblCoverage ON
tblClassSchedule.PeriodID = tblCoverage.PeriodID) ON
tblCoverageReasons.ReasonID = tblCoverage.ReasonID) ON (tblEmployees.EmpID =
tblCoverage.CoverageEmpID) AND (tblEmployees.EmpID =
tblCoverage.AbsenteeEmpID)) INNER JOIN tblTitlesEmps ON tblEmployees.EmpID =
tblTitlesEmps.EmpID) ON tblTitles.TitleID = tblTitlesEmps.TitleID
WHERE (((tblEmployees.Active)=True) AND ((tblEmployees.EmployeeType)=1 Or
(tblEmployees.EmployeeType)=2) AND
((tblClassifications.ClassDescription)="Certificated" Or
(tblClassifications.ClassDescription)="Substitute"));

I know my form has issues but it has me questioning other parts too. My
query may pull from too many tables or maybe the structure for this part is
incorrect. I’m sorry this is so long but I wanted to post enough information
so that someone could spot what I do not see and get me back on track.
 
A

Arvin Meyer [MVP]

If you have a query which is not updateable, and returns no records, the
form will be blank. Scary isn't it. There are 2 remedies:

1. If you need data entry, you'll need to fix the query.
2. If you only need to retrieve data, before the form opens, run the query
and check the recordcount in code, then cancel the open if there are no
records.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
J

Jeanette Cunningham

Hi Aria,
a form can be blank because:
1. the query for it does not show any records.
You can test this by making a copy of that query and opening it as a
separate query.

2. there is a filter on the form and no records match the filter.
Remove the filter and try again.

3. it is in data entry mode. Set the Data Entry property to No, and the
Allow Additions to Yes

3. there are issues with form filters that hide all records if you don't set
the Allow Additions to Yes - most of these are fixed in A2007.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
T

tina

you're making a classic "newbie" mistake. you've inner-joined three tables
in a query that will be used as the RecordSource for a data entry form.
you've found that a multi-table query is often not updateable, and for data
entry forms it's also usually not necessary. assuming that the focus of the
form is to enter records in tblCoverage, just set tblCoverage (or a query
based *only* on tblCoverage) as the form's RecordSource. you can bind the
PeriodID field to a combobox control, using tblClassSchedule as the
control's *RowSource* (not ControlSource) - that will give you access to the
values in the class schedule table. ditto this setup for the AbsenteeEmpID
field, CoverageEmpID field, and ReasonID field, in each case using the
appropriate related table as the combobox control's RowSource.

hth
 
A

Aria

Hi Arvin and Jeanette,
<lol> Yes, it *is* scary. I don't have any records yet because I'm trying to
create this form to input test data. I need data entry so how can I fix the
query? Right now, I can't test anything.
 
A

Aria

Hi Tina,
I figured it was the query once I didn't see my form. I then tried it using
the form wizard and while I can see the controls, there are still issues
where I need help.

Here is what I have:
Recordsource--tblCoverage

txtCoverageDate

cboAbsentee Employee
Rowsource =SELECT [LastName] & ", " & [FirstName] AS LastFirst,
[tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [LastName] & ", " & [FirstName],
[tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassDescription];


cboTchrPrep (had an error here with both a control source and a rowsource,
corrected)
Rowsource =SELECT [tblClassSchedule].[PeriodID],
[tblClassSchedule].[TeacherPrep] FROM tblClassSchedule ORDER BY
[tblClassSchedule].[TeacherPrep];


cboCoverage Employee)
Rowsource = SELECT [tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [tblEmployees].[LastName],
[tblEmployees].[FirstName], [tblClassifications].[ClassDescription];

cboClassPeriod (had an error here with both a control source and a
rowsource, corrected)
Rowsource = SELECT [tblClassSchedule].[PeriodID],
[tblClassSchedule].[ClassPeriod] FROM tblClassSchedule ORDER BY
[tblClassSchedule].[ClassPeriod];


cboCoverageReasons
Rowsource = SELECT [tblCoverageReasons].[CoverageReason] FROM
tblCoverageReasons ORDER BY [tblCoverageReasons].[CoverageReason];

TxtExplanation--Explanation for cboCoverageReasons if School/District
Business or Work on site.

txtCoverageTime--I want to make this a combo box. While I understand that
you shouldn't store calculated values in your db, our bell schedule has too
many variables to make it useful.

What's happening now is that when I try to enter info into the controls the
same info is entered into the controls below (this is a continuous form).
Also when I try to close the form I get the following error message, "You
cannot add or change a record because a related record is required in
tblEmployees".
 
T

tina

What's happening now is that when I try to enter info into the controls
the
same info is entered into the controls below (this is a continuous form).
Click to expand...

then i'd say your controls are *unbound* - that is, blank ControlSource. set
each control's ControlSource to the appropriate fieldname from the form's
RecordSource.
for example: cboCoverageEmployee should have the fieldname CoverageEmpID in
its' *ControlSource* property.

and let's take a look at the *RowSource* for cboCoverageEmployee:

cboCoverage Employee)
Rowsource = SELECT [tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [tblEmployees].[LastName],
[tblEmployees].[FirstName], [tblClassifications].[ClassDescription];

i assume that EmpID is the primary key of tblEmployees, and you've already
said that CoverageEmpID is the corresponding foreign key in tblCoverage. so
EmpID must be included in the RowSource above; go ahead and make it the
first field, because it must be the bound column of the RowSource, and the
BoundColumn property of the combobox is already defaulted to 1 (unless you
changed it). so your RowSource would be

Rowsource = SELECT [tblEmployees].[EmpID], [tblEmployees].[LastName],
[tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [tblEmployees].[LastName],
[tblEmployees].[FirstName], [tblClassifications].[ClassDescription];

you don't want the user to see the EmpID, so just set the ColumnWidths
property to "hide" the columns the user doesn't need to see. for instance,
let's say the user only needs to see LastName, FirstName. the ColumnWidths
property would be

0"; 1"; 1"; 0"; 0"; 0"

adjust the 1-inch wide columns to whatever width you need to show the
LastName and FirstName values appropriately.

follow the same logic detailed above, to correctly set the ControlSource
property and RowSource property of each combobox control, and the
ColumnWidths property, too.

hth


Aria said:
Hi Tina,
I figured it was the query once I didn't see my form. I then tried it using
the form wizard and while I can see the controls, there are still issues
where I need help.

Here is what I have:
Recordsource--tblCoverage

txtCoverageDate

cboAbsentee Employee
Rowsource =SELECT [LastName] & ", " & [FirstName] AS LastFirst,
[tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [LastName] & ", " & [FirstName],
[tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassDescription];


cboTchrPrep (had an error here with both a control source and a rowsource,
corrected)
Rowsource =SELECT [tblClassSchedule].[PeriodID],
[tblClassSchedule].[TeacherPrep] FROM tblClassSchedule ORDER BY
[tblClassSchedule].[TeacherPrep];


cboCoverage Employee)
Rowsource = SELECT [tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [tblEmployees].[LastName],
[tblEmployees].[FirstName], [tblClassifications].[ClassDescription];

cboClassPeriod (had an error here with both a control source and a
rowsource, corrected)
Rowsource = SELECT [tblClassSchedule].[PeriodID],
[tblClassSchedule].[ClassPeriod] FROM tblClassSchedule ORDER BY
[tblClassSchedule].[ClassPeriod];


cboCoverageReasons
Rowsource = SELECT [tblCoverageReasons].[CoverageReason] FROM
tblCoverageReasons ORDER BY [tblCoverageReasons].[CoverageReason];

TxtExplanation--Explanation for cboCoverageReasons if School/District
Business or Work on site.

txtCoverageTime--I want to make this a combo box. While I understand that
you shouldn't store calculated values in your db, our bell schedule has too
many variables to make it useful.

What's happening now is that when I try to enter info into the controls the
same info is entered into the controls below (this is a continuous form).
Also when I try to close the form I get the following error message, "You
cannot add or change a record because a related record is required in
tblEmployees".


--
Aria W.


tina said:
you're making a classic "newbie" mistake. you've inner-joined three tables
in a query that will be used as the RecordSource for a data entry form.
you've found that a multi-table query is often not updateable, and for data
entry forms it's also usually not necessary. assuming that the focus of the
form is to enter records in tblCoverage, just set tblCoverage (or a query
based *only* on tblCoverage) as the form's RecordSource. you can bind the
PeriodID field to a combobox control, using tblClassSchedule as the
control's *RowSource* (not ControlSource) - that will give you access to the
values in the class schedule table. ditto this setup for the AbsenteeEmpID
field, CoverageEmpID field, and ReasonID field, in each case using the
appropriate related table as the combobox control's RowSource.

hth


to
do. turn
for class?)
If I issue.
This (tblEmployees.EmpID
= tblEmployees.EmpID
= part
is
Click to expand...
Click to expand...
 
A

Aria

Tina,
What a difference a day makes along with a "reminder" from a helpful source.
I don't know what I was thinking when I took out EmpID as the ControlSource
and didn't think about column widths *at all*. It seems to be working now. I
think that did it. Thanks for your help.
--
Aria W.


tina said:
What's happening now is that when I try to enter info into the controls the
same info is entered into the controls below (this is a continuous form).
Click to expand...

then i'd say your controls are *unbound* - that is, blank ControlSource. set
each control's ControlSource to the appropriate fieldname from the form's
RecordSource.
for example: cboCoverageEmployee should have the fieldname CoverageEmpID in
its' *ControlSource* property.

and let's take a look at the *RowSource* for cboCoverageEmployee:

cboCoverage Employee)
Rowsource = SELECT [tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [tblEmployees].[LastName],
[tblEmployees].[FirstName], [tblClassifications].[ClassDescription];

i assume that EmpID is the primary key of tblEmployees, and you've already
said that CoverageEmpID is the corresponding foreign key in tblCoverage. so
EmpID must be included in the RowSource above; go ahead and make it the
first field, because it must be the bound column of the RowSource, and the
BoundColumn property of the combobox is already defaulted to 1 (unless you
changed it). so your RowSource would be

Rowsource = SELECT [tblEmployees].[EmpID], [tblEmployees].[LastName],
[tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [tblEmployees].[LastName],
[tblEmployees].[FirstName], [tblClassifications].[ClassDescription];

you don't want the user to see the EmpID, so just set the ColumnWidths
property to "hide" the columns the user doesn't need to see. for instance,
let's say the user only needs to see LastName, FirstName. the ColumnWidths
property would be

0"; 1"; 1"; 0"; 0"; 0"

adjust the 1-inch wide columns to whatever width you need to show the
LastName and FirstName values appropriately.

follow the same logic detailed above, to correctly set the ControlSource
property and RowSource property of each combobox control, and the
ColumnWidths property, too.

hth


Aria said:
Hi Tina,
I figured it was the query once I didn't see my form. I then tried it using
the form wizard and while I can see the controls, there are still issues
where I need help.

Here is what I have:
Recordsource--tblCoverage

txtCoverageDate

cboAbsentee Employee
Rowsource =SELECT [LastName] & ", " & [FirstName] AS LastFirst,
[tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [LastName] & ", " & [FirstName],
[tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassDescription];


cboTchrPrep (had an error here with both a control source and a rowsource,
corrected)
Rowsource =SELECT [tblClassSchedule].[PeriodID],
[tblClassSchedule].[TeacherPrep] FROM tblClassSchedule ORDER BY
[tblClassSchedule].[TeacherPrep];


cboCoverage Employee)
Rowsource = SELECT [tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [tblEmployees].[LastName],
[tblEmployees].[FirstName], [tblClassifications].[ClassDescription];

cboClassPeriod (had an error here with both a control source and a
rowsource, corrected)
Rowsource = SELECT [tblClassSchedule].[PeriodID],
[tblClassSchedule].[ClassPeriod] FROM tblClassSchedule ORDER BY
[tblClassSchedule].[ClassPeriod];


cboCoverageReasons
Rowsource = SELECT [tblCoverageReasons].[CoverageReason] FROM
tblCoverageReasons ORDER BY [tblCoverageReasons].[CoverageReason];

TxtExplanation--Explanation for cboCoverageReasons if School/District
Business or Work on site.

txtCoverageTime--I want to make this a combo box. While I understand that
you shouldn't store calculated values in your db, our bell schedule has too
many variables to make it useful.

What's happening now is that when I try to enter info into the controls the
same info is entered into the controls below (this is a continuous form).
Also when I try to close the form I get the following error message, "You
cannot add or change a record because a related record is required in
tblEmployees".


--
Aria W.


tina said:
you're making a classic "newbie" mistake. you've inner-joined three tables
in a query that will be used as the RecordSource for a data entry form.
you've found that a multi-table query is often not updateable, and for data
entry forms it's also usually not necessary. assuming that the focus of the
form is to enter records in tblCoverage, just set tblCoverage (or a query
based *only* on tblCoverage) as the form's RecordSource. you can bind the
PeriodID field to a combobox control, using tblClassSchedule as the
control's *RowSource* (not ControlSource) - that will give you access to the
values in the class schedule table. ditto this setup for the AbsenteeEmpID
field, CoverageEmpID field, and ReasonID field, in each case using the
appropriate related table as the combobox control's RowSource.

hth


Hi,
I don't want to post such a dumb question but I don't know what else to
do.
I could really use some help. I'm almost finished creating a my db for my
school but a situation that has been an issue all along has taken a turn
for
the worse. My db tracks keys and employee info (personal and school
related).
I now need to track classroom coverage for urgent/emergency situations
(ex.
Teacher A is suddenly ill and needs to leave, who will cover the class?)
If I
cannot find a volunteer, an administrator will have to force the issue.
This
info will also be used for payroll because staff sometimes makes side
deals
without informing the office and then sometimes months later, turns in a
timesheet without enough information.

I added the following tables:

tblClassSchedule
**************
PeriodID
ClassPeriod
TeacherPrep

tblCoverage
***********
PeriodID (FK)*
AbsenteeEmpID (FK to tblEmployees)* *4field PK
CoverageEmpID (FK to tblEmployees)*
CoverageDate *
ReasonID (FK to tblCoverageReasons)
Explanation (if the reason is School/District Business or Work on
Site)

tblCoverageReasons
*****************
ReasonID (PK)
CoverageReason


I based the form on a query. When I switched from design view to form
view,
it was blank. Well, the labels were still there but there weren't any
controls to input information.
SQL:
SELECT tblCoverage.PeriodID, tblCoverage.AbsenteeEmpID,
tblCoverage.CoverageEmpID, tblCoverage.CoverageDate,
tblCoverage.CoverageTime, tblCoverage.Explanation,
tblClassSchedule.ClassPeriod, tblEmployees.EmpID, tblEmployees.Active,
tblEmployees.FirstName, tblEmployees.LastName,
tblClassSchedule.TeacherPrep,
tblEmployees.EmployeeType, tblClassifications.ClassID,
tblClassifications.ClassDescription, tblTitlesEmps.TitleID,
tblTitles.TitleDescription, tblCoverageReasons.ReasonID,
tblCoverageReasons.CoverageReason
FROM tblTitles INNER JOIN (((tblClassifications INNER JOIN tblEmployees ON
tblClassifications.ClassID = tblEmployees.ClassID) INNER JOIN
(tblCoverageReasons INNER JOIN (tblClassSchedule INNER JOIN tblCoverage ON
tblClassSchedule.PeriodID = tblCoverage.PeriodID) ON
tblCoverageReasons.ReasonID = tblCoverage.ReasonID) ON (tblEmployees.EmpID
=
tblCoverage.CoverageEmpID) AND (tblEmployees.EmpID =
tblCoverage.AbsenteeEmpID)) INNER JOIN tblTitlesEmps ON tblEmployees.EmpID
=
tblTitlesEmps.EmpID) ON tblTitles.TitleID = tblTitlesEmps.TitleID
WHERE (((tblEmployees.Active)=True) AND ((tblEmployees.EmployeeType)=1 Or
(tblEmployees.EmployeeType)=2) AND
((tblClassifications.ClassDescription)="Certificated" Or
(tblClassifications.ClassDescription)="Substitute"));

I know my form has issues but it has me questioning other parts too. My
query may pull from too many tables or maybe the structure for this part
is
incorrect. I'm sorry this is so long but I wanted to post enough
information
so that someone could spot what I do not see and get me back on track.
Click to expand...
Click to expand...
Click to expand...
 
T

tina

yeah, sometimes just walking away from a problem for a couple hours or even
days makes all the difference. and you're welcome :)


Aria said:
Tina,
What a difference a day makes along with a "reminder" from a helpful source.
I don't know what I was thinking when I took out EmpID as the ControlSource
and didn't think about column widths *at all*. It seems to be working now. I
think that did it. Thanks for your help.
--
Aria W.


tina said:
What's happening now is that when I try to enter info into the
Click to expand...
controls
the
same info is entered into the controls below (this is a continuous
Click to expand...
form).

then i'd say your controls are *unbound* - that is, blank ControlSource. set
each control's ControlSource to the appropriate fieldname from the form's
RecordSource.
for example: cboCoverageEmployee should have the fieldname CoverageEmpID in
its' *ControlSource* property.

and let's take a look at the *RowSource* for cboCoverageEmployee:

cboCoverage Employee)
Rowsource = SELECT [tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [tblEmployees].[LastName],
[tblEmployees].[FirstName], [tblClassifications].[ClassDescription];

i assume that EmpID is the primary key of tblEmployees, and you've already
said that CoverageEmpID is the corresponding foreign key in tblCoverage. so
EmpID must be included in the RowSource above; go ahead and make it the
first field, because it must be the bound column of the RowSource, and the
BoundColumn property of the combobox is already defaulted to 1 (unless you
changed it). so your RowSource would be

Rowsource = SELECT [tblEmployees].[EmpID], [tblEmployees].[LastName],
[tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [tblEmployees].[LastName],
[tblEmployees].[FirstName], [tblClassifications].[ClassDescription];

you don't want the user to see the EmpID, so just set the ColumnWidths
property to "hide" the columns the user doesn't need to see. for instance,
let's say the user only needs to see LastName, FirstName. the ColumnWidths
property would be

0"; 1"; 1"; 0"; 0"; 0"

adjust the 1-inch wide columns to whatever width you need to show the
LastName and FirstName values appropriately.

follow the same logic detailed above, to correctly set the ControlSource
property and RowSource property of each combobox control, and the
ColumnWidths property, too.

hth


Aria said:
Hi Tina,
I figured it was the query once I didn't see my form. I then tried it using
the form wizard and while I can see the controls, there are still issues
where I need help.

Here is what I have:
Recordsource--tblCoverage

txtCoverageDate

cboAbsentee Employee
Rowsource =SELECT [LastName] & ", " & [FirstName] AS LastFirst,
[tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [LastName] & ", " & [FirstName],
[tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassDescription];


cboTchrPrep (had an error here with both a control source and a rowsource,
corrected)
Rowsource =SELECT [tblClassSchedule].[PeriodID],
[tblClassSchedule].[TeacherPrep] FROM tblClassSchedule ORDER BY
[tblClassSchedule].[TeacherPrep];


cboCoverage Employee)
Rowsource = SELECT [tblEmployees].[LastName], [tblEmployees].[FirstName],
[tblClassifications].[ClassID], [tblClassifications].[ClassDescription],
[tblEmployees].[Active] FROM tblClassifications INNER JOIN tblEmployees ON
[tblClassifications].[ClassID]=[tblEmployees].[ClassID] WHERE
((([tblClassifications].[ClassDescription])="Certificated" Or
([tblClassifications].[ClassDescription])="Classified" Or
([tblClassifications].[ClassDescription])="Substitute") And
(([tblEmployees].[Active])=True)) ORDER BY [tblEmployees].[LastName],
[tblEmployees].[FirstName], [tblClassifications].[ClassDescription];

cboClassPeriod (had an error here with both a control source and a
rowsource, corrected)
Rowsource = SELECT [tblClassSchedule].[PeriodID],
[tblClassSchedule].[ClassPeriod] FROM tblClassSchedule ORDER BY
[tblClassSchedule].[ClassPeriod];


cboCoverageReasons
Rowsource = SELECT [tblCoverageReasons].[CoverageReason] FROM
tblCoverageReasons ORDER BY [tblCoverageReasons].[CoverageReason];

TxtExplanation--Explanation for cboCoverageReasons if School/District
Business or Work on site.

txtCoverageTime--I want to make this a combo box. While I understand that
you shouldn't store calculated values in your db, our bell schedule
Click to expand...
has
too
many variables to make it useful.

What's happening now is that when I try to enter info into the
Click to expand...
controls
the
same info is entered into the controls below (this is a continuous form).
Also when I try to close the form I get the following error message, "You
cannot add or change a record because a related record is required in
tblEmployees".


--
Aria W.


:

you're making a classic "newbie" mistake. you've inner-joined three tables
in a query that will be used as the RecordSource for a data entry form.
you've found that a multi-table query is often not updateable, and
Click to expand...
for
data
entry forms it's also usually not necessary. assuming that the focus
Click to expand...
of
the
form is to enter records in tblCoverage, just set tblCoverage (or a query
based *only* on tblCoverage) as the form's RecordSource. you can
Click to expand...
bind
the
PeriodID field to a combobox control, using tblClassSchedule as the
control's *RowSource* (not ControlSource) - that will give you
Click to expand...
access to
the
values in the class schedule table. ditto this setup for the AbsenteeEmpID
field, CoverageEmpID field, and ReasonID field, in each case using the
appropriate related table as the combobox control's RowSource.

hth


Hi,
I don't want to post such a dumb question but I don't know what
Click to expand...
else
to
do.
I could really use some help. I'm almost finished creating a my db
Click to expand...
for
my
school but a situation that has been an issue all along has taken
Click to expand...
a
turn
for
the worse. My db tracks keys and employee info (personal and school
related).
I now need to track classroom coverage for urgent/emergency situations
(ex.
Teacher A is suddenly ill and needs to leave, who will cover the class?)
If I
cannot find a volunteer, an administrator will have to force the issue.
This
info will also be used for payroll because staff sometimes makes side
deals
without informing the office and then sometimes months later,
Click to expand...
turns in
a
timesheet without enough information.

I added the following tables:

tblClassSchedule
**************
PeriodID
ClassPeriod
TeacherPrep

tblCoverage
***********
PeriodID (FK)*
AbsenteeEmpID (FK to tblEmployees)* *4field PK
CoverageEmpID (FK to tblEmployees)*
CoverageDate *
ReasonID (FK to tblCoverageReasons)
Explanation (if the reason is School/District Business or Work on
Site)

tblCoverageReasons
*****************
ReasonID (PK)
CoverageReason


I based the form on a query. When I switched from design view to form
view,
it was blank. Well, the labels were still there but there weren't any
controls to input information.
SQL:
SELECT tblCoverage.PeriodID, tblCoverage.AbsenteeEmpID,
tblCoverage.CoverageEmpID, tblCoverage.CoverageDate,
tblCoverage.CoverageTime, tblCoverage.Explanation,
tblClassSchedule.ClassPeriod, tblEmployees.EmpID, tblEmployees.Active,
tblEmployees.FirstName, tblEmployees.LastName,
tblClassSchedule.TeacherPrep,
tblEmployees.EmployeeType, tblClassifications.ClassID,
tblClassifications.ClassDescription, tblTitlesEmps.TitleID,
tblTitles.TitleDescription, tblCoverageReasons.ReasonID,
tblCoverageReasons.CoverageReason
FROM tblTitles INNER JOIN (((tblClassifications INNER JOIN tblEmployees ON
tblClassifications.ClassID = tblEmployees.ClassID) INNER JOIN
(tblCoverageReasons INNER JOIN (tblClassSchedule INNER JOIN tblCoverage ON
tblClassSchedule.PeriodID = tblCoverage.PeriodID) ON
tblCoverageReasons.ReasonID = tblCoverage.ReasonID) ON (tblEmployees.EmpID
=
tblCoverage.CoverageEmpID) AND (tblEmployees.EmpID =
tblCoverage.AbsenteeEmpID)) INNER JOIN tblTitlesEmps ON tblEmployees.EmpID
=
tblTitlesEmps.EmpID) ON tblTitles.TitleID = tblTitlesEmps.TitleID
WHERE (((tblEmployees.Active)=True) AND
Click to expand...
((tblEmployees.EmployeeType)=1
Or
(tblEmployees.EmployeeType)=2) AND
((tblClassifications.ClassDescription)="Certificated" Or
(tblClassifications.ClassDescription)="Substitute"));

I know my form has issues but it has me questioning other parts
Click to expand...
too.
My
query may pull from too many tables or maybe the structure for
Click to expand...
this
part
is
incorrect. I'm sorry this is so long but I wanted to post enough
information
so that someone could spot what I do not see and get me back on track.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Repeating Data 4

Top