New form record set control to value of previous record other control

[misscrf]

I am starting this issue fresh, because I have tried many things. This is a car application. Track costs associated with many cars. Main form is cars. Subform is car costs. Any time someone enters in a cost type of gas. they enter in mileage. If it isn't gas, they don't enter it in. What I need to do is have an invisible field in the source data for car costs that will capture the previous mileage recorded. It would look like this for a car numbered car 1

Cost Date - Cost Type - Cost - mileage - previous mileage
1/1/01 - gas - $32.00 - 10,100 - 0
2/15/01 - oil change - $40.00 - (null) - 10,100
2/28/01 - gas - $34.99 - 13,258 - 10,100
3/12/01 - body work - $515.00 - (null) - 13,258

Make sense?

Don't ask me why they calculate it this way, but if they run a month of costs, they want the cost per mile, and they need the mileage that would come from the previous month's gas cost, because that mileage was the time at fill up, so difference needs to be figured into the cost for the next month.

Any help would be great.

Thanks!





misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You shouldn't even need to store the same number twice.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

You shouldn't even need to store the same number twice.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Why not use the DLookUp and DMax functions in the subform ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

I will try that PHV. My problem, dhookom, is that I have a report menu set up where they can choose a date range, a month, a year, etc. When I run the report I need the max mileage - the max mileage from just before the range. I am not sure how to accomplish that.

This issue is driving me nuts, so to speak. lol

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Misscrf,
If you can calculate this with DLookup() or other on the fly, you can create a query with the desired values. Since the results are displayed in a report they don't need to be updateable.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What is the actual SQL code of the report ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

I have it at home. I will have to get it and let you know. I agree with you D, I just have not been able to figure out how to calculate it on the fly. Partly because the mileage is not in every record. Only in cost records that are for gas. I think that is what is making this more complicated.

i will get the layout together and lay it all out for you.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Sorry this took so long the following is the code for the form, where a user can choose 1 or more cars from a multi-select listbox and then a date option from a combo box (with month-year options) or two text boxes for date ranges.

Private Sub cmdOpenReport_Click()
On Error Resume Next

Dim strCriteria As String
Dim strReport As String
Dim strQuery As String
Dim Mysql As String

strReport = "rptCarStats"

strCriteria = "1=1 "
strCriteria = strCriteria & _
BuildIn(Me.lstCar, "[PKCarID]", "")

If Me.ChkYearToDate = 0 Then
strCriteria = strCriteria
Else
strCriteria = strCriteria & _
 " AND Year([DtCostDate]) = " & Year(Date) & _
 " AND Month([DtCostDate]) <= " & Month(Date)
 End If

If Me.cboMonthYear.Value = "" Then
strCriteria = strCriteria
Else
strCriteria = strCriteria & _
" AND Month([DtCostDate]) = " & Month(Me.cboMonthYear) & _
" AND Year([DtCostDate]) = " & Year(Me.cboMonthYear)
End If

Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

If Len(Me.dtRangeBegin & vbNullString) = 0 Then
strCriteria = strCriteria
Else
strCriteria = strCriteria & _
" AND ([DtCostDate]) >= " & Format(Me.dtRangeBegin, conJetDate)
End If

If Len(Me.dtRangeEnd & vbNullString) = 0 Then
strCriteria = strCriteria
Else
strCriteria = strCriteria & _
" AND ([DtCostDate]) <= " & Format(Me.dtRangeEnd, conJetDate)
End If

DoCmd.OpenReport strReport, acViewReport, , strCriteria


End Sub

This is the sql for the report control source:

SELECT tblCar.PKCarID, tblCarNum.intCarNum, tblCarStatus.txtCarStatus, tblCarMake.txtCarMake, tblCarModel.txtCarModel, tblCarCostType.txtCarCostType, tblCarCost.DtCostDate, tblCarCost.intGallons, tblCarCost.CurCostAmount, tblCarCost.intMileage, IIf([txtCarCostType]="Gas",[intGallons],Null) AS MyGallons, tblCarCost.PKCarCostID, tblCarCost.FKCostType, IIf([tblCarCost]![intGallons]>0,[tblCarCost]![CurCostAmount]/[tblCarCost]![intGallons],Null) AS MyAvgGal, IIf([tblCarCost]![FKCostType]<>6,Sum([tblCarCost]![CurCostAmount]),0) AS CostNoBod

FROM (tblCarStatus RIGHT JOIN (tblCarNum RIGHT JOIN (tblCarModel RIGHT JOIN (tblCarMake RIGHT JOIN tblCar ON tblCarMake.PKCarMakeID = tblCar.FKCarMake) ON tblCarModel.PKCarModelID = tblCar.FKCarModel) ON tblCarNum.PKCarNumID = tblCar.FKCarNum) ON tblCarStatus.PKCarStatusID = tblCar.FKCarStatus) LEFT JOIN (tblCarCostType RIGHT JOIN tblCarCost ON tblCarCostType.PKCarCostTypeID = tblCarCost.FKCostType) ON tblCar.PKCarID = tblCarCost.FKCar

GROUP BY tblCar.PKCarID, tblCarNum.intCarNum, tblCarStatus.txtCarStatus, tblCarMake.txtCarMake, tblCarModel.txtCarModel, tblCarCostType.txtCarCostType, tblCarCost.DtCostDate, tblCarCost.intGallons, tblCarCost.CurCostAmount, tblCarCost.intMileage, IIf([txtCarCostType]="Gas",[intGallons],Null), tblCarCost.PKCarCostID, tblCarCost.FKCostType, IIf([tblCarCost]![intGallons]>0,[tblCarCost]![CurCostAmount]/[tblCarCost]![intGallons],Null);

If you have any suggestions of how I can get the mileage for any given range, pulling the mileage from as just before the range chosen. I get tired just trying to explain it! lol

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot