Macro to write data in database for 6 different employees

[ManipulatoRX]

Hi,
my first ever post here.
I have built an interactive database. It adds projects to specific employees. It basically runs this way: you write a project name and choose 1 employee from the list consisting of 6 of them.
Then there is a button that sends an e-mail and then writes this project to database.
I would like to improve it.
There are some situations where I would like to send 1 project to all of the users and to sent 6 emails and in database there should be 6 different records.


How to make it with macro?

[Employee] - field with employees (list)
[Project] - name of the specific project, text box

[ranman256]

make a form, on the form is a combo box to pick the report/query to send.
put a list box , that has all the users emails.
a button to send that report/qry , cboRpt, to all the users listed in lstEmails.

[Micron]

You don't need a list box or another form if you can simply return all employee email addresses by query. Then you don't send six emails. You send one to six recipients so they're aware that they all got the same task.

Not sure if you really meant you want a macro or code. Posters here often call code a macro, which is something I rarely use.

[ManipulatoRX]

I think we have 2 problems here.
1 - sending email
2 - saving records equal to number of users
For instance: Project: Project A, Users from User 1 to User 6.
So I would like to save 6 different records.
1 - Project A, User 1
2 - Project A, User 2
...
6 - Project A, User 6
Could you guys describe it how to build this code/macro?

[aytee111]

You don't tell us your table structure - is this all on one table? Ideally, there are three tables: one for users, one for projects, and a lookup table containing the projectID and the userID.

Your form's record source would be the lookup table where they select a project and select an employee and a record is written to that table. With this setup, no code is needed.

To send an email, you would have buttons to do that - on that form to send an email after a new record has been added (or any time), plus another button somewhere to send emails based on the lookup table in the manner described by Micron.

[Micron]

To do that the single form should have a multi select listbox to allow multiple assignments to one project. Would be easier than a subform design, which isn't useful for single assignments anyway. I would create the emails as a separate function, done after he record creation is assured, otherwise you'll find yourself sending multiple emails to the same people for some projects.