macro to delete record in cont. form... but only from one table (not both!)

[gangel]

Hi pplz, i have a 2 linked tables giving info to one form
Invoices & Scripts



So i have the following info in the form:

Invoice number, Invoice Date, Script number, Script date

Now i have put a button at the end of the form to delete the record, however it deletes the record from both Invoices and scripts.. whereas the script needs to stay, but i need to delete the invoice.

I hope that makes sense...
Any help would be awesome

[RuralGuy]

What is the relationship of the two tables? Many Invoices for one script? One Invoice for one script? Many scripts for one Invoice?

[gangel]

hey rural guy, sorry for the late response, i typed one and it must have not come through!!

They are in a one to one relationship, each script can only be invoiced once each.

What i need is it to delete the invoice record if the person finds out the invoice had an error, however if there is a script issue i dont want it deleted as they will still need to invoice that later, so the record needs to NOT be deleted. Hope that makes sense!

[RuralGuy]

It does but do the Invoice table records have a unique field for each record? Will Invoice Number do it?

[RuralGuy]

What field is in the Script table that links it to the Invoice table. Logic tells me that it is the Invoice Number but if you delete the Invoice record then that field in the Script table will need to be cleared. How do you get it linked again.

[gangel]

i have a "link creator" page, which lists all scripts that havent been invoiced.

There is a button at the end of each reocrd (continuous form) and if you want to add that record to the current invoice claim then you press the button and it auto creates the link

Pretty much:
Scripts(form)
Fields:
ScriptID
Date Dispensed
Drug


SingleInvoices(form)
InvoiceID
InvoicePrice
BatchID
ScriptID

MasterInvoice(form)
BatchID
BatchDate

Therefore

Masterinvoice (BatchID) ----> SingleInvoices (BatchID):SingleInvoices(ScriptID)----->Scripts(ScriptID)

first is one to many (one batch has lots of single invoices) then next is one to one (one single invoice has one script)

I can send screen shots, this is first time ive had to ask something this in depth so didnt know which info to provide but thanks for having patience and not giving uP!!!

[gangel]

relationship view

We are dealing with MasterInvoice, PPIinvoice(which is the one i called single invoice) and Scripts

[RuralGuy]

So it sounds like your "Delete the Record" button on your form needs to run a delete query that deletes the record in the PPLInvoice table with the current InvoiceID number. Would you agree?

[June7]

If there is a 1-to-1 relationship and each script will always have an invoice, why not combine the tables? Even if script does not always have an invoice, I would probably combine the tables.

What is MasterInvoice for? Multiple are scripts assigned to same invoice? TotalValue should probably not be a field in table and instead calculated when needed in query.

[gangel]

thanks june, ill look into putting them in the same table.

What happens is its for a claim,
so we send about 100 scripts to a company, they claim money for us
then they send an invoice back to us with scripts invoiced on it.


The issue is:
they dont invoice what we send, they invoice whatever has been paid (so may come from all different script batches).

The reason the value is inputted, is taht this is the value of the total invoice that the company has written on their page.
We enter each invoiced item and then the top value turns green to say the sum of all the items is the same as the top value added


RuralGuy,
Yes that is pretty much it exactly. SO i create a delete query...
ill have a go at it tonight and see how i progress!