Dlookup - Test if record exists in table.

[kevinpreston]

Hi, new to the forum but struggling with what is probably simple to sort!



I have a booking in form set up for work required on vehicles/trailers.
The fields in question are vehicles.regnumber and vehicles.fleet

The main ID is regnumber, I have this set to check the vehicles table to ensure that a matching record exists, I use the following dlookup

If IsNull(DLookup("[regnumber]", "[vehicles]", "[regnumber]= '" & Me.RegNumber & "'")) Then
MsgBox "Vehicle Record does not exist - Enter Vehicle details"

This works fine for vehicles, the problem I have is with trailers, in the vehicles table a trailer has a Reg number and Fleet number.

99% of the time the trailer will be booked in using it's fleet number, what I need to be able to do is:

When I enter the fleet number into the booking form I need it to check both the vehicles.regnumber and the vehicles.fleet fields to see if a record exists for that trailer.

Sorry if that was a bit long winded but I am trying to avoid having to answer questions as to why this or that etc.


kevinpreston

[rpeare]

If dcount("*", "[vehicles]", "[regnumber]= '" & Me.RegNumber & "'") = 0 AND dcount("*", "[vehicles]", "[fleetnumber]= '" & Me.FleetNumber & "'") = 0 Then

just be aware you are treating your regnumber and fleet number as strings (not numbers) with this expression

[kevinpreston]

Thanks for reply.

For info: regnumber for vehicle could be = aa01zzz or any combination of 7 characters/numbers
Trailer numbers are: regnumber = C123456. Fleet could be any combination of upto eight characters/numbers.

On my booking form I have 1 field for regnumber, I either enter vehicle regnumber or fleet number.

my dlookup checks the vehicles table fine and correctly if I put the trailer reg number in, if I put the fleet number in it rightly tells me the record doesn't exist.

Looking at your dcount code I would expect it to do the same, as regnumber would return 0 and fleet would return 1

Unless I am reading it wrong!

[kevinpreston]

rpeare, please accept my apologies, your reply worked perfectly and is doing exactly what I wanted it to do.

Many thanks

kevin preston.