Dlookup question

[Guest]

Hi all

I want to return the 'current' car assigned to a member of staff in an
expenses form if mileage is selected in the description field.

The Table tblVehicles holds all the car details with the field SpecID
containing who owns the car and StartDate being the date they first used the
car. Some members of staff can have two cars to choose from which are still
current but I set the StartDate of the less popular car to be older than the
most popular so it always defaults to the more popular one.

I created a totals query called qry Current Vehicle, using tblVehicles
returning Max of StartDate, Last of RegNum and Last of VehicleID (named car).

I have got it to work by using a dlookup function in vba (dlookup"car","qry
Current Vehicle") but I just want to check if there is a better/easier way?

Thanks in advance for any help.
Sue

 

[Wayne Morgan]

My recommendation would be a combo box or list box where the Row Source
query would return each car the person has. You could then select the
appropriate car from the list. You could set the first item in the list to
be selected automatically so that if a person only has one car then the car
will be selected and you can skip the field.

 

[Guest]

Thanks, already have this anyway (except the first in list being
automatically selected - how would you do this?). Trouble is, its the person
with more than one cars who does not want to have to choose his car each
time. He uses the same one most of the time and occassionally uses a
different one. Not sure how I could get his main car selected automatically
this way.

Thanks in advance for any help.
Sue

 

[Wayne Morgan]

To get the main car selected, you could have a field in the cars table that
ranks the cars by preference then sort on that field in the Row Source. This
will place the favored car at the top of the list. To automatically select
the first item in the list, in the form's Current event try

If Me.List1.ListCount >= 1 Then
Me.List1 = Me.List1.Column(0, 0)
End If

Where the first 0 would be the number of the Bound Column -1 (the number in
the Column property is zero based). If you have a header row (titles above
the columns in the list) then check for ListCount >= 2.

 

[Guest]

Thanks Wayne.

Sue

To get the main car selected, you could have a field in the cars table that
ranks the cars by preference then sort on that field in the Row Source. This
will place the favored car at the top of the list. To automatically select
the first item in the list, in the form's Current event try

If Me.List1.ListCount >= 1 Then
Me.List1 = Me.List1.Column(0, 0)
End If

Where the first 0 would be the number of the Bound Column -1 (the number in
the Column property is zero based). If you have a header row (titles above
the columns in the list) then check for ListCount >= 2.