Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hmmm.



    ??Where does StockLevel come from in this query
    Code:
    SELECT tblProduct.ProductID, tblProduct.Description
    , tblProduct.StockLevel AS Expr21, tblProduct.ReorderLevel
    , [tblProduct]!StockLevel-[tblProduct]!ReorderLevel AS Expr1
    FROM tblProduct;
    I changed Expr1 to Expr21 because of the duplicate outputs.

    What was your initial approach to designing this application?
    Tables and relationships from business description OR
    Forms to approximate/respond to Telephone scenario above.

    Maybe someone else has followed your thread and has answers to your specific issue

  2. #17
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    I just looked and that is an old query that I ended up not using; I have not cleaned up unused objects in the database. Initially thought to have Stock Level in the table, and have a table for Manually updating inventory, however that would be a calculated field in a table which I have learned subsequently is a bad idea.

    My initial approach was to identify tables and relationships (along the lines of E-R diagrams). I looked at the MS Access Northwind database template, and others, however needed something much simpler in some ways, but with features particular to my client (such as multiple shipping address for each customer, multiple pricing for individual products that are not mathematical functions, and ability to input ad hoc products and change pricing on the fly when taking an order). I started building out the Tables, relationships, Forms, and Reports. Then worked in the primary use case (telephone scenario) to make the forms flow in a logical sense for my customer. I have had some assistance from a few folks on the forum along the way to straighten up the relationships and the coding, but still may have design errors.

  3. #18
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Stock level is usually a calculation as per this article by Allen Browne

    I'm not sure if I'm helping or not. I don't follow the flow, but I did make a copy for my own adjusting/testing.
    I have made some changes to remove the lookup at field level and on the Order form.

    I have done this -but it may not be in line with your needs:
    --on ShippingAddress subform
    I do a dropdown and am able to Add a new Shipping Address for the customer on the form.
    Then I can select any of the Shipping Addresses for that Customer, and click address line1.
    The click event adds the selected ShippingAddressId to the combo box on the main form.
    I edited the label on that combo to say Use this ShippingAddress for this Order.
    See attached.
    ShipAddID fkCustID ShipAddLine1 ShipAddLine2 ShipCity ShipStateProv ShipZipPostal ShipCountry Ship_other_info
    1 1 23 Peachtree Lane Box 35 Dayton OH 44444 US
    2 1 86 Alldone Way Peoria IL 78787 US
    7 1059 86 Rosewood Drive PO Box 2347 Memphis TN 24356 US
    8 2 23 Plain Lane San Mateo CA 90121 US
    9 1059 45 Old Road PO Box 34 Little Rock AR 34567 US
    10 1 23 45 Sioux City KS 33333 US
    11 2 1234 Easy Street PO Box 34 New Line NJ 32323 US
    12 1 OrangeOrange 2 OJ Citrus City FL 33462 US

    Click image for larger version. 

Name:	PinkShiptoSub1.PNG 
Views:	34 
Size:	44.3 KB 
ID:	36677

    Click image for larger version. 

Name:	PinkShiptoSub2.PNG 
Views:	34 
Size:	50.6 KB 
ID:	36678


    I also created tblSalePerson since SalesPerson is an entity in the scope of your database and is related to Order.

  4. #19
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Can you post your current version? It appears that the current version might not be the latest version posted here.

  5. #20
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    I did not see an attachment, however am trying to work through your suggestions as presented. I will update shortly.

    Update: would it be possible to see the changes that you made? I have not been successful in creating a code to pass the selected shipping address to the main form. Have tried using an OnClick event in the subform to the fkShipAddrID control on the main form but now have no addresses shoing up in that dropdown.
    Last edited by PinkDuster; 12-22-2018 at 12:39 PM. Reason: Update to earlier post

  6. #21
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #22
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    I cleaned up the table structure as recommended to get rid of look-up fields; making the table relationships explicit. Also replaced the comboBox control with a textBox control for the CustomerID. However, now I can't navigate through the Order Details tab in order to get to the Shipping Address tab to implement the onClick event as described above. Here is the error message I get.Click image for larger version. 

Name:	OrderError.PNG 
Views:	27 
Size:	41.1 KB 
ID:	36729

    Any suggestions? It looks like the Orders are being created in the table, so am not sure why the error has cropped up. Attached is db. v4.3e - Copy - Copy.zip

  8. #23
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you can have several Orders for a Customer and that Customer could direct the Shipment of an Order to a different Shipping Address, it seems to me you need to identify the Shipping Address for that Order. If I'm understanding correctly, Where and How do you tie an Order with that Customer to identify the proper Shipping Address? Maybe I'm missing something basic, but it seems you have to isolate a Customer's Order, then assign the Shipping Address for that Order.

  9. #24
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    v4.3e -davegri-v01 .zip
    Got rid of the error when trying to add records to order subform.
    Fixed load of Orders form from Customer form when trying to view existing orders.
    Shipping address combobox on orders form now shows shipping addresses for the customer. Don't know what it is supposed to do, as there is also a tab on the subform for shipping address.

  10. #25
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Quote Originally Posted by orange View Post
    If you can have several Orders for a Customer and that Customer could direct the Shipment of an Order to a different Shipping Address, it seems to me you need to identify the Shipping Address for that Order. If I'm understanding correctly, Where and How do you tie an Order with that Customer to identify the proper Shipping Address? Maybe I'm missing something basic, but it seems you have to isolate a Customer's Order, then assign the Shipping Address for that Order.
    The Orders table has: OrderID (PK), OrderDate, fkCustID (relates to Customers table), and fkShipAddID (relates to selected Shipping Address), and other order data.

    The Shipping Addresses table has: ShipAddID (PK), fkCustID (relates to Customers table), and fields for address lines, city, etc.

    The Customer table has: CustID (PK), and customer related data.

    I want to show all shipping addresses for a customer associated with the current order in the subform tab; select a shipping address from the subform tab and pass the ShipAddID associated with that address to the fkShippAddID field in the Orders table. So when I look at the Orders table I should have a Customer ID and a ShippingAddID shown for each order. Then I can use the specific address data (Line1, Line2, City, etc.) for that ShippingAddID to fill out the Invoice.

  11. #26
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Thank you very much for the error corrections. The tab on the subform is intended to let the user add in a new shipping address on the fly, in case the customer wants an address different from one used previously. I wasn't sure how to flip between the Customer Form and the Order Form to add a new address, then make sure it updates the dropdown list on the Order Form, so I put the tab in on the Order Form. Is that not a good practice? If it is not, then I think I will need to revise the tabs for the Shipping Addresses and the Payment Information. Thank you for your assistance.

  12. #27
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    v4.3e -davegri-v02 .zip

    Added update query to shipping address combobox after_update to update tblOrders.
    Fixed problem of switching between customer form and order form, if order form was already open a different customer would not display.
    Commented out several lines of code causing compile errors.

  13. #28
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Thank you very much Davegri! This fixed it, and it runs smoother. Thank you also to Orange, and to RanMan256 for all your assistance! Solved.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 07-05-2017, 11:31 AM
  2. Replies: 2
    Last Post: 03-21-2014, 10:40 AM
  3. Replies: 3
    Last Post: 10-09-2012, 11:58 PM
  4. Replies: 1
    Last Post: 12-04-2011, 09:11 PM
  5. Subform Filter based on Main Form Information
    By james.carpenter2 in forum Forms
    Replies: 0
    Last Post: 02-16-2011, 09:55 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums