Does a Record exists in a table

[paul.raeburn]

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

 

[rainman89]

have you tried using dlookup to do this?

 

[paul.raeburn]

I have not, but I will have a go...

is this it...

=DLookup("[Travel_from]", "[travel_places]","[travel_from]='" & Forms![form_name]![form_field_from] &Forms![form_name]![form_field_to "'")

how do I get both fields to be checked?

regards - PR

 

[rainman89]

you would want

"[travelfrom]="'"&& Forms![form_name]![form_field_from] &"'" AND [travelto]="'"&& Forms![form_name]![form_field_to]"'"

Check this out

then you would want something like

if nz(yourdlookup,"")<>"" then
run your statement

 

[paul.raeburn]

Ray,
thanks for this... but I cannot see how it looks both fields up in the table... with what Criteria...

=DLookup("[Travel_from]", "[travel_places]","
"[travelfrom]="'"&& Forms![form_name]![form_field_from] &"'"
AND
[travelto]="'"&& Forms![form_name]![form_field_to]"'

I have looked at the link, which does explain dlookup, but cannot see where you look up two fields from a form in a table.

Regards - Paul

 

[rainman89]

oh im sorry i misunderstood.

Basically what you would do is lookup something in your table, not the travel to or from, say travelid or whatever and if the criteria of travel to and from are met, it will return the travelID

you then would check that travel ID and if it returns soemthing other than "" <a blank> then a record would exist for those travel from and to dates and therefore you can let them know that

 

[paul.raeburn]

Ray,
sorry I may not explain it correctly...

I have a table with all the current places that people travel from to with a distance...

When a user uses the application their can pick where their travel from and to and entered a date... this is saved to a table called "travel_date"

I want to check that there is a record of the travel from and to that the user has entered in the source table called "travel_places" because this could be a new location that their have travel to or from, this will need to be update to the table...

sorry for the confusion...

Regards - Paul

 

[paul.raeburn]

here is what I am trying to do...

=DLookup("[tbl_Travel_from]", "Employees", "[EmployeeID] =
" & _ Forms![FormName]![ctl_LastName])

so from the above I would like to check on the:

tbl_Travel_from and tbl_Travel_to against ctl_Travel_from and ctl_Tarvel_to

Regards - Paul

 

[rainman89]

Sorry for still being confused but.... I think well get it

Ray,
I want to check that there is a record of the travel from and to that the user has entered in the source table called "travel_places"

So you want to check the travel places table for dates that the person is entering into the new form?

are these tbl_travel_to and tbl_travel_from actual tables or are they fields in your table?

=nz(DLookup("[Travel_placesID]", "[travel_places]","[travelfrom]="'"&& Forms!form_name]![form_field_from] &"'"AND[travelto]="'"&& Forms![form_name]![form_field_to]"',0))

will return a value if the record exists with that travel from and travel to entries, otherwise it will return 0 stating that the record does not exist in the table

Hope this clarifies

 

[paul.raeburn]

rainman,
you are correct these are fields in a table...

when I use this I get 2 errors one is ' = nz ' expected line number... and the other is a expected list separator at ' "'"&& '

regards - Paul

 

[rainman89]

Oh that was just an example.

You would have to do something like

if nz(DLookup("[Travel_placesID]", "[travel_places]",""[travelfrom]="'"& Forms![form_name]!form_field_from] &"'"AND [travelto]="'"& Forms![form_name]!form_field_to]"'"),0) <>0 then
msgbox "A record Exists"
else
msgbox "A record Does not exist for these travel locations"
end if

You should be able to take it from there
just make sure you have

" ' " around the form fields

 

[paul.raeburn]

rainman,
again thanks very much for this information, I still get an error:

expected list separator at ' ",""[travelfrom]="'"& '

many thanks
Paul

 

[rainman89]

I dont think you have it exactly like i have. look again

 

[paul.raeburn]

rainman,
I cannot see the difference

here is mine:
Code
............
if nz(DLookup("[Travel_placesID]", "[travel_places]",""[travelfrom]="'"& Forms![form_name]!form_field_from] &"'"AND [travelto]="'"& Forms![form_name]!form_field_to]"'"),0)<>0 then msgbox "A record Exists"
...........

regards - Paul

 

[rainman89]

if nz(DLookup("[Travel_placesID]", "[travel_places]","[travelfrom]= '"& Forms![form_name]!form_field_from] & "' AND [travelto]=[U][COLOR=Red][/COLOR][/U] '"& Forms![form_name]!form_field_to]"'"),0)<>0 then msgbox "A record Exists"

I didnt even look what i sent you. I just copied and pasted what i had before! sorry about that. This SHOULD work. I hope

 

[paul.raeburn]

rainman,
thanks for your patience...

I have used the last code you sent, but I get an error now at...

expected list separator at ![form_field_to]"'"),0)<>0

 

[MSAccessRookie]

rainman,
thanks for your patience...

I have used the last code you sent, but I get an error now at...

expected list separator at ![form_field_to] & "'"),0)<>0

I think you are missing a "&" character

 

[rainman89]

I think you are missing a "&" character

correct