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.