Deleting record after use

[Geoffk]

Hi All

I have 2 tables "Customers" and "Work in Progress (WIP)"

The "WIP" table is result of the form "WIP" where I input all my data for each job completed. I use a lookup control "JobNo" to input the customer details from the table "Customers".

The objective is to call up the customer details into "WIP" add the job details, then on save, delete the record in the "Customers" table.

If this is possible, could you give me some ideas how.

Thanks in Advance.

Geoffk

 

[Alc]

Assuming you use a button to record the update on the WIP form, you could add some code to the On Click event that does the deletion. For example, if the common field between the Customer and WIP tables were Account_Number, you could use something like:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM Customers WHERE Account_Number = 1;"
DoCmd.SetWarnings True

 

[boblarson]

You shouldn't be deleting anything. Your structure sounds like it isn't normalized and therefore you are going about this in a way that doesn't make sense in a relational database. You shouldn't be storing customer details in the WIP table. You should only be storing the ID for the customer so that the data can be pulled back together via a query, or set of queries. Does this customer data never need to be added again? If the same customer has another job, is the data entered again? The customer data should only be needed to be added once and then actual work in progress can be noted in the WIP table.

 

[The_Doc_Man]

I agree with Bob.

As a stop-gap measure to make this work until you can redesign it right, add a Yes/No field to the WIP table so that when the work is no longer in progress, you mark the flag. It is six of one and a half dozen of the other as to whether you make the flag "Completed" (in which case you make it YES when you have done your update) or "InProgress" (in which case you make it NO when you have done your update.)

This gives you historical abilities because now you never lose data. And instead of doing the update from the WIP table, just do it from a query of that same table that filters for whatever state of that Yes/No flag means "still active."

 

[Geoffk]

Thanks for your replies

The customer details in the customer table is only used once it then becomes redundant, (the basis for this DB is to track materials used and payments due, after the details are entered ie: JobCode, COD collected inventory used, the customer details are not required).

Thanks
Geoffk

 

[boblarson]

Thanks for your replies

The customer details in the customer table is only used once

Just trying to understand - So, you'll never need to enter that customer again for another job? They are only one-time things that, once an initial order is entered, you never ever have to use them again? They will never have another order for which you would need them in the customer table?