Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65

    Select Shipping Address from Subform and Add Identifying Information to Main Form (Order Form)


    I have set up my sales database with an order form as a main form, and three tabbed subforms within it. Two of the subforms are children of the Customer table; one being Shipping Addresses. Many customers have multiple shipping addresses and only one billing address. For any order, I would like to select one of the shipping addresses and have the identifying info (ShipAddID) added to the current record in the Order table (fkShipAddID). The Shipping Address table has two identifying keys, one for the Customer ID and the other for the Shipping Address ID.

    I created a button to do this when the appropriate shipping address row is selected, but not sure of how to write the code to tell the order record to use the particular address. Is this a VBA code or a Macro?

    Thank you for any advice. DB attached. v4.3e - Copy.zip

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Youd have a comb box that would fill with the shipToIDs ,onto the Main Order record,
    ponce user picks the customerID.

    the query for this combo would look at the ClientID on the form.

  3. #3
    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 ranman256 View Post
    Youd have a comb box that would fill with the shipToIDs ,onto the Main Order record,
    ponce user picks the customerID.

    the query for this combo would look at the ClientID on the form.
    I've almost got this, I created the combo box however I get all of the shipping addresses in the ShipAdd table instead of filtering out only those based on the CustID that is selected on the form.
    Attached Files Attached Files

  4. #4
    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
    Why is there no Primary Key on the Customer table?

  5. #5
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    In tblCustomer there is CustID as the primary key. On the Order form I use a lookup that substitutes the name, but it is based on the primary key.

  6. #6
    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
    There is no primary key if you look at the table design of Customer.
    I would advise that you not use Lookup fields. Make a real Lookup table.

    Click image for larger version. 

Name:	CustNoPK.PNG 
Views:	48 
Size:	22.5 KB 
ID:	36618

  7. #7
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Well, what you show is true. The intent was to make it the PK though and I have fixed. Does that help with my follow up question?

  8. #8
    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

  9. #9
    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 Orange, attached is the updated database. On frmOrders there are 3 tabbed subforms. The products detail is a child to tblOrders, and the Shipping Address form is a child to tblCustomer. On the Orders form there is a cbo control for selecting a shipping address to use for the current order. The dropdown list shows all possible shipping addresses, but I want to restrict it to only those for the customer shown.v4.3e - Copy.zip

  10. #10
    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
    My first thought is that the issue is related to the lookup for Custid in the tblOrder. I avoid field lookups totally.
    However, I did adjust the lkupOrderCustId by setting the widths to 1;1 from 0;1. I then just added the field as a control on the form ( I highlighted it in Yellow). That seems to have constrained the Shipping Address options to those of the Customer in question "Crane".

    Part of why I don't use field level lookups is because it hides things, and it is only a recent tweak and limited to Access.

    If this satisfies your issue, then go with it. But I would remove the field lookups if you are planning any adjustments/new features.
    Click image for larger version. 

Name:	PinkDustCustId.PNG 
Views:	43 
Size:	45.6 KB 
ID:	36639

    I also adjusted your relationships to show all fields and reduce crossed lines.

    Click image for larger version. 

Name:	PinkDusterModel.PNG 
Views:	43 
Size:	59.6 KB 
ID:	36641
    Last edited by orange; 12-18-2018 at 03:03 PM.

  11. #11
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Hello Orange, I did make changes as you suggested, however I still have a problem limiting the dropdown list to the addresses only related to the current customer. Attached is screenshot showing the dropdown. My intent is to use the Addresses subform to add new addresses for each customer as needed, and then to use the dropdown (filtered by the current customer) to add the selected address to that particular order in the Orders table and save it.
    Attached Thumbnails Attached Thumbnails List.png  

  12. #12
    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
    Perhaps we are mis-communicating??
    I took your DB and added a new ShippingAddress for CustID 1 (Crane). [OrangeOrange, 2 OJ, Citrus City...]
    When I open the Order form, select Crane, and then go to Shipping Addr tab,
    I get a list of ShippingAddrs only for CustID 1 (Crane).
    Click image for larger version. 

Name:	PinkDustOrange.PNG 
Views:	45 
Size:	43.9 KB 
ID:	36654


    What am I missing?
    As I mentioned, I recommend no use of lookups at the table field level.

    I don't understand the dropdown for Shipping Addr in the tab and its relation to the Choose Shipping Addr in the parent form.

  13. #13
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Ahh, I see where I wasn't clear in describing the scenario. The Order form (main form) populates with a customer. The three subforms are either children of the Order table (Tab 1: Order Details) or of the Customer table (Tab 2: Shipping Addresses & Tab 3: Payment Details). The Shipping Addresses tab does populate with the Shipping Addresses associated with the Customer, however I want to only pick one of those addresses to use on a given Order.

    Initially I thought to use a button control within the Shipping Addresses subform to select the address and add the address identifier (ShipAddID) to the Order table. However I couldn't figure out how to do that. Then I went with a control on the main Order form "Choose Shipping Address" which I want to filter only on Addresses associated with the current customer on the form. When the "Complete Order" button is clicked, then the appropriate shipping address would become part of the record for that order, and I can use it to create an Invoice and/or Shipping Label. Thank you for any advice.

  14. #14
    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
    This model - Customers and Orders- from Barry Williams' site may be helpful.
    In my view he is missing a Table AddressTypes, although he does reference Address_type_cd
    in his Customer_Addresses table. I would also suggest the ShippingAddress could be
    included on the Order - in may be needed for historic records or audit.
    I have worked with systems with address types along this:

    PhysicalLocationAddress
    MailingAddress
    BillingAddress
    ShipToAddress
    AlternateAddress

  15. #15
    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
    This model - Customers and Orders- from Barry Williams' site may be helpful.
    In my view he is missing a Table AddressTypes, although he does reference Address_type_cd
    in his Customer_Addresses table. I would also suggest the ShippingAddress could be
    included on the Order - in may be needed for historic records or audit.
    I have worked with systems with address types along this:

    PhysicalLocationAddress
    MailingAddress
    BillingAddress
    ShipToAddress
    AlternateAddress
    Hello Orange, yes! That was a model I looked at when developing this database, it was very helpful, however while it allowed for multiple types of addresses: Billing, Delivery, Residence it did not allow for multiple addresses within a type which is the case that I have. Below is a description of my project:
    This db application has three primary functions:
    1) Track inventoried products to allow timely re-ordering
    2) Capture sales and return transactions for a variety of customers both for Inventory products and ad hoc products
    3) Produce sales reports for standard and ad hoc time frames

    The typical scenario is for the db User to:


    1. Take a customer telephone call
    2. Search the db quickly (by zip code) to see if the customer is in the db
    3. If not in db, then add
    4. Once customer has been found in db or added to db, then proceed to Create Order
    5. Order Subform Tab1: Search and add Inventory Products, quantities, Price Types (Retail, Wholesale, or Shop); add any ad hoc products and ad hoc pricing
    6. Order Subform Tab2: Get Ship To information from Customer (many customers have 1 or more Ship To addresses that are different from a single Billing Address). Ideally the first Ship To address would be the Billing Address associated with the customer, and subsequent Ship To addresses would be added as needed. Select from the addresses and add the address fields (or the Customer-ShipTo Address ID to the Order table).
    7. Get Payment information from Customer. Our client accepts: Cash, Check, Credit Cards, PayPal, Extend credit for 20 days, or C.O.D.
      1. If the Customer chooses to pay by credit card, I would like to have the option to use the Last Credit Card used or to add new credit card information as needed. We need to store that information in order to use it to run batch transactions at the end of the day.

    8. Complete Order, which automatically (a) produces a Sales Receipt or Invoice and (b) decrements the Inventory Products appropriately.
      1. Right now I have a single sales receipt/invoice form, but it needs to be customized based on whether the customer is: Retail, Wholesale, or a special form is invoked during the Order transaction.


    I may have gone down a bad road with using a lookup field for the customer on the Order form. Since every order starts with finding the customer on the Customers form, maybe I can just pass the customer to the Order form without using a lookup. Am just not sure how to do that. Thank you and the other folks on this forum for your valuable advice and pointers.

Page 1 of 2 12 LastLast
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