help with combo box in continuous subform!!

[rsbutterfly16]

hi guys i have a main form called Passengers coming from tblPassengers table and then it has a subform to select the destinations for each passenger.

My subform (continuous) consist of entering up to 3 destinations for each passenger so i created a combo box with this row source:

SELECT DestinationID, Destination FROM tluDestination
WHERE DestinationID NOT IN(SELECT DestinationID FROM tblDestinations WHERE PassengerID = Forms!formname!txtPassengerID);

The reason is that i want that when the user enters a destination, when they go to select another one , the selected one is excluded from the list. but it does not happen... can someone help me with this , i am going nutss
i attached the db to see if anyone can help...

 

[macca the hacke]

Try the following:

SELECT DISTINCTROW tludestination.destinationid, tludestination.destination FROM tludestination WHERE (((tludestination.destinationid)<>[forms]![formname]![txtPassengerID]));

I assume that txtPassengerID is the name of the first combo box.
You may need to put me.requery on the after update of first combo box

 

[rsbutterfly16]

thanks for helping, i tried your way but same thing no errors but when i go to the next one the one selected is not excluded. txtPassengerID is a text box in my main form. My continuous subform contains 1 combo box (cmbDestinations). In my subform, I also put in the after update event of the cmbDestinations to requery, and in the lost focus event also to requery. Anything else i could try???.. .. i have been trying to solve this for days....

 

[macca the hacke]

I didn't realise it was a text box on another form.
So your issue is that the user inputs a value into the text box on the main form.
You then have a continuous subform where the user selects new values from a combo box. This combo box value should exclude the value from the original text box??

 

[rsbutterfly16]

no , the user enters a value in the txtPassenger text box in the main form. then they go to the subform and select a Destination in the combo box. Since it is a continuous subform with one combo box, then when you select one then the combo box appears again and again to enter many Destinations. So what i want is that when a passenger is entered and the user enters a destination, and they go to the second one, the selection in the first one (in the same combo box) is excluded.

One passenger can have up to three destinations.

 

[rsbutterfly16]

dont' worry , i finally figure it out, i just had to add the requery to the after insert event... woohoooo!!!!!!!!! finally