Search code examples
ms-accessmany-to-manysubform

Access: How to implement joining a contact to a trip?

(Table Relationships)

I'm developing a travel agency database, and I've got a table for contacts (people), a table for trips, and a junction table, people_has_trips. "Trip" refers to a table whose identity is mostly many-to-many relationships with various optional travel elements such as hotel, flight, etc. (I have doubts about this structure, but that's an issue for another question).

Anyway, I'm in the early phases of designing the form for Trips, which will almost entirely consist of subforms linking to junction tables. The problem that's given me the most difficulty so far is that of how to add a contact to a trip. I'm fairly new to access, and thus far I've been populating such tables using autofilling comboboxes, but the contacts table contains over 100,000 records, and as such some sort of search function would be prudent.

Here's what I have so far. This subform is based on the people_has_trips table, and currently is limited to showing existing records. I disabled all fields as not to allow the editing of People records, but I'm somewhat stumped as to how to handle adding more people. In my limited Access knowledge, the most logical solution to me seemed to be having a button to open a form that would allow the searching of People and perhaps the addition of new people.

Now, I have a fully realized form for entering new People (shown below), but it doesn't have any real search functionality besides Access's built-in filters. So my subform currently opens People and... does nothing else. Quite frankly, I have no idea where to go from here. Do I develop a second form for searching people, or do I build in search functionality to the primary People form, something I should probably do anyway? If so, How? And after doing either of those things, how do I then populate people_has_trips? The only answer I can think of involves the user clicking the button to open People, finding the person they want to add, and then manually entering the primary key into the subform, which surely isn't optimal.

The aforementioned People entry form

The solution, I would wager, is VBA commands. I've recently taken the time to learn some VBA, and I'm a bit more comfortable with it, but I just don't know where to begin with this problem. I'm confident that I can adapt and apply other people's VBA ideas and code, but I can't find a thread on this particular issue from which to do that! I'm guessing the reason I can't find any help for this issue is that it's so blatantly obvious that nobody has asked the question yet, or my Google skills are lacking. Either way, I'd be quite happy to facepalm in realization of the solution. Thanks any and all for their help!

Solution

  • You need to be focused on your core problem and as far as I understood, you want to search and add "users/contacts" to your "People on this trip" form.

    Yes combo-boxes can eat up time loading 100k rows and it's not really search friendly.

    In your "People in this trip" form you can:

    have a text box => for search
    have a combo-box => showing search result
    [Search] [Add] [Clear] buttons

    In theory,

    1. search button performs a search on your TContacts table and assigns the results to the combobox.
      something like SELECT PersonId, FirstName, LastName From Prople WHERE firstName like @keyword OR lastNAme like @keyword ... other fields
    2. User selects a specific result form the combo-box.
    3. Add button then performs SQL operation adding selected user to your junction table and refresh your junction table.
      something like: INSERT INTO people_has_trips (people_personId, trips_tripId) values (@selectedPersonId, @tripId); followed by requery

    This is just a theory, you need to try out something and narrow it down to a specific problem. Otherwise your question might be marked as too broad.