Report that lists all items received for each customer

Question

I work for a small company that receives, stores, and installs art and furniture for interior designers. We a generic receiving report that we use when clients shipments arrive to us each client can receive multiple items per shipper. I want to create a form that allows you to enter the client name and the names of the shipper and manufacturer as well as some specific data that identifies each delivery. Each item that is received needs to be a new record in the items table. but the customer and shipper and manufacturer need to carry down that list of records as well. this is the form. Each numbered column needs to be a new record in the items table of the DB, how would i go about setting this up as an access form? any help is appreciated!!

I am having trouble uploading an image due to my account not being verified yet. so each item in bold is a blank field for entering text  

Customer Name     Side Mark     Shipper   Manufacturer   <-- these fields should only be typed once

Item 1  <

Item 2 <

                          All of the items should go into a new row but continue to have the above info attached to them.

Item 3 <

Item 4 <

 

Answer 1

As always, the important thing is to get the model right first.  The implication in your post is that all items per shipment are manufactured by the same manufacturer.  Assuming that to be the case the model would, in broad outline, comprise the following tables:

Customers
….CustomerID  (PK)
….Customer
….etc

Manufacturers
….ManufacturerID  (PK)
….Manufacturer
….etc

Shippers
….ShipperID  (PK)
….Shipper
….etc

Shipments represents a ternary many-to-many relationship type between the above tables, so is modelled by a table which resolves the relationship type into three one-to-many relationship types:

CustomerShipments
….CustomerShipmentID  (PK)
….CustomerID  (FK)
….ManufacturerID  (FK)
….ShipperID  (FK)
….ShipmentDate
….ItemNumber
….etc

Items are modelled by table which references CustomerShipments like this:

Items
….ItemID  (PK)
….CustomerShipmentID  (FK)
….Item
….etc

All non-key columns in the above tables must of course be functionally determined solely by the key of each table.

The user interface is a standard one of a CustomerShipments form, in single form view, within which is an Items subform, in continuous forms view, linked to the parent form on CustomerShipmentID.  In the parent form the controls bound to the CustomerID, ManufacturerID and ShipperID.  The subform would have controls bound to the Item column and any other columns representing attributes of the item.

To number the rows inserted in the subform assign a value to the subform's bound ItemNumber control's DefaultValue property in the subform's Current event procedure as follows:

    Dim strCriteria As String
    
    strCriteria = "CustomerShipmentID = " & Nz(Me.Parent.CustomerShipmentID, 0)
    
    Me.ItemNumber.DefaultValue = """" & _
        Nz(DMax("ItemNumber", "Items", strCriteria), 0) + 1 & """"

For reporting purposes a CustomerShipments parent report would be based on a query which joins the CustomerShipments, Customers, Manufacturers and Shippers tables.  Within the parent report would be a subreport based on Items, linked to the parent report on CustomerShipmentID.

---

Ken Sheridan, Stafford, England