automatically creating pages and queries for new users RRS feed

  • Question

  • Hi,

    Is it possible to set up access to automatically create new pages, queries and reports for each record when its created in a master table so that my customer can create new users etc?

    Thanks in advance.

    Kind Regards


    Monday, May 21, 2018 4:52 PM


All replies

  • Hi Richard,

    Not sure why you're asking but it should be possible. However, I can't say it is a recommended approach. When you say "pages," to which were you referring?

    Monday, May 21, 2018 5:08 PM
  • Hi,

    The current strcture requires me to manually program the user forms and reports etc which is ok short term but would prefer them to be able to create there own users in the long term.

    I hope that makes sense.

    Kind Regards


    Monday, May 21, 2018 5:25 PM
  • Hi Richard,

    I'm afraid it didn't explain the issue clearly for me. Do users use a different form for the same database? If you're simply making some labels reflect the user's name, then you don't have to make a form for that. Can you post some images to show an example of the forms for two different users? Thanks.

    I guess what I don't understand is why you have to create new forms just because you have new users.

    • Edited by .theDBguy Monday, May 21, 2018 5:45 PM
    Monday, May 21, 2018 5:43 PM
  • The journey starts with a login page that has the login details programmed by me at the moment so would need some code to automate this.

    The pages and reports that would need replicating are as follows;

    This is the agent dashboard

    This is the main job page

    This is the daily job report 

    This is the job log form

    This is the job log report that can be printed or emailed, I also have a daily job report that can be emailed or printed.

    This project is becoming a labour of love and I want to make it as good as possible, plus I have learnt how good the technical skills of the people who will be using the system is and so have to make it as easy as possible to use, hence automation being key.

    Any help with this is appreciated.

    Kind Regards


    Monday, May 21, 2018 6:13 PM
  • Hi Richard,

    Thank you for posting those images. Although you did not explain it, I am guessing the "rubbed out" parts are where you are displaying the name of (or some information about) the logged in user, correct? If so, then there's no reason to create new forms for every user.

    For example, let's say you log in as Richard, do you have a form called something like "RichardSignOutForm" and "RichardDashboardForm" and "RichardJobLogForm", etc.. and if another user logs in as John, do you duplicate your forms and maybe name them like "JohnSignOutForm" and "JohnDashboardForm" and "JohnJobLogForm", etc.?

    If so, yes, there's no need to do so. Can you post an image of the Sign Out form in Design view showing the area you rubbed out? I just want to see what it looks like in design view (I am hoping it doesn't show the actual data).

    Monday, May 21, 2018 6:34 PM
  • Hi,

    The area I rubbed out was the logo of the company in the form header.

    the path taken is that all users start at the login page that directs to the specific landing page/dashboard form for that user and from there they access forms and reports associated with that user. Do I need to find new code for the login as the code I have requires a unique page per user.

    Kind Regards


    Monday, May 21, 2018 7:37 PM
  • Hi Richard,

    I guess I'm still trying to understand what is different between one user's form as compared to another user's form. Let's say you have a button to open the user's form, after they log in, do you do something like this:

    If LoginUser = "Richard" Then

    Open "RichardForms"

    ElseIf LoginUser = "John" Then

    Open "JohnForms"

    End If

    In other words, do you actually have multiple forms, which otherwise look the same except for the form names because each user must use a different form?

    Monday, May 21, 2018 7:51 PM
  • The real issue here is whether the different forms used by each user differ in appearance, or whether they differ by presenting different data for each user.  The latter is usually termed 'row level security' and is generally the way in which users' access to only those records for which they have authorisation is controlled.

    Sometimes different users will be given radically different interfaces, but this is usually on the basis of the type of user, rather than each user having their own individually designed interface.  This was done in my own organisation, I and my staff using the full interface with the ability to enter, edit or delete data, and access to the full analytical capabilities of the database, whereas other members of the department had access via a simplified read-only interface.  All users accessed the same back end, however, so it was merely a case of distributing copies of one front end to my own section, and copies of another front end to all other sections.

    If it is row level security which you are implementing, then the same forms can be used by each user, but the data presented in those forms will be those records assigned to or created by the user in question.  The same applies to reports.

    You'll find examples of how row level security can be implemented in SecDemo.zip in my public databases folder at:


    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In the zip archive the basic SecDemo07.accdb file controls access to records by group.  Each user can be assigned to one or more groups, and each record can be made accessible by one or more groups.  

    The two 'pseudo-login' files require the user to login in an opening form.  For the purposes of the demo anybody can login as any user, but in an operational database they would be required to do so by means of a user name and password of course.  Once logged in the records presented are those either created by them or to which they have been granted access.  The _projects variant gives access to users on the basis of projects to which they have been assigned.

    The autologin variant of the _projects file automatically logs a user in on the basis of their Windows login name.  When a new user initially logs in they are assigned to the user list.  Again, for the purposes of the demo the user can, after being logged in automatically, login as a different user.  This would not be possible in an operational database of course.

    Ken Sheridan, Stafford, England

    Monday, May 21, 2018 9:06 PM
  • Hi .theDBguy,

    Yes thats how it works.

    Kind Regards


    Tuesday, May 22, 2018 7:59 PM
  • Hi Ken,

    Thank you, I will look into this in more depth tomorrow.

    Kind Regards


    Tuesday, May 22, 2018 8:04 PM
  • Hi .theDBguy,

    Yes thats how it works.

    Kind Regards


    Hi Richard,

    If so, and as Ken also mentioned, this approach is not (should not be) necessary.

    That is, if all the forms look the same for each user, with some slight differences based on functionalities, then you should be able to use the same form over and over for all employees.

    For example, if I have a form called frmEditOrder, then this same form can be used by any user who logs into the system. The only difference is perhaps, only managers can edit the monetary values, such as adding discount adjustments, while the sales people can only edit the customer data.

    Either way, there is only one physical form called frmEditOrder and based on who logs in, then certain functionalities are disabled or enabled by using code. So, there is no need to duplicate the form and modify it for a specific user.

    Hope it makes sense...

    • Edited by .theDBguy Tuesday, May 22, 2018 8:16 PM
    • Proposed as answer by Terry Xu - MSFT Wednesday, May 23, 2018 5:43 AM
    Tuesday, May 22, 2018 8:14 PM
  • Hi .theDBguy,

    My brain isn't working properly today but I think I understand what you're saying.

    Kind Regards


    Tuesday, May 22, 2018 8:34 PM
  • Allow me to try one more time. Let's say you have a form called SalesForm, which for example displays all the products sold by your employees.

    According to your previous post, you seem to be duplicating this form for each user of your database. For example, if you have a user named John, you would create a copy of SalesForm and call it JohnSalesForm by modifying the form to only display all the data pertaining to John.

    Then, when a new user named Henry shows up, you create another copy of SalesForm and call it HenrySalesForm to only display all sales transactions pertaining Henry. So, Henry or John cannot see the data for the other users.

    Is this correct? If so, then what we are saying is you can simply use SalesForm (without making any copies of it) and simply "filter" the data displayed in SalesForm based on who is logs in.

    This can be accomplish using a filtered query or by assigning the record source for the form when the user opens it. Basically, dynamically assign what the form displays at the time it is used. Therefore, there's no need to create new forms because the same form can display any set of data you need based on the user.

    Hope it's clearer...

    Tuesday, May 22, 2018 8:47 PM
  • Hi,

    Yes you've pretty much hit the nail on the head there, the way my database is supposed to work is to have four different levels of user, below is a breakdown of how it works (or will eventually)

    1. full access for maintenance purposes

    2. main admin, this allows booking of work and tracking of work, employees, service provider logs and email integration.

    3. Service provider, this allows them to track the progress of o=new and ongoing work relating to them.

    4. Employee, this allows them to track what work they have for a specific day, log there jobs and total there expenses as well as email these totals to the main office for a predetermined period of time so they can be procesed.

    I also need to have the website secured with passwords that are unique to each user and to be able to track the activity on the database.

    As I previously stated I have created the bulk of the forms, queries and reports (most of them multiple times) and have password protection and use tracking set up but I am not too sure how to set it up to allow new users to be  added with ease or the filtering coding required for the users to be recognized and the access filtered.

    Coding has always been my wall that Ive just never managed to break through hence my confusion about how to assign the users to specific forms and filter those forms accordingly.

    Please can you help?

    Kind Regards


    Wednesday, May 23, 2018 4:09 PM
  • Hi Richard,

    Rather than thinking how to assign a user to a specific form, you could think about how to modify form properties so only allowed actions can be performed by the current user. This means you can reuse the same one form multiple times.

    Have a look at some of the demos at UtterAccess Code Archive on the topic of Security to see how something like this is possible.

    Here's some examples:

    Security Demo

    Advanced Custom Password

    Also, take a look at this Wiki article: Login Security

    Hope it helps...

    • Marked as answer by R.Nock Tuesday, May 29, 2018 3:31 PM
    Wednesday, May 23, 2018 4:18 PM
  • Thank you for your help and patience, I will look at the articles and try to get the information to sink in, I am sure that the structure I have in place is close to what I need its just that I have found myself going in circles with online 'advice' hence this time I've come straight here to get the right advice and I very much appreciate all your help and patience.

    Kindest Regards


    Wednesday, May 23, 2018 6:18 PM
  • Hi,

    I have been looking into the articles that you recommended but they all seem to be based around the 2000 version of the software and the steps laid out don't match what I can find on my system, I am starting to get my head around how it needs to work now but just getting unstuck with the great differences between 2000 version and the latest version of access.

    Any guidance would be appreciated.

    Kind Regards


    Friday, May 25, 2018 4:39 PM
  • Hi Richard,

    The Wiki article should still be applicable to the latest version of Access. Can you describe exactly with which part you're having problems? It might help too if we could actually see your database design. Are you open to a private online meeting?

    Friday, May 25, 2018 4:49 PM
  • Hi, 

    Sorry for the late response, I had a manic weekend, I have now set up a login page that differs slightly from the one on the wiki page but still filters the start of the users journey so there path is a set one with access to certain forms.

    I have the user type the username and password as I don't like the idea of a drop menu for security but it does seem to make it so that I cant use the filter coding on the wiki article further into the journey but at this stage I think I can work with that.

    I was wondering if you know of any way that I could display the username in the form headers once the user has logged in so that I could then refer to it for query filter actions which were seamless in my old database because each form was dedicated to that user so coding was relatively simple (in my mind any way).

    Thank you again .theDBguy for all your help and patience.

    Kind Regards


    Tuesday, May 29, 2018 9:41 AM
  • Hi Richard,

    There's a couple of approaches you could try for showing the current login username:

    1. Instead of closing the login form, just hide it. You can then refer to it anywhere else in your database for display. For example:

    Me.Visible = False


    Me.txtUsername = Forms!LoginForm.txtUserName

    2. Set the value of a TempVars variable to the login username. You can then use the TempVars anywhere in your database to display it. For example:

    TempVars.Add "UserName", Me.txtUserName.Value

    and then

    Me.txtUserName = TempVars!UserName

    Hope it helps...

    Tuesday, May 29, 2018 1:18 PM
  • That worked exactly how I wanted, in theory I should be able to filter the query using the same sort of coding as option 1 above shouldn't I?
    Tuesday, May 29, 2018 1:51 PM
  • Hi,

    Yes, you can actually filter a query using either a form reference or a TempVar.


    Tuesday, May 29, 2018 2:35 PM
  • Hi,

    Thank you very much .theDBguy, you are a real life saver, trying to figure out what I was doing would have likely cost me my marriage because of how long I would have been stuck for :)

    I will mark the answer now.

    Kindest Regards


    Tuesday, May 29, 2018 3:31 PM
  • Hi Richard,

    You're very welcome. Glad we could assist. Good luck with your project.

    Tuesday, May 29, 2018 3:42 PM
  • Thanks, I am sure that I will be in touch again soon :)
    Tuesday, May 29, 2018 4:56 PM