Help with SubForms

[Guest]

I am a very new user and am attempting to write my first database from
scratch. Here is the problem I am having. I am attempting to setup a database
to track daily stats and also equipment usage. I have six tables, they are:

Boat# --- Table
PK – Boat# (This has 1 field and shows the boat #'s we currently use)

Boats --- Table
BoatID
Boat#
EngHourStart
EngHourEnd

Employees --- Table
PK – Employee#
LastName
FirstName

DailyLog --- Table
PK – ID
Date
ReportID (I set a relationship up as One to Many with
Reports Table)
BoatID (I set a relationship up as One to Many with Boats
Table)
CheckListID (I set a relationship up as One to Many with
VesselCheckList Table)
1st Employee# (This does a look up from Employee and gets info)
2st Employee# (This does a look up from Employee and gets info)
3st Employee# (This does a look up from Employee and gets info)
4st Employee# (This does a look up from Employee and gets info)

Reports --- Table
PK - ReportID
DepartmentReport#
TypeOfReport

VesselChecklist --- Table
CheckListID
CommentsField
Tow Line, Tools etc….(I have several check boxes for items
to be inventoried prior to taking boat)

PK = Primary Key

I have created a Form called DailyLog, which will use every field above. The
problem I am having is when I attempted to create a SubForm in the DailyLog.
I named it ReportSub and it is using the Reports table as it's source. I am
using DepartmentReport# and TypeOfReport from that table. When I click on the
SubForm I get the following Error, "The Microsoft Jet Database Engine cannot
find a record in the table "VesselCheckList" with the key matching filed(s)
"DailyLog.CheckListID". I have attempted to search through the forums and
knowledge base, but unable to find a solution. I think it is more that I am
not sure where to look. Can anyone point me in the right direction. Thanks

 

[Larry Linson]

First, you should not use Reports as a Table Name -- it is an Access
Reserved word referring to a Collection of open Reports in the Database and
using it for another purpose can be confusing to the developer if not to
Access.

Second, I see no need for a separate Boat# Table. I _infer_ (but it is only
a guess) that there may be boats in the Boats Table that are currently NOT
used? A Yes/No field in the Boats Table, if so, could replace the Boat#
Table and simplify the structure of your database.

Third, you need to clarify about "reports". You get the message when you
click on the Subform because that is one of the conditions under which
Access automatically saves the Record in the Parent Form. Most likely, no
Checklist ID has yet been set.

Fourth, it appears your Daily Log Table is unnormalized by having specific
Fields for four employees. In fact, your daily log looks suspiciously as if
you are creating a table to use as RecordSource for an Access Report, when
you could use a Query that brings together the other Records.

Fifth, you've listed the tables and data, but it's not clear to me what you
are trying to _accomplish_ (my initial guess is that the structure may be
more complex than it has to be to do what you need done).

Larry Linson
Microsoft Access MVP