What I am trying to do:
I have a form launched from a switch board that allows users to send emails. I want to add a list of current staff members and their email address from which a user can select any number of people and then generate an email. I have no issues with the email generating function. I am having trouble creating the selectable list.
The catch:
I cannot add a select type field to the staff table in the database backend because I need multiple users to be able to use this function. The table contains a number of transferred staff and several fields not related to the email form. I only want the list to show current staff and a limited number of fields so I cannot access the staff table directly. A simple query does not add the selection capability I need, or at least simply adding a new field in the query didn't work.
What I tried that did not work:
I created an empty table that had just the fields I needed, an Append Query to populate it with the exact data I want and a Delete Query to purge it when I am done. That table resides on the front end so every user would have their own table. My problem is that when I put the Append query in the forms OnLoad or OnOpen events the query runs but after the form loads the blank table data. So the form loads blank but if I peek into the table the data is there. I need a way to run the query and then allow the form to load the data. I considered running the query when either the database or the switch board opens. But then if a user adds a new staff member to the database he would have to close the session and reopen to get the latest data. If I trigger it based on some other event that occurs in the course of navigating to the email form I risk the table being populated multiple times before the delete query is triggered and then presenting the user with a lot of duplicates. I cannot identify a good place to trigger the Append Query.
I am not attached to the new table/Append/Delete solution. Anything that gets me a selectable list will work. I am comfortable enough with VBA to write code and create recordsets if that is the way to go, however examples would be very much appreciated. Thank you in advance for your help.
I have a form launched from a switch board that allows users to send emails. I want to add a list of current staff members and their email address from which a user can select any number of people and then generate an email. I have no issues with the email generating function. I am having trouble creating the selectable list.
The catch:
I cannot add a select type field to the staff table in the database backend because I need multiple users to be able to use this function. The table contains a number of transferred staff and several fields not related to the email form. I only want the list to show current staff and a limited number of fields so I cannot access the staff table directly. A simple query does not add the selection capability I need, or at least simply adding a new field in the query didn't work.
What I tried that did not work:
I created an empty table that had just the fields I needed, an Append Query to populate it with the exact data I want and a Delete Query to purge it when I am done. That table resides on the front end so every user would have their own table. My problem is that when I put the Append query in the forms OnLoad or OnOpen events the query runs but after the form loads the blank table data. So the form loads blank but if I peek into the table the data is there. I need a way to run the query and then allow the form to load the data. I considered running the query when either the database or the switch board opens. But then if a user adds a new staff member to the database he would have to close the session and reopen to get the latest data. If I trigger it based on some other event that occurs in the course of navigating to the email form I risk the table being populated multiple times before the delete query is triggered and then presenting the user with a lot of duplicates. I cannot identify a good place to trigger the Append Query.
I am not attached to the new table/Append/Delete solution. Anything that gets me a selectable list will work. I am comfortable enough with VBA to write code and create recordsets if that is the way to go, however examples would be very much appreciated. Thank you in advance for your help.