How do I NOT overwrite data in a table in Access 2010?

[richard1941]

I have a form that has 2 combo boxes and a list box. As I select items from each of the lists, the lists get changed. I tried setting some queries to Snapshot, but then I couldn't select from the list.



Alternatively, How can I find out where or why the data is being overwritten?

Thanks for your help.

[June7]

The form RecordSource and data controls ControlSource properties determine where data input goes to.

If the table that is source for the list is changed then you probably have the table included in the form RecordSource and the combobox is bound to the wrong field.

If you don't want data overwritten in the destination table, then move to new record row and create new record.

If you want to provide db for analysis, follow instructions at bottom of my post.

[richard1941]

I have attached a copy of my database. The form that is causing trouble is frmIdentifySubOrganization. The tables involved are tblOrganizations, LUtblTypeOfSubOrg, and tblOrgSubOrg. Both tblOrganizations, and LUtblTypeOfSubOrg get corrupted frequently.

[orange]

Do yourself and readers a favor --name your ID fields with meaningful names within the Tables. You have too many ID fields that are confusing. Use things like RoleID, not just ID.
In your junction tables (removing the M:M relationships) you do not need both an ID and all FK to make a unique record. Either use all FK or use the separate ID PK and a unique composite index of the fields representing FK (to prevent duplicates).
You might consider a tblSubOrgType --seems to be missing.

Good luck.

[June7]

Think the table exists, called LUtblTypeOfSubOrg.

Instead of the listbox pulling from a query object, just make the SQL statement pull from the table.
SELECT [LUtblTypeOfSubOrg].[TypeOfSubOrg] FROM LUtblTypeOfSubOrg ORDER BY TypeOfSubOrg;

That form/subforms makes no sense. Why do you have the main form bound to LUtblTypeOfSubOrg and the listbox RowSource is the same? And the listbox is bound to TypeOfSubOrg. Of course selecting an item from the listbox will change the record.

Then the subforms are not linked to the main form and have no reason to be. Also, the two subforms are bound to the same data.

This is not the way to build forms for data entry.

Suggest you study some more tutorials on relational database concepts and Access functionality. Maybe this will help http://www.rogersaccesslibrary.com/