Solved How to create new record from filtered record

[nector]

Hi

I have managed to get the underlaying record by filtering, from the filtered record I want to create a new record to be used as a credit note. The filter below gets the invoice detail all of it, instead of recapturing the records one by one I'm using filter below, now I want to use that record to be new record or addition so that I create a credit note.

How do I convert that record to new addition so that I can convert it to a credit note

Here is the filter

On Error GoTo Err_Handler

Me.Filter = "InvoiceID = " & Me!CboInvReversal.Value & ""
Me.FilterOn = True

Dim Records As DAO.Recordset

    Set Records = Me![sfrmLineDetails Subform].Form.RecordsetClone

    If Records.RecordCount > 0 Then
        Records.MoveFirst
        While Not Records.EOF
            Records.Edit
              
            Records.Update
            Records.MoveNext
        Wend
    End If
    Records.Close
Exit_CboInvReversal_AfterUpdate:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_CboInvReversal_AfterUpdate

 

[theDBguy]

To add a record, you would use .AddNew instead of .Edit

 

[nector]

I have tried that but it is adding zero record up to infinite until I had to stop it by rebooting

The parent table and the child table need to have the new record

 

[theDBguy]

I have tried that but it is adding zero record up to infinite until I had to stop it by rebooting

What is the value in Me.cboInvReversal? When you filter the records, how many records do you end up having? If more than one, are you saying to want to create that many records?

 

[MajP]

but it is adding zero record up to infinite until I had to stop it by rebooting

You cannot loop a recordset forward and add to it at the same time in each loop using a "While Not Records.EOF" construct.
Since each time you iterate you are adding a record to the end of the file and thus never get to end of file. Creating an infinite loop.

Although I have never done it, I would assume you can loop it backwards, or you could count the records and loop that many..

Records.movelast
Do while not Records.BOF
    'code to add a record
   records.moveprevious
loop

Or you can do a SQL insert query instead of working the same recordset.

 

[Minty]

Similar to MajP thoughts, you can't use a recordset clone and then add to it, think about it, you need to add to table.
You can use that recordset to Drive an Insert statement or probably more simply use an insert saved Query based around the source filtered record set.

 

[MajP]

Similar to MajP thoughts, you can't use a recordset clone and then add to it, think about it, you need to add to table.

No, you definitely can add to the recordsetclone. It is connected to the underlying table.

But you cannot do something like this where you read each record and create a modified version of it. This will cause an infinite loop

Do while not RS.EOF
  RS.addnew
     'Add a record, but it goes at the end making impossible to get to EOF
    RS.update
  rs.moveNext
Loop

There are lots of ways to get around it such as creating a second RS, loop in reverse, loop only the original X records, sql insert.

 

[nector]

The record is properly retrieved and I can edit it the problem is the it doesn't come with a new primary key, I'm simply trying to create the exactly duplicate record with just a new primary key ,think of reversing the record or a credit note the only difference required here is that the record must go to the new primary key

 

[theDBguy]

The record is properly retrieved and I can edit it the problem is the it doesn't come with a new primary key, I'm simply trying to create the exactly duplicate record with just a new primary key ,think of reversing the record or a credit note the only difference required here is that the record must go to the new primary key

So, are you trying to add a single or multiple new records in one go? If only one record, then you don't need a loop (Do While).

 

[nector]

Kindly see the example database for easy experiment , I want the same record to be copied to primary key number 2 to make a copy of record on primary key number 1. I do want people to be capturing manually , this way we will avoid errors, both record in parent and child table must be the same with the duplicate.

This a clear example of passing a credit note it reverses the original invoice not partial but 100%

 

[nector]

So, are you trying to add a single or multiple new records in one go? If only one record, then you don't need a loop (Do While).

I need the same record to populated in both the parent and child

 

[theDBguy]

So, are you trying to add a single or multiple new records in one go? If only one record, then you don't need a loop (Do While).

I need the same record to populated in both the parent and child

Take a look here.

Microsoft Access tips: Duplicate the record in form and subform

How to duplicate the current record on a form in a Microsoft Access database, along with the related records in the subform.

allenbrowne.com

 

[MajP]

I am clueless of what you want to do, but I am pretty sure it makes no sense.

Are you saying you want to duplicate the Invoice and duplicate the related child records.
Jones Supermarket (duplicate)?

This does not make sense, but to do that.
1. I would do a "Select Into " into tblInvoiceHeader.
2. Get the max InvoiceID (dmax)
3. Edit the new record such as mark this record "backup or duplicate"
4. Loop the child records
5. do an insert query into line details updating the invoice id to max id from line 2 above.

 

[GPGeorge]

Kindly see the example database for easy experiment , I want the same record to be copied to primary key number 2 to make a copy of record on primary key number 1. I do want people to be capturing manually , this way we will avoid errors, both record in parent and child table must be the same with the duplicate.

This a clear example of passing a credit note it reverses the original invoice not partial but 100%

Why do you want duplicate records?

 

[jdraw]

Please describe-in simple terms- what you are trying to achieve. If a CreditNote is different than an Invoice, how do you distinguish the 2? Should it be a separate table or , if in the same table, don't you need some discriminator?
Perhaps you could describe the business process in simple English with an example.

Perhaps a function to ConvertInvoiceToCreditNote??

 

[nector]

What I want to archive is to completely reverse the transaction without changing the copy record here, for example see the picture below:

I want the same record to be copy into the next new record that is it nothing else

 

[GPGeorge]

"...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?

 

[Mike Krailo]

So this is all about handling returned shipment of goods?

 

[MajP]

Again, I doubt what you want to do makes sense as described. But at a minimum if I was doing what you suggest and making a copy I need to know that the newly created record is related to the original record and probably need some field to say what type of record it is.

So if I create a "Duplicate" of Jones Supermarket I save the foreignkey "1" as the parent invoice and know that the two invoices are related. Then I mark my "duplicate" something as record_type "Return". Now you may or may not need to even copy the linedetails and relate them to the new record because they are related through the original Invoice.

 

[jdraw]

I attempted to do what you've asked, but do not understand your business logic.
Hopefully, someone else will understand your request and offer a solution.

I added a button and used Allen Browne's code to copy as mentioned in #12 by theDBGuy.
I changed your Data Entry from Yes to No so I could see the data in the Form.

Revised Allen Browne code:

Private Sub btnConverToCNote_Click()
'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
   
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
   
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
'  Allen Browne example fields
'                !CustomerID = Me.CustomerID
'                !EmployeeID = Me.EmployeeID
'                !OrderDate = Date
'                'etc for other fields.
         ![Customer Name] = Me.[Customer_Name]
         !City = Me.City
         !InvoiceDate = Me.InvoiceDate
            .Update
           
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !InvoiceID
           
            'Duplicate the related records: append query.
            If Me.[sfrmLineDetails Subform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [tblLineDetails] ( InvoiceID, ProductName, Quantities, SellingPrice ) " & _
                    "SELECT " & lngID & " As NewID, ProductName, Quantities, SellingPrice " & _
                    "FROM [tblLineDetails] WHERE InvoiceID = " & Me.InvoiceID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
           
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
End Sub