Copy values from 2 fields in prior record set into 2 different fields in current record set

**hehrerh** · 03-05-2018, 08:35 AM

Hello - I need help coming up with a VBA loop to copy values from 2 fields in a previous record set into 2 different fields in the current record set.

I have Start_Dirt and End_Dirt fields which need to be copied from the prior record set into the Start_Lots and End_Lots fields of the current record set.

The table in question is YearSetup, and it has a combination key comprised of foreign key DevSet_ID and Rep_Year. I created a calculated field that makes these values into a unique key Rep_YearID (DevSet_ID 2 and Rep_Year 2017 = Rep_YearID 22017).

I also created a calculated field for the prior record-set from which the record should pull End_Dirt and End_Lots values into its own Start_Dirt and Start_Lots fields (in the prior example, I would want to copy and paste values from the 12017 Rep_YearID record set)

I am not good at coding, but I feel like there should be a VBA loop capable of executing this - much like an amortization schedule.

Help!

Thank you,
Howie

**orange** · 03-05-2018, 09:32 AM

Howie,
There probably is a vba loop that can help with your issue. However, it would be helpful to you and readers if you could describe in simple, plain English the business involved; the major things and the processes that involve those things. We have no idea of the "dirt business", so if we are to help you, we need more info.

Good luck.

**ranman256** · 03-05-2018, 09:36 AM

something like:

Code:

Public Sub FillBlankRecs()
Dim rst
Dim vVal, vKey, vPrevKey
Dim vStartDirt, vEndDirt


DoCmd.SetWarnings False
Set rst = CurrentDb.OpenRecordset("qsMyQuery")
With rst
   While Not .EOF
      'the 1st record HAS no prior record
      
        If IsNull(vVal) Then
           .Fields("Start_Lots").Value = vStartDirt
           .Fields("End_Lots ").Value = vEndDirt
           .Update
        End If
        
        vStartDirt = .Fields("Start_Dirt").Value
        vEndDirt = .Fields("End_Dirt").Value
        
       .MoveNext
   Wend
End With


Set rst = Nothing
DoCmd.SetWarnings True
MsgBox "done"
End Sub

**hehrerh** · 03-05-2018, 11:03 AM

Originally Posted by orange

Howie,
There probably is a vba loop that can help with your issue. However, it would be helpful to you and readers if you could describe in simple, plain English the business involved; the major things and the processes that involve those things. We have no idea of the "dirt business", so if we are to help you, we need more info.

Good luck.

I'll try to make it a little easier to understand!

In a residential development in 2017:
end lots = 38
end dirt = 40

In the same residential development in 2018:
start lots = 38 (copied from above)
start dirt = 40 (copied from above)

Repeat this process for all developments for all years.

The code offered above didn't quite work. I keep getting an "object required" error on the While Not .EOF line item.

**orange** · 03-05-2018, 11:23 AM

Did you include?

Code:

With rst

**hehrerh** · 03-05-2018, 11:40 AM

Originally Posted by hehrerh

I'll try to make it a little easier to understand!

In a residential development in 2017:
end lots = 38
end dirt = 40

In the same residential development in 2018:
start lots = 38 (copied from above)
start dirt = 40 (copied from above)

Repeat this process for all developments for all years.

The code offered above didn't quite work. I keep getting an "object required" error on the While Not .EOF line item.

Yes - see attached. I added back in the missing line 'Set rst = CurrentDb.OpenRecordset("YearSetup")'. Now it runs fine, but no values are updated in my record set. It looks like this code doesn't extract information from the previous record - which section looks back at the previous record and pulls the information from Lots_End and Dirt_End?

Code:

Option Compare Database


Private Sub Command0_Click()


Dim rst
Dim vVal, vKey, vPrevKey
Dim vStartDirt, vEndDirt




DoCmd.SetWarnings False
With rst
Set rst = CurrentDb.OpenRecordset("YearSetup")
   While Not .EOF
      'the 1st record HAS no prior record
      
        If IsNull(vVal) Then
           .Fields("Lots_Start_Calc").Value = vStartDirt
           .Fields("Lots_End").Value = vEndDirt
           .Update
        End If
        
        vStartDirt = .Fields("Dirt_Start_Calc").Value
        vEndDirt = .Fields("Dirt_End").Value
        
       .MoveNext
   Wend
End With




Set rst = Nothing
DoCmd.SetWarnings True
MsgBox "done"
End Sub

**hehrerh** · 03-05-2018, 11:53 AM

Here is a visual of what I'm trying to accomplish. I believe there must be some way to look at the Previous year's YearDevID, copy the two fields shown, and paste them into the current year's record by using YeaerDevID_Previous as a foreign key.

Attachment 32895

**orange** · 03-05-2018, 11:54 AM

Since ranman gave the original code, I'll await his response. However, it is better for readers if you post all the code in your routine.

**hehrerh** · 03-05-2018, 12:04 PM

Originally Posted by orange

Did you include?

Code:

With rst

Originally Posted by orange

Since ranman gave the original code, I'll await his response. However, it is better for readers if you post all the code in your routine.

Still getting used to the tags. Updated with copy/paste code.

**davegri** · 03-05-2018, 12:51 PM

Look at ranman's code again. You have these 2 lines reversed:

Code:

With rst
Set rst = CurrentDb.OpenRecordset("YearSetup")

**hehrerh** · 03-05-2018, 01:05 PM

Originally Posted by davegri

Look at ranman's code again. You have these 2 lines reversed:

Code:

With rst
Set rst = CurrentDb.OpenRecordset("YearSetup")

I switched them back - still seems to append 0's to each 'Start' field.

Again - I don't see where in this code, it looks for the PREVIOUS record's end values. This code looks like it contains itself to copying/pasting values within the same record. I fully accept that I could be wrong on this - thoughts?

**davegri** · 03-05-2018, 04:50 PM

This might be closer. It doesn't take into consideration your compound keys or the year. Not enough info on your table layout for that...

Code:

Option Compare Database
Option Explicit


Private Sub Command0_Click()
Dim rst as DAO.Recordset
Dim saveStart_Dirt, saveEnd_Dirt
DoCmd.SetWarnings False
Set rst = CurrentDb.OpenRecordset("YearSetup")
rst.movelast
rst.movefirst		'get first rec 
	if isnull(rst.Start_Dirt) then
		msgbox "First record has blanks. I'm quitting.",vbokonly+vbCritical,"  DATA Screwed up "
		exit sub
	endif
   	saveStart_Dirt = rst.Start_Dirt
        saveEnd_Dirt = rst.End_Dirt
        rst.movenext
With rst
While Not .EOF
        If IsNull(.Start_Lots) Then
           .Start_Lots = saveStart_Dirt
           .End_Lots = saveEnd_Dirt
           .Update
        End If
        saveStart_Dirt = .Start_Dirt
        saveEnd_Dirt = .End_Dirt
        .MoveNext
   Wend
End With
Set rst = Nothing
DoCmd.SetWarnings True
MsgBox "done"
End Sub

**hehrerh** · 03-06-2018, 08:09 AM

Originally Posted by davegri

This might be closer. It doesn't take into consideration your compound keys or the year. Not enough info on your table layout for that...

Code:

Option Compare Database
Option Explicit


Private Sub Command0_Click()
Dim rst as DAO.Recordset
Dim saveStart_Dirt, saveEnd_Dirt
DoCmd.SetWarnings False
Set rst = CurrentDb.OpenRecordset("YearSetup")
rst.movelast
rst.movefirst        'get first rec 
    if isnull(rst.Start_Dirt) then
        msgbox "First record has blanks. I'm quitting.",vbokonly+vbCritical,"  DATA Screwed up "
        exit sub
    endif
       saveStart_Dirt = rst.Start_Dirt
        saveEnd_Dirt = rst.End_Dirt
        rst.movenext
With rst
While Not .EOF
        If IsNull(.Start_Lots) Then
           .Start_Lots = saveStart_Dirt
           .End_Lots = saveEnd_Dirt
           .Update
        End If
        saveStart_Dirt = .Start_Dirt
        saveEnd_Dirt = .End_Dirt
        .MoveNext
   Wend
End With
Set rst = Nothing
DoCmd.SetWarnings True
MsgBox "done"
End Sub

I've attached the database.

The table to be manipulated is 'YearSetup'.

Form1 contains a button that, when pressed, should:

1. copy Dirt_End and Lots_End from the first record.
2. navigate to the next next record, where Rep_YearID = Prior_YearID from the 'copied from' record in step 1.
3. paste the saved Dirt_End and Lots_End values into the Dirt_Start_Calc and Lots_Start_Calc fields of the current record, respectively.
4. repeat to step 1, copying Dirt_End and Lots_End from the current record.
5. loop until all records have been updated.

Thank you!

**davegri** · 03-06-2018, 01:21 PM

Database161-davegri.zip

You have 10 calculated fields in table YearSetup, which is the table being updated by your requested code. I couldn't analyze the effect of that on changes made to the table data via the code, but here's what I have...
I eliminated all the table fields in the query that weren't involved in the update logic. It was just confusing extra stuff to wade through that had no reason to be there at all.

**hehrerh** · 03-06-2018, 02:05 PM

Originally Posted by davegri

Database161-davegri.zip

You have 10 calculated fields in table YearSetup, which is the table being updated by your requested code. I couldn't analyze the effect of that on changes made to the table data via the code, but here's what I have...

I love you.

This works as far as I can tell! Time to load it up with the forecast data and see how it works. Really, I couldn't say thanks enough

Copy values from 2 fields in prior record set into 2 different fields in current record set

Thread Tools

Copy values from 2 fields in prior record set into 2 different fields in current record set

Similar Threads

Form with multiple entry fields, only fields with values created as new record in table

Using the current record to store fields into a new table

VBA to create a temporary copy of a record prior to deleting

Only Copy certain fields of a record and other fields from a form into a new record.

How to Copy a record to another table, prior to editiing

Posting Permissions