Need Combo Box to display value

[Chuckp]

I've tried to simple this down from the larger project I'm working on - this little problem effects a few similar areas in the project so I simplified it.

First table: tblEmployee which has fields of Employee_PK, FirstName, LastName, Active, Position and LDW which means last day worked.
Second table: tblBoatTrip which has fields of Trip_PK, TripDate, Boat and Captain_FK

The goal here is to be able to schedule say six boats a day and put a different captain on each boat but of course you can't have the same guy driving two boats on the same day.

In the frmBoatTrip, I have a combo box so that you can pick a captain - it's rowsource is a query that grabs the Employee_PK, makes a fullname of that guys name, checks whether he's an active employee, checks whether he's a Captain and finally it compares the employee LDW with the tripdate from the frmBoatTrip - this gives me a list of guys that are not working that day and it works perfect

So we have picked our guy, in the afterupdate of the combo box, I run an update query to change that guys LDW to the actual tripdate so when we try to schedule another boat for the same day, we can't pick him again - and again, this works perfect

At this point, as long as the form stays open, you can move back and forth between records of boat trips and the selected captain name stays visible within the combo box - all appears just fine BUT if you close the form and reopen it and move back and forth between records, the captains name is gone - his primary key was stored in the tblBoatTrip but you can't see his name on the form.

If you remove the date comparison from the combo box query, the name stays visible closing and reopening the form but that defeats the purpose of not wanting to select the same guy twice on the same day.

I can certainly just store the captains name in a text box and have a seperate combo box where you select the captain da da da but I really don't want the extra field on the form, I'd just like the one field, pick what you want and move on, but I need to be able to see what the name is when I open and close the form. There's gotta be a way that this can be done but my brain has just turned to mush trying so many things - Please help!!



I attached the simplified version here as a zip file, any help would be appreciated.

[Gicu]

Could you please see if that is what you want?

Cheers,

[Chuckp]

That works perfect - I had tried that in so many different ways but never on the "Enter" event - I limited in my experience and am trying to self learn. Sometimes it's really hard searching for answers because of the wording of your question. Thank you - I learned something very new to me!!

[Gicu]

You're very welcome!

Cheers,