MS Access: Prevent Combobox Control from adding new data to linked table

Question

MS Access (current version/365) -- Trying to build a simple invoicing solution. For the sake of this question, I have 2 tables:

• clients (ID [autoNumber], Name etc).
  • Example: ID = 1//name = "client 1"
  • Example: ID = 2//name = "client 2"
• items (ID [autoNumber], ClientID [FK to clients --> ID], itemName, etc.).
  • Example: ID = 1// clientID = 1 // name = "item 1 for client 1"

PK/FK/relationships established properly and clients are added in manually.

I want to build a form that has the capability to add in new items. Since it's difficult to identify clients by ID I'm hoping to (visually) link the clients and items table so I would have a dropdown (combobox) with the client name and then I could add in the new items associated with the client from the dropdown. There is no need to add in new client information at this stage, as that has already been added in in a previous step. Simply put, I want to select "client 1" from the dropdown, add a new item and it gets saved to the items table with client ID 1 (see above example) as:

• New item: ID = 2// clientID = 1 // name = "item 2 for client 1"

With the comboBox: The problem I have is that if I set the Control Source to be Client Name and the row source to be the appropriate column values from the clients table, if I add in a new item, Access will duplicate the client name under a new client ID. The item therefore gets saved as:

• New item: ID = 2// clientID = 3 // name = "item 2 for client 1"

Question: how on earth do I prevent Access from adding in a new (duplicate) client name with a new ID and just add the item to the existing client? Thnx

Answer 1

To help your Client combo box and only seeing the ID, have the ClientID as the first column on the query builder and their name in the second column, then under the combobox Properties > Format, for Column Count put 2 (or 3 if you want to do First name last name) and for Column Width, put it as (for column count 2) 0;1.5 or (for column count 3), 0;1.5;1.5, this will hide the ID and only show the name.

Also what are the buttons on your form? You would need a 'New Record' button because it seems like you're just overwriting one record. Does your form show Record: 1 of __ on the lower left side of your form?

Edit: What table did you want to collect all this data in? What is your forms Record Source set to? (to find this, click on the square in the top left corner of your form in Design View)

When the square is not clicked:

When the square is clicked:

Right-Click to open Form Properties and it should be under Data.