Form Help

[Mnewton911]

Here is the task that i am trying to complete.
I have a tracking system that i am using to monitor changes to a product.

Within one table - i have a listing of the products and details for each products ( Called Default) - then i have a listing of changes that are entered and tracked on another form to a table called (changes).

I want to create a form that has 2 purposes -

1) I wanna look up the name of the product within the default table field.
2) I want to simultaneously delete the record from the default table, while tracking the product name and indicating the deletion with a date and a yes\no box in the changes table.

Most of my application thus far hasnt had to track things in multiple tables like this and i am stumped.

Any help would be greatly appreciated in helping me accomplish this endeavor.

Thank You

 

[jzwp22]

If you delete the record in the default table, you will end up having orphan records in the changes table that are unrelated to any product in the product table. That is not good database practice.

What I would recommend is to add a field to the default table such as a yes/no field that identifies the product as obsolete when the yes/no field = yes. In your form, if someone changes the yes/no control from no to yes, you can run an append query to add a record to the changes table indicating the date the product was marked as obsolete (along with any other info you keep in the change table).

You can use the yes/no field to not show obsolete products in the form by filtering the form's record source.