Test rows to copy (1 Viewer)

richardw · May 24, 2016

Hi Dear All,

I have this code that copies a table content on the same table changing ID (Thanks to Arnelgp who helped me with this).

Code:

            Set rs = db.OpenRecordset("tbl_Vals", dbOpenDynaset)
    
            
            lngCounter = 1 
            With rs
            
            
            lngCounter = 1 'Set records counter on 1
            lngID = DMax("idVAL", "tbl_Vals")
            With rs
            

            If Not (.BOF And .EOF) Then
                
                .MoveLast
                lngRecordCount = .RecordCount
                'lngID = ![idVAL]
                .MoveFirst
                

               For lngCounter = 1 To lngRecordCount
                varidVAL = lngID + lngCounter
                varidProduct = ![idProduct]
                varidCustomer = "Here I assign a new ID that is chosen on the form" 

    
                bm = .Bookmark
                .AddNew 
                

                ![idVAL] = varidVAL
                ![idProduct] = varidProduct
                ![idCustomer] = varidCustomer

                
                .Update
                .Bookmark = bm
   		.MoveNext
               Next
               DoEvents
               End If
               
               
                .Close
            End With

Now I would like to test the Customer ID in the rows to copy, and copy just the records where ID has a specific value (specified on the form).

Thank you in advance

arnelgp · May 24, 2016

Code:

            Set rs = db.OpenRecordset("tbl_Vals", dbOpenDynaset)
    
            
            With rs
            
            
            lngID = DMax("idVAL", "tbl_Vals")
            With rs
            

            If Not (.BOF And .EOF) Then
                
                .findFirst "idVal = " & Me.[COLOR=Blue]yourIDTextBox[/COLOR]
        if .NoMatch Then goto exit_sub
                

                varidVAL = lngID + 1
                varidProduct = ![idProduct]
                varidCustomer = "Here I assign a new ID that is chosen on the form" 

    
                .AddNew 
                

                ![idVAL] = varidVAL
                ![idProduct] = varidProduct
                ![idCustomer] = varidCustomer

                
                .Update
               DoEvents
               End If
               
exit_sub:               
                .Close
            End With

richardw · May 24, 2016

Thank you Arnelgp,

I think that this code copies just one record, I would like to do as the first code I had (Copy all rows when ID=MyID).

Is it technically possible?

arnelgp · May 24, 2016

i see.

Code:

            Set rs = db.OpenRecordset("tbl_Vals", dbOpenDynaset)
    
            
            lngCounter = 1 
            With rs
            
            
            lngCounter = 1 'Set records counter on 1
            lngID = DMax("idVAL", "tbl_Vals")
            With rs
            

            If Not (.BOF And .EOF) Then
                
                .FindFirst "idVal = " & Me.yourIDTextBox
                
        lngCounter = 1
               Do While Not .NoMatch
                varidVAL = lngID + lngCounter
                varidProduct = ![idProduct]
                varidCustomer = "Here I assign a new ID that is chosen on the form" 

    
                bm = .Bookmark
                .AddNew 
                

                ![idVAL] = varidVAL
                ![idProduct] = varidProduct
                ![idCustomer] = varidCustomer

                
                .Update
                .Bookmark = bm
           .FindNext "idVal = " & Me.yourIDTextBox
        lngCounter = lngCounter + 1
               Loop
               DoEvents
               End If
               
               
                .Close
            End With

richardw · May 24, 2016

Thank youuu ! You are my savior

Test rows to copy (1 Viewer)

richardw

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

richardw

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

richardw

Registered User.

Similar threads

Users who are viewing this thread