Subform for selecting multiple Employees to Associate with Project (1 Viewer)

MLBall

New member
Local time
Today, 08:29
Joined
Jul 24, 2020
Messages
6
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.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
14,544
Hi. Welcome to AWF!

A union could work, but I'm afraid it would mess up with your primary keys. I imagine there could be overlaps between internal and external people.

I would probably just use two separate subforms.
 

mike60smart

Registered User.
Local time
Today, 07:29
Joined
Aug 6, 2017
Messages
727
Hi
I think you would need :-
1. a table to record The Trips
2. a Table to record the many Staff on the Trip
3. a table to record the ExternalPersonnel on the Trip

The DbGuy just beat me to it but we are both talking from the same music sheet
 

MLBall

New member
Local time
Today, 08:29
Joined
Jul 24, 2020
Messages
6
Hi. Welcome to AWF!

A union could work, but I'm afraid it would mess up with your primary keys. I imagine there could be overlaps between internal and external people.

I would probably just use two separate subforms.
Thank you for the super-speedy reply! No. There won't ever be overlaps between internal and external people. They are definitely either one or the other, though they will combine in various combinations on trips.
 

MLBall

New member
Local time
Today, 08:29
Joined
Jul 24, 2020
Messages
6
Hi
I think you would need :-
1. a table to record The Trips
2. a Table to record the many Staff on the Trip
3. a table to record the ExternalPersonnel on the Trip

The DbGuy just beat me to it but we are both talking from the same music sheet
Thanks for the quick reply. I have those three tables. I just don't know how to set up the (I assume) subform to allow users to select which staff member(s) to attach to each trip and then add new or select existing external people to add to the trip.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
14,544
Thank you for the super-speedy reply! No. There won't ever be overlaps between internal and external people. They are definitely either one or the other, though they will combine in various combinations on trips.
Hi. I was referring to overlaps in primary key values. I mean there could be ID = 1, for example, in both internal and external tables.
 

MLBall

New member
Local time
Today, 08:29
Joined
Jul 24, 2020
Messages
6
Right now I have tblTrip - tblTripStaff as a one-to-many relationship and both tblStaff and tblExtPersonnel as a one-to-many relationship with tblTripStaff. I think that's wrong, but not my entire issue.
 

MLBall

New member
Local time
Today, 08:29
Joined
Jul 24, 2020
Messages
6
Hi. I was referring to overlaps in primary key values. I mean there could be ID = 1, for example, in both internal and external tables.
Ahh. Good point. Though, I don't think so. Because I'm just using an autonumber for external people and we're using the 'last 4' of SSN for internal people. Can I get my autonumber to always be a 5-digit number, just in case? Or is the Union query not really worth it?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
14,544
Right now I have tblTrip - tblTripStaff as a one-to-many relationship and both tblStaff and tblExtPersonnel as a one-to-many relationship with tblTripStaff. I think that's wrong, but not my entire issue.
If you stick with that design, I imagine you might need an extra field in tblTripStaff to designate internal or external staff.
 

MLBall

New member
Local time
Today, 08:29
Joined
Jul 24, 2020
Messages
6
If you stick with that design, I imagine you might need an extra field in tblTripStaff to designate internal or external staff.
OK. That's easy enough. But I still don't understand how to populate tblTripStaff through the subform. At this point, every time I try to select someone, I just get a BING sound, without any indication why.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
14,544
OK. That's easy enough. But I still don't understand how to populate tblTripStaff through the subform. At this point, every time I try to select someone, I just get a BING sound, without any indication why.
Oh, I forgot, UNION queries are "read only." Sorry...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:29
Joined
Feb 19, 2002
Messages
31,476
I've attached a sample database for working with many-many relationships. It is very similar to what you need.

I would recommend using two separate tables for the two types of staff but ONLY because you do not have control over one of them because it is in a different database. You can use union queries for reports if you want to have the staff in a single subreport. On forms, use two subforms. One for each type since as you have discovered the union query is not updateable.

This sample includes an Active flag and there is code behind the forms that prevents you from adding an inactive person to a class or from adding an active person to an inactive class.
 

Attachments

  • ManyToMany20201007.zip
    1.5 MB · Views: 34

mike60smart

Registered User.
Local time
Today, 07:29
Joined
Aug 6, 2017
Messages
727
OK. That's easy enough. But I still don't understand how to populate tblTripStaff through the subform. At this point, every time I try to select someone, I just get a BING sound, without any indication why.
Hi

Can you upload a zipped copy of the database?
 

Cronk

Registered User.
Local time
Today, 16:29
Joined
Jul 4, 2013
Messages
2,487
I wonder why the people who are external have to have a separate table rather than being in the same table as Staff, with an additional field to indicate Staff or External.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:29
Joined
Feb 19, 2002
Messages
31,476
I wonder why the people who are external have to have a separate table rather than being in the same table
My Staff table is linked from another DB, managed by a different office.
Because the internal people table is not in THIS application. It is in a DIFFERENT application. That restricts what you can modify in that table. If you added a new class of people, you would actually have to modify the OTHER application to ignore them.
 

Users who are viewing this thread

Top Bottom