Access Form issue with creating dynamic selectable list (1 Viewer)

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 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.

Click to expand...

you can always filter which staff is current.

arnelgp said:

you can always filter which staff is current.

Click to expand...

True, but that still does not provide me a mechanism to make the list selectable for the purposes of generating the email. Unless I am missing something that is so obvious that you would assume I know about it. Please don't assume anything. I am comfortable getting into the guts of access, but I only dabble and as a result only learn what I need for whatever side project I am doing.

you dont need to add selectable field on your table, you do it in the form.

arnelgp said:

you dont need to add selectable field on your table, you do it in the form.

Click to expand...

Ok I am missing something then. Here's what I did to try that. I changed the data source of my form to be the staff table. Then in design view of the form I added a check box control to the detail portion of the form. Then when I view the form, either in datasheet view or form view, that checkbox is repeated for every record but they are all slaved together. So I can either check all of them, or none of them. And all occurrences of them have the same name so even if they were not slaved together I would still have no way of determining which where checked and which were not when I try to determine which staff names the user selected.

For selecting a bunch from a table you have a number of options:

1. You can make a new table in which you store the selected ID's in one column and ID for who did the selection in 2nd column (in case of a multiuser db). You delete the selection and make a new one each time.

2. You can use a listbox - this is a fairly common way of selecting stuff, becasue you can bind the listbox to a table, and on top of this, inside the listbox, see what is selected and what isn't. This listboix can have multiselect enabled, so all selected items are directly marked.

3. An even more common way is to have 2 listboxes - each time you click on the list in the first one, that item gets added to the second and removed from the first. In this way it is easy to see a list of selected items.

4. Using an unbound checkbox in a form will not work, because there is only one - even though it seems to appear on every record, as you've witnessed.

Update:

For a bit of fun, you could create a so-called "detached recordset" or "fabricated recordset", which would allow you to operate on a table held in memory only. Google that term There are some posts on this topic here, search for it using my name or Galaxiom

Thank you both for your replies. I actually found a solution to this problem. I found that I could add a requery command to the macro that forced the form to repopulate with data after the append query updated the table.

So to sum it up for anyone coming to this thread for the same question a technique for creating a table of data in a form from which the user can make a single or multiple selections to take action without altering the table the source data is in here is what you do:

1. Create an empty table that contains the fields you want from the source table plus a yes no field

2. Create an append query that takes data from the source table and populates your new table

3. Create a delete query that removes that data from the table.

4. add a macro to the OnLoad event which 1) runs the append query and then 2) requeries the form

5. Add a macro to the OnClose event which runs the delete query

here's another approached you might want to enhanced, disconnected recordset, does not need you to add a new table just for that purpose.