Check Records exists

[PR]

I would like to check that a record exists in a table when a user enters
data into 2 fields on a form...

it is travel database, it has a table called "travel_places" which has 3
fields called "travel_from", "travel_to" and "distance" on the from the user
has 2 drop down combo boxes, the user choices the places here has travel
from and to and is written to a table called "travel_dates"...

What I would like to happen is, once the user updates the 'to' combo box
that the record is check to see if the details entered exists in the
"travel_places" table if not present the user a message box stating that the
record does not exist... and allow the user to update the table with the
record including the distance...

is this possible...

Regards - Paul

 

[Clifford Bass]

Hi Paul,

It is possible. Use an After Update event on the second combo box. In
that event use the DLookup() function:

Dim varDistance As Variant

varDistance = DLookup("distance", "travel_places", "travel_from = """ &
Replace(cbTravel_From, """", """""") & """ and travel_to = """ &
Replace(cbTravel_To, """", """""") & """")
If IsNull(varDistance) Then
' Prompt user to enter information
Else
txtDistance = varDistance
End If

Where cbTravel_From is the name of the combo box where the user enters
the travel_from and cbTravel_To is the travel_to combo box and txtDistance is
the distance text box. I am assuming that travel_from and travel_to are
text. If they are numbers (i.e. location codes) use this instead:

varDistance = DLookup("distance", "travel_places", "travel_from = " &
cbTravel_From & " and travel_to = " cbTravel_To)

You also could use an After Update event on the first combo box in case
that is changed after the second one. In either case you should probably
check them both to see if they actually have values before doing the
DLookup().

Hope that helps,

Clifford Bass