@Villeroy it could very well be that my database and forms are still improperly set up. I’m very fresh at this and truly I appreciate any suggestions and criticism.
I’ll try to justify my requirement below, but if you still think this is a wrong approach, I’m really open to suggestions.
I thought it would be logical to create my data tables in a way that will as closely represent the real world environment as possible. I’d imagine that professional database architects have precisely the same approach. Thus, I created a set of interconnected tables which describe people, companies, and subsequently I’ll do the same thing for quotation of raw materials, estimates, purchasing, production, invoicing, scheduling, marketing, pricing analysis and the list goes on.
Some of the people in this database do actually work for various companies (commercial clients or suppliers) that we deal with. Some of our suppliers are also our clients at certain times as well. So it gets a little bit tricky. Nonetheless, I set up my people data entry form in such way that they can have several physical addresses, phone numbers and email addresses associated with them. Essentially, I can have a client who is building themselves a house, but currently lives at a different address and wants the invoicing to be sent to the latter. Or I can have another client who is employed by one of the companies that we deal with. At which point they may have two mobile numbers (one private and one for work - happens all the time), same goes for email.
When it comes to the company data entry form, on it I have a subform which associates folks with that place of business. That subform holds a grid with four columns (listboxes): 1, first and last name, 2. department, 3 phone number and 4 email address. In the latter two listboxes I wanted to be able to list only the phone numbers and emails of that particular person and only those which are for business purposes. And this is precisely why I posed that question. I simply could not find a way of making SQL aware of the row selected in the “first and last name” listbox (the first one).
Please see pictures below for visualization of what I just described: