Vehicle Mileage Database

[Mtyetti2]

I am have been stuck on how to log vehicle mileage for a database which includes several vehicles. I have a 'vehicle' table, and an 'event' table. My Event table populates with all the needed items, but what I end up with is a table with mixed vehicles and mileages. What I would like is to be able to have a text box on my Vehicle form which obtains the latest mileage (hence the most current) for that particular vehicle. Should this be done through a query? Can a query result populate a text box? I hope this makes sense, as I am fairly new to this forum. Thank you so much!

[alansidman]

I think that you can do this with an unbound text box on your form employing the Dmax or Dlookup functions. Here is a link on the syntax
http://www.techonthenet.com/access/f...omain/dmax.php
http://www.techonthenet.com/access/f...in/dlookup.php

[ssanfu]

I would tend to use VBA and push the mileage into the text box.

Your question got me looking and I found a MDB (A2K) I made in 2001 (ish). Only the 1985 has data. I was trying to learn and keep track of miles per gallon.

OK, get back up in the chair and quit laughing

[Mtyetti2]

alansidman,

I think DMax may be my answer. Here's what I have, but I'm struggling with the 'criteria' portion.

TxtBox = DMax("TachIn", "Event", "VehicleID = [Forms]![Event]![Vehicle]")

I think that should give me the maximum Tach number, which would be what I want. When I run that, it flashes (like it's constantly calculating) and shows #Error. I need the expression to know which vehicle I'm selecting from an associated combo box, but I'm stuck on how to do that. Thanks so much!

[alansidman]

Is your control Vehicle in the form Event, a string value or a numerical value? This will make a difference. If it is a string value, then you will need to enclose that in single quotes.

TxtBox = DMax("TachIn", "Event", "VehicleID = '[Forms]![Event]![Vehicle]'")

http://msdn.microsoft.com/en-us/library/office/aa172181(v=office.11).aspx

[DHOW]

I'm having similar trouble in designing my Vehicle Expense datbase.

I have a mileage table that holds the vehicle names and ending mileage and begining mileage. Im trying to create a form where the Driver only has to enter the ending mileage and the begining mileage will auto populate after he selects the vehicle.

I decided to enter the code in the form for the control source for begining mileage text box. Using this code:
=DMax("[Mileage]![Ending Mileage]","[Mileage]","[id]= & '[Forms]![mileage details]![vehicle]'")

When i open it form view the text box already has the "#Error" and after selecting a vehicle it blinks and returns the value "#Error" again and this goes for each vehicle.

Not sure what i need to change to allow the driver to come in see a blank form then select a vehicle and the form puts in the last mileage recored for the vehicle selected.

Any help?

[ssanfu]

Welcome to the forum, DHOW.

First a word on etiquette. You have "Hi-jacked" this thread by posting your question in a thread started someone else. You should start your own thread and reference this thread (or one that is similar - if there is one).


Soooo.....
You have this in the control source of a control:

=DMax("[Mileage]![Ending Mileage]","[Mileage]","[id]= & '[Forms]![mileage details]![vehicle]'")

1) If the vehicle ID is a number, the syntax should be:

=DMax("[Ending Mileage]","[Mileage]","[id]= " & [Forms]![mileage details]![vehicle])

2) Putting this in the control source property of a control will only display the value on the form. The control will be unbound - the previous ending mileage will NOT be saved in the table.
3) To save the previous ending mileage, I would have code in the form before update event to push the previous ending mileage into a hidden control bound to the beginning mileage field.
The code would look something like

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Me.BeginMileage = DMax("[Ending Mileage]", "[Mileage]", "[id]= " & [Forms]![mileage details]![Vehicle])
End Sub

where "BeginMileage" is the name of the control bound to the beginning mileage field.


Or you can take a look on how I set up my mileage mdb.......see my previous post.( shameless plug)