Solved: New Record - Auto Fill Fields

[JustJerry]

I am trying to have three fields in a NEW record be automatically filled in from the previous record, and I can not figure out how to accomplish this yet.

I want [TruckID] to be filled in with the same value from the previous record.
I want [StartMile] of the NEW record to have the value of [EndMile] of the previous record. And...
I want [Month] of the NEW record to advance one month from the previous record.

I have searched online, and found some material, but not understanding it all, I am hoping someone here can help me out.

Much thanks in advance.

Jerry

[Norie]

Jerry

Where are you actually trying to do this?

In a form or a table/query?

By the way do you really need to repeat the data?

[JustJerry]

Hi Norie,


Where are you actually trying to do this?

I have a form that we are using to fill in data. I have a button on that form, that when clicked, will advance to the next record and thus, fill in the data I mentioned.


By the way do you really need to repeat the data?

As we are filling in mileage data for one vehicle, and at the end of the month, I was just trying to have a button that would prefill data for the next month for that specific vehicle. The ending mileage for the month is the starting mileage for the next month..etc.

[geekgirlau]

The concept is as follows:

In the On Click event for your button, capture the values for TruckID, EndMile and Month
After adding the new record, populate TruckID with the value you captured earlier, StartMile with the EndMile value, and Month with Month+1
If you need more explicit help, try to attach a sample copy of your database - strip out all unnecessary tables, queries, forms etc. along with any sensitive data, and zip it up.

[JustJerry]

Hello Geekgirl,

Just got your message. I am travelling on business at the moment, and will attempt your suggestion as soon as I can.

Thank you for the reply.

[JustJerry]

This is what I came up with after searching online, and seems to work for what I needed.

Private Sub FinalizeMonth_Click()
On Error GoTo Err_FinalizeMonth_Click
'Set variables of current truck record for next month
Dim ctruck As Variant
Dim cmile As Variant
Dim cstate As Variant
Dim cmonth As Date
Dim nmonth As Date

'Assign values to variables
ctruck = Me.TruckID.Value
cmile = Me.EndMI.Value
cstate = Me.EndST.Value
cmonth = Me.MonthYr.Value
nmonth = DateAdd("m", 1, cmonth)

DoCmd.GoToRecord , , acNewRec

'Fill in fields of new records with previous months values
Me.TruckID.Value = ctruck
Me.BeginMI.Value = cmile
Me.BeginST.Value = cstate
Me.MonthYr.Value = nmonth


'Save new Record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_FinalizeMonth_Click:
Exit Sub
Err_FinalizeMonth_Click:
MsgBox Err.Description
Resume Exit_FinalizeMonth_Click

End Sub