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