Results 1 to 13 of 13
  1. #1
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99

    Adding New Record Using Combo Box

    Hi,



    I have a form that allows a user to select a company from a list in a combo box and then add an order number for when that company places an order. I have my tables set up as 1 to many so that there is one Customer to many Orders (See below):


    Click image for larger version. 

Name:	Annotation 2019-06-20 120123.jpg 
Views:	10 
Size:	77.3 KB 
ID:	38822



    I want the database to allow the user to select a company and add a new order number for that company. because of how my tables are set up, I would need to tell the database to add a new Order ID number in the order table to do this. Right now, when I select a company and create a new order number, I get this error because it is trying to look for an existing ID number for that customer:


    Click image for larger version. 

Name:	Annotation 2019-06-20 120354.jpg 
Views:	10 
Size:	68.8 KB 
ID:	38823


    How can I tell the database to create a new Order ID number for that customer?

  2. #2
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    I think I may have solved the issue. I did not have a control source set for the combo box, so I set it to Customer_ID and it seems to be working now

  3. #3
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    Scratch that. It worked when I first fixed the control source, but after exiting that form and going back into it, it gives me the same error

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Can you upload a zipped copy of the database?

  5. #5
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    Current Static Casting Inspection.zip

    Yes. I remade the form to try and see if that would solve the issue, and now it is not letting me enter in an Order Number and is giving me an error about my JOINS. I'm thinking these errors are all related to the fields that link the tables together and which field links I include in the form. Let me know if you are able to get anywhere with this. My data probably does not make much sense in the tables because I had to rearrange my tables and change up the data, so don't get hung up on the data that is currently in the tables if you are confused by it. All I need is to be able to add a new Order Number under a selected company in the "Add New Order" form. Thanks!

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Look at how I have configured the Add New Order Form

    1 Customer makes an Order on a Specific Date and can Order 1 Or More Items

    The Add New Order Form is based on the Customer Table ONLY and does not need the Order Table
    The Order is a Subform based on the Order Table
    The Items is a Subform Nested within the Order Table

    Take a look and make comments as necessary.
    Attached Files Attached Files

  7. #7
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    Hi @mike60smart

    Thank you for the response! Your database is definitely what I'm looking to do, but I have a couple questions. First, with the way that my relationships in my tables are set up, I have the CustomerID field in the Customer_Table linked to the CustomerID field in the Order_Number_Table as a one-to-many relationship so that when a user adds order numbers, they will all be linked to their respective company. So, in my tables, I need the CustomerID in the Customer_Table to show up in the CustomerID field in the Order_Number_Table next to every order for that company. For example, take a look at the data in my tables:


    This is my Customer_Table. I have circled the customer "Shell", and it's ID number is 12.

    Click image for larger version. 

Name:	Shell ID.jpg 
Views:	7 
Size:	42.7 KB 
ID:	38831


    This is my Order_Number_Table. I have highlighted the Shell Order Numbers that have been entered into the system, and next to each of them there is a number 12 in the CustomerID field, indicating that they are a Shell Order:

    Click image for larger version. 

Name:	Shell Order ID.jpg 
Views:	7 
Size:	90.9 KB 
ID:	38832


    This is what I want to happen when I use the Add New Order Form. With the modified Add New Order form you sent me, it does not link the CustomerID field in the Customer_Table to the CustomerID field in the Order_Number_Table. In the pictures below, I have added the order 8675309 to Dow, which has an ID of 11 in the Customer_Table. The number 11 does not show up in the CustomerID field in my Order_Number_Table next to the 8675309 order, however:


    Click image for larger version. 

Name:	Dow ID.jpg 
Views:	7 
Size:	39.0 KB 
ID:	38833


    Click image for larger version. 

Name:	Dow Order ID.jpg 
Views:	7 
Size:	90.5 KB 
ID:	38834



    Is there a way to link the two together on the form you sent me? Also, are the ID fields that you included on the Add New Order form necessary for the form to work, or can I delete them? I don't want to confuse the user with fields they won't use. Thank you so much!!!

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi I just added an item with an order No of 123456789 and it stores ok in the Order table

    See this renamed version

    You can hide the Pk & FK when you have confirmed that the correct data is being stored
    Attached Files Attached Files

  9. #9
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    Hi @mike60smart,

    I do see that it added your order number correctly in the table. But, when I try and add an order number, the CustomerID field linked with the Customer_Name says "(New)" in it, but I think it is supposed to contain the ID number of the Customer I choose. Why would it not be automatically filling that field with the appropriate ID number when I select a Customer?

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You are confusing yourself.

    Take a look at the attached modified version.

    When you add a new Order all of the Controls that are filled with Colour are the Primary & Foreign keys which will fill automatically as you go through the Order Process.

    The first Control you would select is the Order# and then the Order Date. This completes all the Data for the Order Date.

    Next you fill in the Item and all remaining Controls for an Order Item. Repeat for each Item required.
    You will note that all linking Primary & Foreign Keys have autofilled.
    End of Process
    Attached Files Attached Files

  11. #11
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    I understand exactly what you're saying, it makes perfect sense. I just tried the database you sent me, and the problem is that the CustomerID fields are not autofilling; they are maintaining the number 1 regardless of which Customer I choose. That ID number needs to change based on which Customer the user selects. Take a look at these screenshots to see what I mean:

    The ItemID fields are autofilling, but the CustomerID fields are not:
    Click image for larger version. 

Name:	Shell example_LI.jpg 
Views:	6 
Size:	59.8 KB 
ID:	38852

    You can see in my Order_Number_Table that the CustomerID 1 is linked with the Shell order I just entered, but this needs to be a 12 because 1 is Shell's ID number in the Customer_Table:

    Click image for larger version. 

Name:	Shell example Order Table_LI.jpg 
Views:	6 
Size:	65.2 KB 
ID:	38853

    Click image for larger version. 

Name:	Shell example Customer Table.jpg 
Views:	6 
Size:	42.3 KB 
ID:	38854


    This is the issue I have been struggling with. I've been messing with it in my own database to try and figure out how to link all the ID numbers together, and nothing is working.

  12. #12
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    I think I may have figured out the issue. I will let you know if something changes, but until then thank you for all of your help!

  13. #13
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    I just added a new Order for Shell

    This is the form showing the Linking ShellID as 12

    The Order table data shows this:-

    OrderNumberID Order # CustomerID OrderDate
    85 123 12 21/06/2019

    The OrderItem table shows this:-

    ItemID ItemNumberID Item # Pattern # Description Analysis Quantity
    92 85 aaa aaa aaa aaa 2























    I have no idea why the process is not working on your PC
    Attached Thumbnails Attached Thumbnails ID.JPG  

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

Similar Threads

  1. Replies: 1
    Last Post: 11-19-2018, 07:57 PM
  2. adding a record through a combo box
    By sssandhya89 in forum Access
    Replies: 11
    Last Post: 03-15-2013, 11:23 AM
  3. Clearing Combo Box when adding a new record
    By Purdue_Engineer in forum Forms
    Replies: 3
    Last Post: 09-25-2012, 12:57 PM
  4. Replies: 14
    Last Post: 05-25-2012, 02:40 AM
  5. Adding a record based on combo
    By cjamps in forum Forms
    Replies: 1
    Last Post: 02-24-2009, 12:01 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