VBA Joining variable number of selects into union all based on list box selection(s)

[KNobel]

Hi-
I have a contact table in Access 2021 that holds contact emails and the various distribution lists they are subscribed to. I'd like to make a "List Generator" form where a user can select 1 or more distribution list name(s) from a list box then hit a button to generate a list containing the Email_address and Dist_List name(s) to be exported to Excel. The list box names correspond to the distribution list field names in the contact table.



Below is the select query that would run for each selection in the listbox:

SELECT t_Contacts.Email_Address, "Dist_Newsletter" AS Dist_List
FROM t_Contacts

WHERE (((t_Contacts.Email_Address) Is Not Null) AND ((t_Contacts.Dist_Newsletter)="Subscribed"))

I need help creating the VBA to 1) set the list box selection as a variable to be inserted into the SQL where it is bold/red, and 2) combine all the selects into one big UNION ALL query. I just have no idea how to do it LOL. Any help would be so appreciated from this noob. Thanks in advance!

[madpiet]

I think this is the article you're after:
Forms: Use Multi-Select List boxes as query parameters (theaccessweb.com)

you basically grab each selected item (newsletter or whatever) and do something with it. I suppose you could cheat and just use a pattern like"

EmailRecipient IN('value1','value2','value3')

and just build the concatenated list (string) in vba. Just stupid easy. Loop over the .ItemsSelected collection, and append each (with a comma and delimiters) to the string. Then just use that in your recordset or whatever (or in your query... you could just edit the query's .SQL property and append the temporary filter, run it, and not save the query

[xps35]

It seems to me that your database structure is not good. If you have a field for each list in your contacts table, that is bad design. You should have 3 tables: contacts, lists, and a junction table between the two. For each list that a contact joins, you include a record in the junction table. This also makes it easy to solve what you want.

[moke123]

Following Madpiet's recommendation, here's a demo with some public functions I use with multiselect listboxes
you would use it like

EmailRecipient IN(" & fgetLbx(YourListBoxName) & ")"

[madpiet]

The usual structure for a distribution list type thing is

Individual ---(1,M)--DistributionListMember---(M,1)---DistributionList

So DistributionListmember is (IndividualID, ListID)

then all of this is super simple. if it's not, you're likely doing something wrong.

[June7]

I agree, resorting to UNION query indicates non-normalized data structure.

Since a contact can be in multiple distribution lists, do you want to prevent duplicate mailings?

Build and save UNION query object to generate a normalized structure.

SELECT "D1" AS ListID, Email_Address FROM tablename WHERE NOT D1 IS NULL
UNION ALL SELECT "D2", Email_Address FROM tablename WHERE NOT D2 IS NULL
UNION ALL SELECT "D3", Email_Address FROM tablename WHERE NOT D3 IS NULL;

Use that as source for another query using IN filter:
SELECT DISTINCT Email_Address FROM Query1 WHERE ListID IN(distribution list selection);

IN function list array cannot be dynamic in query object. VBA using QueryDefs would have to modify query object.

Alternative would be to build a simple report and set its RecordSource to VBA constructed SQL statement when opening and export report.

[madpiet]

Since several of us suspect that your database structure is not quite right, if you want, post a sample of the database with dummy data in it. (The people should be completely fake - don't want to get anyone in trouble!). But then we can fix this. Should take all of five minutes.

[KNobel]

Thank you all for your input. Very likely it's not structured properly as I am not a developer. =) But here is an anonymized version so you can see what I'm trying to do. Thank you again. =)

[KNobel]

Thanks all for your replies. Probably not structured correctly - I'm not a developer =( just know enough to be dangerous. really appreciate your help - here is the samplesampledb.zip

[madpiet]

the nested table of distribution lists (well, repeating field, whatever) in the t_Contacts table is what's causing your problem. It should be (ContactID Autonumber Primary key, FirstName, LastName, EmailHome, Primary Household contact, DoNotContact), and then t_Lists should be (ListID, ListName) and then "subscription" would be (ContactID, ListID). So you end up with this:

Contact--(1,M)--Subscription--(M,1)--DistributionList

This way, if you add a new DistributionList, you can just add subscribers (base the subform on Subscription, and use ContactID from Contacts in dropdown.)

(I really have to sort out the Powerqueries to do all this. ... kinda sad that the tool of choice for cleaning messy data is Excel, not Access. That "fixed data type" thing is a real hassle sometimes!)

[pbaldy]

Post 8 was moderated, I'm posting to trigger email notifications.

[June7]

As already mentioned, structure is not normalized. I already advised how it can be dealt with. Even if normalized, issue of how to prevent duplicate mailings will persist.

Now, do you want to change?

Normalizing would need 3 tables.

sampledb.accdb

[madpiet]

"Even if normalized, issue of how to prevent duplicate mailings will persist."

Why not put a primary key index on the combination of columns (MailingID, RecipientID). Then you can't have duplicates, because the data engine won't allow it.

From OP: "I'd like to make a "List Generator" form where a user can select 1 or more distribution list name(s) from a list box then hit a button to generate a list containing the Email_address and Dist_List name(s) to be exported to Excel."

You don't need one. If you have a subform on the DistributionList form, and base it on the junction table "Subscription" and add recipients there, then that's all you need. Create a query with the ListName, and the Recipient Names and EMails. No need to export it to Excel unless you really want to.

SELECT dl.ListName, p.EmailAddress
FROM person p INNER JOIN Listmembership lm ON p.PersonID = lm.PersonID
INNER JOIN DistributionList dl ON lm.ListID = dl.ListID

[June7]

The duplicates I mean are multiple emails to same address because address is in multiple selected lists. Not that Contact and Dist List pair is duplicated in table.