I'm working on building a database that can be used for inspecting raw materials and components against specified inspection criteria. Everything has been working smoothly so far and I feel like I have the tables and relationships set up exactly how I want them (see attachment).

At the moment, I'm stuck on getting my ReceiptInsp form to function how I want it to. What I want it to do is to display all the criteria from InspCriteriaMaster for a given item number and allow me to input QtyAccept for each. Where I'm struggling is how to link QtyAccept back to ReceiptInsp through ReceiptNumber as well as back to ItemCriteriaMaster through CriteriaNumber. I've tried doing it with a subreport for all the criteria and a subform for qtyaccept, along with some very mediocre coding, but I'm just not happy with it and I feel like there's probably a more elegant way of doing it. Any thoughts?

Can a criteria item apply to many items (but just with a different criteria value)? For example, if you are inspecting raw materials, you would want the weight, couldn't "weight" be a criteria item that can apply to many materials? The actual criteria value would vary from one item to another i.e. item A might have a weight of 50 kg while item B might have a weight of 100 kg.

I see what you're saying, but this typically doesn't happen. I'd say almost all of the criteria are specific to the item because they call out specific dimensions on the respective drawing.

The name of the dimension can be the same: length, width, ID, OD but the values would be different from one part to another.

What I am suggesting is to have a table that holds the various attributes such as length, width etc.

In your itemcriteriamaster table you only have 3 fields; I would propose the following

ItemCriteriaMaster
-CriteriaNumber autonumber, primary key
-ItemNumber foreign key to ItemMaster table
-fkAttributeID foreign key to tblCriteriaAttribute
-criteria (the actual value for the attribute item)

tblAttribute (a record for each attribute that you might capture for a part or material)
-pkAttributeID primary key, autonumber
-txtAttribute


What I want it to do is to display all the criteria from InspCriteriaMaster for a given item number and allow me to input QtyAccept for each.

In order to populate InspToCriteria, you will have to run an append query that pulls the applicable criterianumbers for the item in question from what you have predefined for each part in ItemCriteriaMaster and append those criterianumbers to InspToCriteria. You will have to execute the query each time a new inspection record is created in ReceiptInsp, so I would highly recommend an automated procedure.

Ahh the append query, genious! This is the first time I'm using it so it'll take some playing around but from some initial testing it seems like this is exactly what I was looking for. Thanks jzwp22!

As for the attribute table, that doesn't sound like such a bad idea. It'd be great to have the criteria organized that way for data tracking purposes so I'll definitely keep that in mind.

You're welcome. Let us know if you are successful.

Click here to find information Restaurant POS SYSTEMS at Retail pos systems. Our restaurant point of sale pos systems provide more than the typical restaurant POS restaurant pos system Complete low cost software solutions. visit










Point of Sale

I've got my form and subform working great!

The update query returns receiptnumber and criterianumber where receiptnumber and itemnumber are equal to the main form values. Then I wrote some vba code on the main form's 'on current' event to open the update query only if there are no records in the subform. I did this so criterianumber records don't keep being added when browsing through other records. As far as I can tell, the update query is working perfectly!

Many thanks for the help, it's much appreciated!

You did mean an APPEND query (not an UPDATE query), correct?

Then I wrote some vba code on the main form's 'on current' event to open the update query only if there are no records in the subform. I did this so criterianumber records don't keep being added when browsing through other records.


I would think that you can just run the append query in the after insert event of the form that you use for adding new records to ReceiptInsp. There would be no new records added if you were just browsing existing records.

Ah yea sorry typo.. I meant to say append query.