Trouble updating hidden fields while the user add a new detail record

[MBlondinde]

Hello,

I'm pretty new to Access but know programming since a while. I have to develop a very basic form to add/update data within a table.
My form has a combo box where the user select a record then a detail list with 1 or more entries are displayed. Only 3 fields out of 10 are displayed to the user. Modifying the record works fine. However when the user try to add a new entry, I get the following error:
The Microsoft Access database engine cannot find a record in the table 'Product' with key matching field(s) 'ProductID'

The interesting part is that there should not be any referential integrity problem. The ProductID is one the of hidden field. However, I'm trying to force through code the assignment of the hidden fields based on one of the detail record already displayed. I've tried putting this piece of code in the Form.BeforeInsert event or Form.Dirty. From what I see, I do assign a valid ProductID (wrote a Msgbox(Me.Recordset!ProductID) at the end of both even and the value is valid.



Funny thing is that I don't have any Relationship defined in Access (neither Foreign Keys on the SQL Server database in the backend). I guess it's because the main query of my form is:

SELECT [Detail].*, [Product].ProductID, [Product].ProductDescription
FROM [Detail] INNER JOIN [Product] ON [Detail].ProductID = [Detail].ProductID;

I'm kind of lost here and any help would be appreciated.
Thanks!

[ranman256]

It looks like you are trying to enter data , but 1 of the tables is not getting its entry , so error.
Make sure your join allows the data entry to goto both tables.

[MBlondinde]

Well I'm not trying to enter data in two tables but only my "Detail" table. I'm wondering if it is the inner join on the Product table at first (Main form query) that cause the problem. However I need this join for filtering purpose initially since I need to exclude some row based on a field value in the related Product table.
So at the end, I want to insert a new record only in the DETAIL table, not the Product; is Access trying to insert into the Product table as well simply because I have an Inner join to this table in my main query?

[ranman256]

Yes. Its the join. If you are entering data into 1 table , then why the join?
remove it.

[MBlondinde]

The join is needed to filter the records based on specific value in the related Product table. As an example, I don't want the user to see the DETAIL record for which the PRODUCT CATEGORY = 'A'. Thus I'm filtering the initial query using the JOIN and WHERE clause which I forgot to add in my initial post.