Subform - create list of records

[colonial]

I was wondering if what I'm trying to do in Access XP and 2003 is possible. I've looked at countless templates and samples and havn't seen anything like what I want to do to be able to construct it. I've tried manipulating queries, relationships different table joins but to no avail. I'm only a beginner in VBA and know little SQL, I would be truly gratefull if someone can please shed some light on the below.



I've been creating a preventitive fleet maintenance database in access and want to make some changes that will make the database more flexible.

What I have is a main form which has the following main fields from the tblWorkOrder; WorkOrderID, FleetID, StartDate, FinishDate, Odometer, and ServiceTypeID.

Within this I have a subform in datasheet view (tblServiceItems, fields; ServiceItemID, ServiceDescription, ServiceCompleted(checkbox)) and this lists all the service items that belong to the ServiceTypeID in the main form.



I have another table called tblServiceDetails and this has the fields WorkOrderID, ServiceItemID which join the above two tables.



My problem is that I can't get the subform to list all the records that are in the table tblServiceItems, it only shows each record if you go through and select it manually. What I want it to do is to show like a listbox and allow me to go through the list and check of those service items once they have been completed. On top of this I want all those service items for that service type to be recorded against that workorder (regardless of completed or not) so then when I create a report on a WorkOrder It will list all the service items showing the checkbox's of which services have been done.



I've tried to do this with a listbox, but It doesn't show the checkbox, only yes or no. I've also tried using the tblServiceItems as the subform but this only updated the table and didn't create a record in the table tblServiceDetails matching it with the workorder.

Any idea's please, there must be something simple that I'm missing surely.

[MMcCarthy]

Set the record source of the subform to ....

SELECT tblServiceDetails.WorkOrderID, tblServiceItems.ServiceItemID, ServiceDescription, ServiceCompleted
FROM tblServiceDetails INNER JOIN tblServiceItems
ON tblServiceDetails.ServiceItemID = tblServiceItems.ServiceItemID
 

Also check the relationship between tblServiceDetails and tblServiceItems. Open the relationship window and double click on the relationship. When the properties window opens make sure the Referential Integrity, Cascade Update and Cascade Delete fields are all ticked.

[colonial]

Thankyou for reply.
I've checked the relationships and they appear fine, and checked the properties and made them as you suggested. I also tried the SQL code in the record source, however this doesn't seem to overcome my problem of the subform not listing all the service item records (which are in the table tblServiceItems), It allows you to add each record individually (bit like data entry) but that creates problems in itself by accidently selecting the same ServiceItemsID twice, or the user may miss a one of the service items.

Below is an example of my table and query structure, this may make it more clear.

TABLES

tblServiceItems
ServiceItemsID (Primary key)
ServiceItemDescription
ServiceTypeID
ServiceItemStatus

tblServiceDetails
WOID (Primary key)
ServiceItemsID (Primary key)
ServiceCompleted (checkbox)

tblServiceType
ServiceTypeID (Primary key)
ServiceTypeCode
ServiceTypeDescription

tblWorkOrder
WOID (Primary key)
FleetID
ServiceTypeID
WOStartDate
WOFinishDate, etc


QUERIES

qryWorkOrder (used in the frmWorkOrder)
This query has the tables; tblWorkOrder and tblServiceDetails
Relationship is one to many, joined by the WOID with a join property # 2 (wont work any other #).
All fields from the tblWorkOrder have been selected.

qryWorkOrderSub (used in the frmWorkOrderSub - datasheet view)
This query has the tables; tblServiceItems and tblServiceDetails.
Relationship is one to many, joined by the ServiceItemId with join property #1.
Selected fields from these tables are;
All fields from the tblServiceDetails and only, fields ServiceDescription and ServiceTypeID from the tblServiceItems.


thankyou

[colonial]

Would anyone have some more ideas I could try please.

This is a link to what my relationships, table, queries and form looks like if this helps.
view images

[Scott Price]

I'm not an expert, but I'm wondering if you should restructure your table/relationships to begin with. Just an idea, and could be off target. Something looks strange, though, with your db relationships window.

If you remove the relationship between tblServiceItems and TblWorkOrder, and instead link the two through the tblServiceDetails (adding a servicetypeId field in that table and removing it from TblWorkOrder), it should simplify the task of implementing your db design.

Looks like you might need to restructure your queries and maybe forms also.

Another suggestion is to remove the WOID field from your subform and underlying query, MaintenanceDetails. It looks to me like your query is filtering based on the WOID # and as it only finds one, it only returns one record? You shouldn't need the WOID on that subform, as the workorder table and the serviceitems/types tables will be linked through your tblServiceDetails.

You asked for ideas! Hope these make sense and help.