Results 1 to 5 of 5
  1. #1
    JJHeck is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Michigan
    Posts
    3

    Bind Combo Box on Order Form

    Hello Everyone!

    I'm very new to MS Access. I work for a charity organization. We offer free items to our clients. We have what are called "Personal Items" and "Community Closet Items". I am attempting to build a database that can track all of these give away's. I have created an order form. On this order form I would like to be able to add to the order record. When I attempt to do so I get the error "Field cannot be updated". I believe this is because the field is not bound correctly but unfortunately I have not been able to figure out how to bind it properly. I have been able to make this field a combo box with only the options from the item list table however when selecting from the combo box I get the same error.



    For the end result I would like to have a combo box on the subforms of the order form that has a list of items to select from (this list will originate from the item list table and show the name of the item not the primary key or item ID). Once an item is selected a quantity can be entered and this will add to the current client order record.

    Here are my relationships:
    Click image for larger version. 

Name:	Relationships.PNG 
Views:	24 
Size:	36.6 KB 
ID:	33839

    Here is what my order form looks like currently (I have reverted back from the combo box for now):
    Click image for larger version. 

Name:	Order Form.PNG 
Views:	24 
Size:	35.3 KB 
ID:	33840

    The orders for the customer shown at the top show up on the left. When selecting a date on the left the order details show up on the right. This order form has 3 subforms. The main form is called "Order" the sub forms are "Shopping", "Personal Orders Subform", and "Community Closet Orders Subform".

    Here is a link to the entire database:
    Click Here!

    Thank you in advance. I would be happy to supply any additional information needed.

    Justin

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I would store the two types of item in one list and identify the type in a new field.
    If you ever add another type if item you simply add it a third or five or tenth "type"

    Then you only need one subform and it's still easy to only list the items based on their "type"

    Edit: In fact you don't need the "middle " tables at all - store the type along with the product IDs and you remove the complication completely.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    JJHeck is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Michigan
    Posts
    3
    Hi Minty,

    Thank you for your reply and suggestion. We have quite a few reasons I wont get into as to why we separate the orders. I did attempt your suggestion anyway. Not only will this not work for our organization but it also left me with the original problem I had with the combo box and entering orders.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I finally got it to work, but I had to modify a lot of your dB. You have spaces in all object names... not a good idea.
    While removing the spaces in names, I renamed a few fields to add a PK/FK suffix so I could tell what fields I was working with.

    Object naming:
    You have a table named "Shopping", but the PK field is named "OrderID" (or it was - I renamed it OrderID_PK) . I would have named the table "tblOrders".


    You had created non-updatable queries for 2 of the sub forms. (CC and PI). The record source for the form should be only the appropriate table/query. The row source of the combo box would be the table/query where the items came from.


    See if this close to what you are wanting......
    Attached Files Attached Files

  5. #5
    JJHeck is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Michigan
    Posts
    3

    Thumbs up

    Quote Originally Posted by ssanfu View Post
    I finally got it to work, but I had to modify a lot of your dB. You have spaces in all object names... not a good idea.
    While removing the spaces in names, I renamed a few fields to add a PK/FK suffix so I could tell what fields I was working with.

    Object naming:
    You have a table named "Shopping", but the PK field is named "OrderID" (or it was - I renamed it OrderID_PK) . I would have named the table "tblOrders".


    You had created non-updatable queries for 2 of the sub forms. (CC and PI). The record source for the form should be only the appropriate table/query. The row source of the combo box would be the table/query where the items came from.


    See if this close to what you are wanting......

    Perfect! Thank you very much! This is EXACTLY what I was looking for!
    Last edited by JJHeck; 05-16-2018 at 02:50 PM. Reason: Incomplete

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data Input Order Form and Combo Box issues
    By Tylerluk1 in forum Access
    Replies: 10
    Last Post: 07-28-2015, 12:46 PM
  2. Replies: 7
    Last Post: 09-10-2014, 06:56 PM
  3. continous form cant bind a field
    By MIchael Briggs in forum Forms
    Replies: 2
    Last Post: 11-26-2013, 10:37 AM
  4. Bind one combo box to another one
    By Zamani_2012 in forum Forms
    Replies: 1
    Last Post: 08-12-2012, 09:51 AM
  5. Replies: 6
    Last Post: 06-13-2011, 12:14 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