none
Report that lists all items received for each customer RRS feed

  • General discussion

  • 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 <

     


    • Edited by staloggi1 Thursday, October 25, 2018 11:15 PM
    Thursday, October 25, 2018 11:15 PM

All replies

  • 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

    Friday, October 26, 2018 11:13 AM