PROBLEM:
I'm using an Access form with a team of ten (10) users to automate data input. The form contains a number of tables (about 7) and an equal number of queries. Each user has been assigned a User ID so that the work they receive is uniquely their own.
Each time the form is updated, the queries (all 7) must be assigned to each user (all 10). The assignment process has been manual - enter each query, change the User ID, close, and save. How can I automate this process?
IDEAS for POSSIBLE SOLUTIONS:
1) Use a Combo Box or List Box, allowing the user to select their name/User ID, so that their assigned work will be visible to them.
ISSUE: Each time I've done this, the box indicates all the users times the amount of assignments they have. If a user has 10 assignments, their name appears 10 times in the box plus each user's name times the amount of assignments they have (this could result in 100+ duplicated names). I'm unsure how to limit the names to just the 10 users, so that only their assignments appear.
2) Create a single query, designed to allow the administrator (me) to enter the one query, assign the form to the one User ID, which will auto populate the User ID into the User ID field of all the other queries.
I'm thinking this is possible. I would need to create a query, link all the queries, reference the User ID field in each of the original queries to the new query. What more, I’m not sure? Is this doable?
3) Code a VBA script. I’m working on my macro writing skills – loving it, by the way. I’m also thinking a macro, designed for each user, could be run to change the User IDs in the User ID field of each query. Any suggestions?
4) What would be awesome is for a dialog box, designed to allow the administrator to enter the User ID, and it auto-change all queries.
I am very interested to know if any or all of these things are possible, or if there is another better option that could be explained. As I work more in VBE, the things I can conceive becomes less of “can it be done” than “how is it done.”
Thanks, everyone for your advise/suggestions/guidance. Really appreciate the help and the forum. I’m learning.
I'm using an Access form with a team of ten (10) users to automate data input. The form contains a number of tables (about 7) and an equal number of queries. Each user has been assigned a User ID so that the work they receive is uniquely their own.
Each time the form is updated, the queries (all 7) must be assigned to each user (all 10). The assignment process has been manual - enter each query, change the User ID, close, and save. How can I automate this process?
IDEAS for POSSIBLE SOLUTIONS:
1) Use a Combo Box or List Box, allowing the user to select their name/User ID, so that their assigned work will be visible to them.
ISSUE: Each time I've done this, the box indicates all the users times the amount of assignments they have. If a user has 10 assignments, their name appears 10 times in the box plus each user's name times the amount of assignments they have (this could result in 100+ duplicated names). I'm unsure how to limit the names to just the 10 users, so that only their assignments appear.
2) Create a single query, designed to allow the administrator (me) to enter the one query, assign the form to the one User ID, which will auto populate the User ID into the User ID field of all the other queries.
I'm thinking this is possible. I would need to create a query, link all the queries, reference the User ID field in each of the original queries to the new query. What more, I’m not sure? Is this doable?
3) Code a VBA script. I’m working on my macro writing skills – loving it, by the way. I’m also thinking a macro, designed for each user, could be run to change the User IDs in the User ID field of each query. Any suggestions?
4) What would be awesome is for a dialog box, designed to allow the administrator to enter the User ID, and it auto-change all queries.
I am very interested to know if any or all of these things are possible, or if there is another better option that could be explained. As I work more in VBE, the things I can conceive becomes less of “can it be done” than “how is it done.”
Thanks, everyone for your advise/suggestions/guidance. Really appreciate the help and the forum. I’m learning.
