Restricting LOV's

[smjohns]

Hi,

I am completely new to development (I am a software tester by trade) and have hit a smallish problem, whilst creating a screen on an Access2000 DB. I want to limit a field's drop down list based on a three criteria:

Firstly I need to extract all Reservation ID's off the reservation table. I then need to restrict this list by the following:

Reservation_Status = 'CT'
Transport_ID = Transport_ID entered on an earlier field (on current screen
The Reservation ID must not exist on another table.

This is the SQL that Access has created:

SELECT Reservations.Reserve_ID, Reservations.Reserve_Status_ID, Reservations.Transport_ID

FROM Reservations

WHERE (((Reservations.Reserve_ID)<>[Payments]![Reserve_ID]) AND ((Reservations.Reserve_Status_ID)="CT") AND ((Reservations.Transport_ID)=[Forms]![Transport_Company_Payment_Record_Add]![Transport_ID]));

The SQL is not bringing back no values.....doh. I think it has something to do with the bold bit above.

In english my statement would read something like.

Display all Reserve_ID's from the reservation table, where Reservation Status = 'CT', Transport ID = (Transport ID entered on the above field) and where the Reserve_ID does not appear on the Payments table (Reserve_ID column).

Is this possible within access? If so, what do I need to change to get this to work.

Thanks again for any help you lot are able to provide.

Cheers

Simon

 

[smjohns]

Thanks, that worked a treat and I ended up with a script like the following:

SELECT Reservations.Reserve_ID, Reservations.Reserve_Status_ID, Reservations.Transport_ID
FROM Reservations LEFT JOIN Payments ON Reservations.Reserve_ID = Payments.Reserve_ID
WHERE (((Reservations.Reserve_Status_ID)="CT") AND ((Reservations.Transport_ID)=[Forms]![Transport_Company_Payment_New_Invoice]![Transport_ID]) AND ((Payments.Reserve_ID) Is Null));

Thanks for all your help

Simon