Hi
A few weeks back, I posted a message regarding creating a Form that contained 2 List Boxes. The List Box on the Left would pull its data from a Query that contained all our Current Mailing List Members. When a User clicks an ‘ADD’ Command Button on the Form, the selected Mailing List would ‘move’ from the Available List Box to the Selected List Box. Hans very kindly provided code that would allow this to function, although it used a system whereby check boxes were turned on or off to indicate that a value had been selected. This would be great except that many, many people would be generating mail merges. However, leaving that aside, I purchased 2 books {SAMS Teach Yourself Access VBA (which was useless) and Access VBA Step By Step (which is terrific)} in the hope that I would learn how to pass values from a Form to a Query, but unfortunately neither provided a way to do such. Now I’m wondering is it possible to do this in Access VBA?
Example:
A user selects ‘All Members‘, ‘Additional Contacts‘, and ‘Leadership Team‘ from a List Box, and when the user hits an EXPORT Command Button, Code would run that passes the values into the query:
SELECT company_details.name, company_details.address_1, company_details.town, company_details.postcode, list_details.list_name, personnel_details.name, personnel_details.salutation, personnel_details.title, company_details.active, company_list_details.active, list_details.active, personnel_details.active
FROM ((company_details INNER JOIN company_list_details ON company_details.company_id = company_list_details.company_id) INNER JOIN list_details ON company_list_details.list_id = list_details.list_id) INNER JOIN personnel_details ON company_list_details.personnel_id = personnel_details.personnel_id
WHERE (((list_details.list_name)=”ALL Members“) AND ((company_details.active)=”Y”) AND ((company_list_details.active)=”y”) AND ((list_details.active)=”y”) AND ((personnel_details.active)=”Y”)) OR (((list_details.list_name)=”Additional Contacts“) AND ((company_details.active)=”Y”) AND ((company_list_details.active)=”Y”) AND ((list_details.active)=”Y”) AND ((personnel_details.active)=”Y”))
OR (((list_details.list_name)=”Leadership Team“) AND ((company_details.active)=”Y”) AND ((company_list_details.active)=”Y”) AND ((list_details.active)=”Y”) AND ((personnel_details.active)=”Y”))
ORDER BY personnel_details.name;
From what I’ve read, I do think this is possible, so I guess I’m asking if someone knows how to do this?
.
Stupid mistake on my side. You can just replace Function by Sub:
)
