Access 2016-'Product Inventory' templ db-add multiple contacts & del addr's to 'Customer' form

[PinkfudgeDebs]

Hi All, would really appreciate any Help! - although not a complete novice, am still learning and appreciate have a very long way to go?!



Created db using Access 2016 'Product Inventory' template database (will split and upload to SharePoint, create UI via saved, local copies)

My Problem:
'CustomerDetails'

form

(used for Company Details) have all relationships & queries pre-built (orders, invoices, inventory etc and everything works great) my problem is as follows:

The form does not allow for multiple contact details or delivery addresses..

Any thoughts please on how (or whether) I could do this (bearing in mind I am using an Access 2016 database template for a reason?!

Any help much appreciated!
Debs

[June7]

You want unlimited multiple contacts for each company? Build a related table and subform.

[PinkfudgeDebs]

Thanks June7,

Okay, had a feeling that was the way to go....can't seem to get my head around how to do it though?!

Existing form:



My thoughts:

1 - Create new 'Contacts' table
Fields: Company, Company ID, FirstName, LastName, Email, Title/Dept, Mobile, BusinessPhone,

2 - Create 'Contacts' Form

Insert query to 'Lookup' field for 'Company & ID' from 'Customers' table with expression to 'enter new' if not existing

3 - Create new 'DeliveryAddress' Table
Fields:

Company, Company ID

, Address, City, County, Country, PostCode

4 - Create new 'DeliveryAddress' Form

Insert query to 'Lookup' field for 'Company & ID' from 'Customers' table with expression to 'enter new' if not existing
Insert query to 'Lookup' field for 'DeliveryAddress' from 'Customers' table with expression to 'enter new' if not existing

The above said,.....unsure as to how to formulate the queries and then how to get this data to populate the 'Customers' table as a single record?

If someone would be generous enough to tell me if I'm on the right track and the provide some (in 'idiot speak please) some help on HOW to achieve, I would be incredibly grateful!!

Many, many thanks
Debs

[June7]

DeliveryAddress table would have CompanyID field, not Company field as that duplicates data.

They would not be a 'single' record. Use a form/subform arrangement. Main form for the customer/company and a subform to enter its associated contact/address records.

On an Order, use combobox to select company/customer then another combobox to select contact/address. Might want to utilize cascading/dependent comboboxes.

[PinkfudgeDebs]

Hi June7,

Huge Thank you for taking the time to have another look into my problem!

Will attempt to give it a go using your advice and see how I get on, as you know my knowledge base is somewhat limited to say the least........here's keeping my fingers crossed.... may take me a while!

Huge thanks once again!
Debs

[PinkfudgeDebs]

.....and DONE!!!!

Just wanted to say a huge thank you June7, cannot express enough how grateful I am.

HUGE THANKS
Debs