Solved How to create new record from filtered record

[MajP]

Taking my suggestion add the following

         ![Customer Name] = Me.[Customer_Name]
         !City = Me.City
         !InvoiceDate = Me.InvoiceDate
         !ParentInvoice = Me.InvoiceID
         !RecordType = "Rollback"
            .Update

Now your main form recordsource should include
Where RecordType is null
Because you do not want to see the duplicates.

Then Only show the duplicate button where the current Invoice does not have a child invoice. You do not want to duplicate, duplicates.

InvoiceID	Customer Name	City	InvoiceDate	ParentInvoice	RecordType
1	Jones Supermarket	New Livingstone	3/10/2025
2	Jones Supermarket	New Livingstone	3/10/2025	1	Rollback

tblInvoiceHeader

 

[jdraw]

MajP,

Seems reasonable. This would identify the Rollback/CreditNote which the OP has not explained.

 

[MajP]

Seems reasonable. This would identify the Rollback/CreditNote which the OP has not explained.

I say it is more than simply reasonable, it is the only way this could have a chance of making sense.. There is no way this could be useable without the way to relate the records. It would be completely meaningless.

 

[jdraw]

I agree. It's too bad the OP didn't/hasn't described the business logic or provided examples of before and after.
I'm not an accountant, but I don't follow how you could have Invoices and CreditNotes in same table without some means of identifying what is what. Your solution handles it.
I could add a field "IsCreditNote" or similar and populate it when converting an Invoice to CreditNote. Again, OP's request is vague.

 

[nector]

"...completely reverse the transaction..."

Okay, now I think I get it. You do not really want a duplicate record in a table.

You want a record that has the exact opposite impact of the first record.

You enter an order, but then need to reverse that transaction by entering the same details when the order is later cancelled or returned. Somehow, you also need to indicate that this record is a reversal of the previous transaction.

Is that an accurate statement of the reason for this requirement?

Very good understanding sir

 

[nector]

I have created an insert query it works exactly as required but its not inserting the foreign key in the child table , if that works then is exactly what I wanted.

Insert queries

Private Sub CmdDuplicate_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry01"
DoCmd.OpenQuery "Qry02"
DoCmd.SetWarnings False
End Sub

 

[MajP]

it works exactly as required but its not inserting the foreign key in the child table

Because your solution does not insert the foreign key, but all the provided solutions do?

 

[nector]

When you create an invoice and you discover that the entire invoice is wrong. Then you must reverse that invoice , create an opposite document called credit note referencing the invoice being reversed . This record will have its own primary and foreign keys in both the parent and child tables.

The above can be achieved by capturing the credit note manually but what if you have 200 lines to capture don't you think that its pain to users to do that , that is why I need the automatic copying of the invoice being reverse and then once that new copy is generated simply requery it and change the document type to credit note. This way it will be simple for the users to handle credit notes rather than capture line by line manually.

 

[nector]

Because your solution does not insert the foreign key, but all the provided solutions do?

Is there a way to insert a foreign keys, that is my query nothing more

 

[MajP]

The above can be achieved by capturing the credit note manually but what if you have 200 lines to capture don't you think that its pain to users to do that , that is why I need the automatic copying of the invoice being reverse and then once that new copy is generated simply requery it and change the document type to credit note. This way it will be simple for the users to handle credit notes rather than capture line by line manually

And the provided solution did not do that?

Is there a way to insert a foreign keys, that is my query nothing more

How do you expect query2 to know the new foreign key????? That is what all the provided solutions do.
Explain why you cannot use the provided solution? I am baffled.

 

[Mike Krailo]

Here is what you were trying to do, but as MajP pointed out, there is no way to differentiate the normal order items from the rollback items unless you change the structure of the table as he already laid out for you.

INSERT INTO tblLineDetails ( InvoiceID, Quantities, SellingPrice, ProductName )
SELECT tblInvoiceHeader.InvoiceID, tblLineDetails.Quantities, tblLineDetails.SellingPrice, tblLineDetails.ProductName
FROM tblInvoiceHeader INNER JOIN tblLineDetails ON tblInvoiceHeader.InvoiceID = tblLineDetails.InvoiceID;

I just made a third query so you can do the InvoiceID insert as requested, but this is not practical as it is.

 

[nector]

Here is what you were trying to do, but as MajP pointed out, there is no way to differentiate the normal order items from the rollback items unless you change the structure of the table as he already laid out for you.

INSERT INTO tblLineDetails ( InvoiceID, Quantities, SellingPrice, ProductName )
SELECT tblInvoiceHeader.InvoiceID, tblLineDetails.Quantities, tblLineDetails.SellingPrice, tblLineDetails.ProductName
FROM tblInvoiceHeader INNER JOIN tblLineDetails ON tblInvoiceHeader.InvoiceID = tblLineDetails.InvoiceID;

I just made a third query so you can do the InvoiceID insert as requested, but this is not practical as it is.

Thank you so much you appear to understand my problem and its almost just there, check on the child tale the key being entered 1 instead of new foreign key called 2 , if this can be achieved that is all I want.

 

[Mike Krailo]

Try this.

 

[nector]

Many thanks Mike you pointed me to the right direction with a minor amendments to your code, this now work correctly

INSERT INTO tblLineDetails ( InvoiceID, Quantities, SellingPrice, ProductName )
SELECT DMax("InvoiceID","tblInvoiceHeader"), tblLineDetails.Quantities, tblLineDetails.SellingPrice, tblLineDetails.ProductName
FROM tblInvoiceHeader INNER JOIN tblLineDetails ON tblInvoiceHeader.InvoiceID = tblLineDetails.InvoiceID
WHERE (((tblInvoiceHeader.InvoiceID)=[Forms]![frmnvoiceHeader]![Cboinvoices]));