Recall Value from Field

[SteveShelton]

Being a newbie, I am buildig a mileage db and need some help. I have a mileage table that has starting and ending odometer fields. As I enter the records for the entry I want the starting value of start odometer to prefill with the value of the last end odometer record.

Here is the code I tried.

Private Sub EndOdometer_AfterUpdate()
If Not IsNull(Me.EndOdometer.Value) Then
  StartOdometer.DefaultValue = Me.EndOdometer.Value
End If

End Sub

It did not work. Any help is appreciated.

Steve

[puppydogbuddy]

See this link:
ACC: Referring to a Field in the Previous or Next Record

[SteveShelton]

Thanks for the reply. I found this a couple of days ago and could not get this to work as well.

I will keep trying

Steve

[NeoPa]

Do you have the value you want in [EndOdometer]? Or is that in a separate record?

PS. Please note the edit comment about the tags.

[FishVal]

Hello, Steve.

Looks like the controls have date values.
If so, then you should add # delimeters to the string assigned to DefaultValue property.

See:
Repeating values for a field in several records
Problem with DateAdd and Default Value

[missinglinq]

Why would a field holding an odometer reading (mileage) hold a date? Presumably it would hold a numeric value, in which case the original code posted by the OP should work.

Note that this code will only work if you're entering multiple records for the same vehicle at the same time,i.e. if you enter one record, then move on to the next record and enter it and so on. If you enter one record for a given vehicle then close the form, then open it later to enter mileage for the same vehicle, the value will be lost. It that case you would have to use the DLookup() method given in the link PuppDogBuddy posted earlier.

Exactly where are you placing this code? In the code window or in the Properties Sheet?

Can you zip your file and post it here?

Linq ;0)>

[SteveShelton]

Thanks for the responses. Wow. I am on the road and will return this weekend. I will post a zip file when I return.

Thanks again

Steve

[SteveShelton]

DLookUp

I have tried on the form DLookup
=DLookUp([EndOdometer]","tblMileage", "[CarId]=Forms![frmMileage]![CarId]-1")

I placed this as the source for starting Odometer. I get an error and the form will not open

Steve

[SteveShelton]

I am not sure if this the correct lookup. It just gives error when the form opens

Steve

[NeoPa]

Do you have the value you want in [EndOdometer]? Or is that in a separate record?

PS. Please note the edit comment about the tags.

I'm still waiting on this if you'd like some direction.

[DonRayner]

hmmmm. I always have a little trouble with the dlookup function myself. Try this

=DLookUp("[EndOdometer]","tblMileage","[CarId] = " & "" & Forms![frmMileage]![CarId] & "")

Although I don't think that it's the DLookup() function that you need but rather the DMax()

If I assume that you select the [CarID] after starting a new record you could impliment it somthing like the following.

Private Sub CarID_AfterUpdate()
On Error goto ErrPoint

    Dim inLastReading as integer          ' use whatever name you want
    If Me.NewRecord then                     ' only run this code if it's a new record
        inLastReading = NZ(DMax("[EndOdometer]","tblMileage", "[CarId] = " & "" & Forms![frmMileage]![CarId] & ""))
    End If
    If Not IsNull(Me.StartOdometer) then ' Leave it alone if it's already filled in
        Me.StartOdometer = inLastReading
    End If

ExitPoint:
    Exit Sub

ErrPoint:
    MsgBox Err.Number & " " & Err.Description
    Resume ExitPoint

End Sub

[SteveShelton]

Here is a sample of the form and table

Steve

[DonRayner]

You can't put your statement in as the control source for your StartMiles text box. By doing this you will only be showing a calculated value that would never be added to the database. The control source needs to be the StartMiles field from tblMileage. Putting your expression in as the Default Value also won't work. Since the CarID field is not filled out when arriving at the new record it will return an #Error.

Best way to do this is to add your expression to the "After Update" event for the CarID textbox on your form.

The Dlookup() function will only return the first record it comes accross that matches your criteria so you need to use the DMax() function instead. It will find the maximum value that matches your criteria.

The VBA for the CarID "After Update" event would look like the following code. This code will update the StartMiles textbox with the largest EndMiles that it can find for the CarID that is entered on the form. If the StartMiles is already filled out it won't change the value.

Private Sub CarId_AfterUpdate()
On Error GoTo ErrPoint
  
    Dim inLastReading As Integer 
    If Me.NewRecord Then 
        inLastReading = Nz(DMax("[EndMiles]", "tblMileage", "[CarId] = " & """" & Forms![frmMileage]![CarId] & """"))
    End If
    If IsNull(Me.StartMiles) Then 
        Me.StartMiles = inLastReading
    End If
  
ExitPoint:
    Exit Sub
  
ErrPoint:
    MsgBox Err.Number & " " & Err.Description
    Resume ExitPoint

End Sub

Your table has the primiary key set to the CarID field. This won't work as it will only allow one entry into the database for each CarID. Change the Primary key to the ID field instead.

Also your table has "Date" as the name for one of your fields. Date is a reserved word in access and this is going to cause you problems further down the line as you develop your DB further.

[SteveShelton]

Excellent. I have already implemented most of what you stated and I working on the rest. I will give an update tomorrow.

Steve

[SteveShelton]

Thank You

That worked wonderfully. This has been a fun project of learning. Most of your comments on the previous post were right on as I had learned most of this the hard way. The zip file I sent was just quick example of what I was working with. It took me forever to figure out the CarID problem.

Here is a screen shot of where I am now.

thanks again

Steve