Unabe to enter data in the primary key on a form

[ETCallHome]

Hi,

I have a split database, but the front-end has additional tables. This should not be a problem as far as I can. I created a Form on the front-end to add records to one of the tables. However, this table is linked to another table that only exist on the front-end database. I can enter all the data on the form, with exception to the Primary Key that links these to tables together.

It looks like Access does not allow you to enter the primary key data if the tables are linked. Does this mean I have to create to separate forms to add the data. I would like to use one form that will add to both tables simultaneously, thereby avoiding additional work. NB! I use the same logic when updating and it allows me to update the data on both tables using the same form, but when adding, then it will not allow me to enter data into the primary key.

Your help is much appreciated.

Thanks


Earl

[June7]

Need to know the relationship of these tables - one-to-one, one-to-many? Which table is parent and which is child? In parent the key is primary, in the child key is foreign. Is the primary key an Autonumber?

[ETCallHome]

Tbl 1 has fld A as primary key and Tbl2 has fld A as primary key. Tbl1 is parent and there is a one to one relationship

[June7]

A just tested a query joining two tables with one-to-one relationship. No problem entering values into each primary key field.

[ETCallHome]

Below is my SQL Statement:

SELECT PLUData.PLU, PLUData.Code, PLUData.Description, PLUData.Department, PLUData.InvIn, PLUData.Price, PLUData.IsUpDated, tblPLUStockCost.* FROM PLUData INNER JOIN tblPLUStockCost ON PLUData.PLU=tblPLUStockCost.PLU;

The tables are PLUData (Parent) and tblPLUStockCost (Child). The primary keys have the same field name, i.e. PLU.

When I select modify using the same form, it allows me to modify both tables and updates both tables simultaneously because primary keys are locked. When I select Add a record, using the same form all the data I can enter, except the primary key on the PLUData, but I can enter the Primary Key on the tblPLUStockCost.

Could it mean that the one table is updatable, while the other is not, but then it does not explain why the non-primary keys allows entering of data.

I have now been sitting hours with this... and I will not rest until I can figure it out. I am even considering creating 2 seperate forms to add the data for the 2 tables.

Any suggestions is much appreciated

[June7]

I cannot replicate your issue. Do you want to make project available by attachment (zipped if necessary) or upload to fileshare site such as box.net and post link to file? Either way, remove confidential data, run Compact & Repair first.

A form/subform arrangement would certainly work.

What is nature of the relationship - if there is a record in table 1 will there always be related record in table 2?

[ETCallHome]

Hi June,

Sorry for replying so late, but I think I found the problem. When I created the 1 to 1 relationship, I assumed that Tbl1 was the parent and Tbl2 was the child, it appears that Access made Tbl2 the parent and Tbl1 the child. When I enter the data in fldA of Tbl2, it allowed me to save the record, however only the record on Tbl2 was saved, becase Tbl1 had a second fld as a primary key and since this value was null, it did not save the Tbl1 record.

I am busy making changes to my code to see that the function works and will keep you updated.

Thanks a mill