I feel like this shouldn't be the huge challenge that it's becoming, but after days for searching for an answer, here I am begging for help.
I have the following tables: tblTrip, tblStaff, tblExtPersonnel and tblTripStaffing. My main form is frmSite, with a subform called sfrmTrips. The subform also has a subform sfrmTripStaffing.
Now my issue. My Staff table is linked from another DB, managed by a different office. One field shows their status as Current or Former, so I need anything I create to allow me to filer out the former employees so they're not selectable by users.
I need users of this DB to be able to select the one or two staff members who will be going on each trip and add a Role (job they will preform on this trip) from a combo box. I don't want them to add staff members to tblStaff, but only to associate them to this trip. I'm sure that linking/associating will be done with the helper table - tblTripStaffing, but I can't figure out how to actually make it happen. Everything I've tried either says that I can't add records to the tblStaff table, which I shouldn't be able to do, or only allows me to select one person.
I will also add external people to each trip. These will be employees of other companies who will likely only be associated with one trip, but there could be exceptions. These people should be entered into tblExtPersonnel.
I will probably create a form, accessed by a botton on the Site form, which allows users open a review existing names to make sure the external person hasn't been entered previously. If they have, the user would somehow associate them to the current trip. If not, the user would enter the person and then associate them to this trip.
In an ideal world, I'd have one list of personnel, showing both internal and external people. Maybe through a union query. But, would that work to populate my subform? Would that mess up my helper table? Arragh! My 2020-addled brain hurts.
Thank you for any clarity you can provide. And let me know if I should provide more info.
I have the following tables: tblTrip, tblStaff, tblExtPersonnel and tblTripStaffing. My main form is frmSite, with a subform called sfrmTrips. The subform also has a subform sfrmTripStaffing.
Now my issue. My Staff table is linked from another DB, managed by a different office. One field shows their status as Current or Former, so I need anything I create to allow me to filer out the former employees so they're not selectable by users.
I need users of this DB to be able to select the one or two staff members who will be going on each trip and add a Role (job they will preform on this trip) from a combo box. I don't want them to add staff members to tblStaff, but only to associate them to this trip. I'm sure that linking/associating will be done with the helper table - tblTripStaffing, but I can't figure out how to actually make it happen. Everything I've tried either says that I can't add records to the tblStaff table, which I shouldn't be able to do, or only allows me to select one person.
I will also add external people to each trip. These will be employees of other companies who will likely only be associated with one trip, but there could be exceptions. These people should be entered into tblExtPersonnel.
I will probably create a form, accessed by a botton on the Site form, which allows users open a review existing names to make sure the external person hasn't been entered previously. If they have, the user would somehow associate them to the current trip. If not, the user would enter the person and then associate them to this trip.
In an ideal world, I'd have one list of personnel, showing both internal and external people. Maybe through a union query. But, would that work to populate my subform? Would that mess up my helper table? Arragh! My 2020-addled brain hurts.
Thank you for any clarity you can provide. And let me know if I should provide more info.