Foreign key column not updating in the table from the form

[mismag]

Here is my table structure and relationships of my database. And the form I use to enter the data.
For some reason, the "ProductID" column in "TblSaleDetails" is not being updated when i pick a product in the form and enter data for it.


Please somebody let me know where I am doing wrong.

Thanks,
Mismag

[ssanfu]

In the form, do you have the Link Master & Link Child fields set?



Don't totally understand your design... would you post your dB?

[mismag]

Yes Steve

Regards,
Mismag

[orange]

mismag,

We can not comment on your relationships or interface form(s) without knowing "what" business the proposed database is intended to support. Tell us in plain English what is the purpose of the database - you know - who does what when and how sort of thing.

Good luck.

[mismag]

Orange,

Business is car dealership sales data.
Dealership --> Managers (F&I Managers, Sales Managers) --> Deals.
Every month we enter previous month's deals information and create reports.

There are different products that each F&I Manager sell along with car. For example Service contracts, Warranty and so on. There are about 20 products, along with financed deals and leased deals.
I have tables for dealers, managers, products, Sales Information and VSC Breakdown (VSC is Vehicle Service Contracts). Only VSC again has different brands. That is why a different table again.


Here is an example of our data table, for a particular dealership and an individual f&I Manager, how we do now in excel.


Tricky part is the Total deals. Total deals here is not sum of all the deals.
It is sum of financed deals, Lease deals and Cash deals.
We don't use cash deals in our reports.

Hopefully you got the point, I am trying to explain.

Regards,
Mismag

[ssanfu]

Maybe you would post your dB for analysis??
Make a copy and delete the sensitive data...

[mismag]

Attached the database.
Thank you for taking time to help me.

Regards,
Mismag

[orange]

You may get some ideas re relationships and potential tables from this free Car Sales data model from Barry Williams' site.
What is a F&I Manager?
Are all these managers part of the dealership?

Can you give us more details in terms of processes? Who does what, when?
Any involvement of Orders, Invoices etc?

[ssanfu]

Your question:

For some reason, the "ProductID" column in "TblSaleDetails" is not being updated when i pick a product in the form and enter data for it.

You have the combo box for the product bound to the field "ProductName" and not to the field "ProductID". Since you are saving the actual product name, the "ProductID" does not get updated.
This is why I said the field "ProductName" should be deleted from the table "TblSalesDetails". You only need one of the fields. (I would use/save the ProductID value, not the text name)

If you are going to use/save the "ProductName", then you don't need the ProductID in the combo box row source

SELECT TblProducts.ProductName, TblProducts.ProductID FROM TblProducts;

You could delete the ProductID field in the table TblSalesDetails since it is not being used.

[ArviLaanemets]

You have the combo box for the product bound to the field "ProductName" and not to the field "ProductID.

Reading this sentence I suspected OP having lookup fields in tables - and he/she has! (But of-course this is not cause for current issue.)

[mismag]

Orange,

You may get some ideas re relationships and potential tables from this free Car Sales data model from Barry Williams' site.

This is really helpful. Thank you for sharing the information.

What is a F&I Manager?

F&I is Financing and Insurance managers.

Are all these managers part of the dealership?

Yes.

We need to create a monthly report based on the sales of the cars and related products per dealership per manager.
We get the information from the dealerships as pdf's.

Currently we are doing this reporting in excel. But I am trying to create a database so we have at least a year of data stored, so we can do quarterly reports without reentering the data manually.



Mismag.

[mismag]

Steve,

You have the combo box for the product bound to the field "ProductName" and not to the field "ProductID". Since you are saving the actual product name, the "ProductID" does not get updated.
This is why I said the field "ProductName" should be deleted from the table "TblSalesDetails". You only need one of the fields. (I would use/save the ProductID value, not the text name)

If you are going to use/save the "ProductName", then you don't need the ProductID in the combo box row source
Code:
SELECT TblProducts.ProductName, TblProducts.ProductID FROM TblProducts;
You could delete the ProductID field in the table TblSalesDetails since it is not being used.

Initially I had only ProductID in the table. There are more than 20 products. So when I enter data using the form, i needed to pick a product in the subform. This is difficult if i use productId. That is why I added ProductName.
Also, I am using Tableau to create reports using this Access database.
When I connect to Access from Tableau, I need to see the product name in the TblSalesDetails.


Let me try only with ProductName in the table.

Thank you for taking time to analyze my database.

I really appreciate all the group members for helping people like me.

Regards,
Mismag

[orange]

Mismag,

If you review the links in my signature, the link re Database Planning and Design has many useful articles in various formats. Working through the tutorials from RogersAccessLibrary will give you experience with design and relationships.
Good luck.

[ArviLaanemets]

So when I enter data using the form, i needed to pick a product in the subform. This is difficult if i use productId.

In subform, you'll neet a combo box with ProductID as ControlSource, with RowSourceType as "Table/Query", and RowSource as "SELECT TblProducts.ProductID, TblProducts.ProductName FROM TblProducts". ColumnCount will be 2, ColumnWidth "0;2.5". You select product name in combo, and product name is displayed, but into table ProductID is saved.

The form is proper place to use combos, not table.