Incorrect value written to underlying table on form entry

[scouser]

Hi. I have been modifying an old access database to give it a new lease of life.

I have a Sales Orders from with sub form Sales Order Details. When user selects a package (Sales Order Details) from cboPackageID the remaining fields are updated. The form is based on a query’s ‘qrySalesOrders’ and ‘qrySalesOrderDetails’. When a user selects a ‘Package’ I would expect the ‘PackageID’ value to get written to the underlying table ‘tblOrderDetails’. However it is the PackageDescription that gets written.

In table tblOrderDetails the ‘PackageID field has a data type number. When user selects ‘Package ‘A’ I would expect ‘PackageID’ value ‘1’ to get written to ‘tblOrderDetails’ however value ‘1 10x8 2 7x5’ is written.

Sample DB attached (last edited using access 2010). The DB opens to show the 'Sales Order Form'.
Many Thanks,
Phil.

 

[John Big Booty]

Have a look at your table tblOrderDetails in design view, your field PackageID is using a table level look ups this will be why you are seeing the package description rather than the ID.

 

[scouser]

Thanks John. I did this database over 10 years ago and have not worked with Access for some time. I have been asked to knock up a database as a favour so I am attempting to modify what I have. So If I drop the lookup what will it break?

Also at present the database simply allows selection of services and dislays the sales price. I have been asked to show costs. My initial thoughts were to simply add new fields to tblPackages and tblOrderDetails 'Cost'. I do not want to over engineer. In reality the Packages sold have a structure (BOM). That is a whole new world I do not want to enter in Access

Thanks,
Phil.

 

[scouser]

John I dropped the lookup and the table the expected value is now populated. However now when I select an existing package within the Sales Orders form a new row is entered in tblPackages?

I have attached the DB with the Lookup removed.
Thanks,
Phil.

 

[scouser]

I have just taken another look and the issue I reported previously is not present (which adds to the confusion as a new row WAS added to tblPackages)..... However I can no longer replicate it!

So all appears well in the world after dropping the table lookup.
Phil.