Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    68

    Unanswered: Can user update a query?

    I would like to create a way to have a user update a query and have the query saved. In other words, I would like to them to be able to change the criteria set on a query.

    How can I do this? I am thinking that I need to create a button that prompts for a value and then runs an update Query that grabs the value and stores in it the query???? Any thoughts?

    Thank you.

  2. #2
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Try using a parameter in your query and when the query runs, it will automatically prompt the user for a value. Parameters are designated by [], and can be placed in a criteria field of your query design view.

    For example, if you wanted to have the user select the start date for a query, you'd put this in the criteria for the Start Date field of your query:

    [Enter Start Date:]

    Whatever you put in the brackets will be the user prompt and the value they enter will be passed to the query.

  3. #3
    Join Date
    Mar 2004
    Posts
    68

    more explanation of what I want ...

    thanks for your response. I understand that a parameter query would work in this case. However, I would like the filter or parameter to be saved to the query permanently, so that they are not always prompted for it when the form opens.

    Basically, the front end file that is distributed will be sent out without a filter on the query. I would like to have to avoid going to each person's computer to "configure" or set the filter on the query so that only their data displays on the form when the form is opened.

    Hope that makes sense.

    Thankyou.

  4. #4
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    There are many ways to do this...do you currently have a workgroup file created for your back-end database? That may be the easiest...create user accounts for everyone who will use the front-end application and make a table that shows the relationship between user and what "set" of data they can view. Then just incorporate that into the query - that way whenever anyone logs in, they can only see their subset of data in the query, and there's no need to change the query, even if you add new users...you'll just have to update the "permissions" table. Would something like that work? Or are the sets of data assigned to each user arbitrary and constantly changing?

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Quote Originally Posted by palto1
    thanks for your response. I understand that a parameter query would work in this case. However, I would like the filter or parameter to be saved to the query permanently, so that they are not always prompted for it when the form opens.

    Basically, the front end file that is distributed will be sent out without a filter on the query. I would like to have to avoid going to each person's computer to "configure" or set the filter on the query so that only their data displays on the form when the form is opened.

    Hope that makes sense.

    Thankyou.
    You can use TextBox on your Form, in which you fill it with the suitable filter in the OnOpen Form, alos the user can change its value. This TextBox you have to intigrate it in your Query using the Forms!yourFormName!yourTextBoxName
    Hope that can help

  6. #6
    Join Date
    Mar 2004
    Posts
    68

    More information on the "workgroup file" approach?

    Thanks for both your replies. Jeff, I am very interested in your solution as the "permissions" factor may come into play with future functionality. Do you know if there is any information on the web (or that you can send me) that further explains how to set up a "workgroup file"?

    It may be necessary in the future to restrict the user's access to specific forms (and possibly to specific tables.)

    In the meantime, I am going to try hammbakka's approach by using a textbox. I not really sure how this will work though. Will have to play with it. Will the user have to enter their PIN (unique identifier) each time they access the form. That is what I'm trying to avoid.

    Thanks again for your help,
    Grace

  7. #7
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    palto1 -

    This FAQ on the MS website is not too bad, it tells you how to set one up, among other things:

    http://support.microsoft.com/default...2Fsecfaq%2Easp

    It doesn't go into setting up a table to relate the users to sets of data...that's something I worked up on my own, mainly from books I've read. When a user logs on, you can use the CurrentUser method in VBA to determine who's logged on. So basically, if you can incorporate that into a query that shows your data and has an extra column with the user name (derived from joining a table of data with a table of user "permissions" - a table consisting of the user name and a field from your data that represents a grouping of the data, like the State in a list of Cities), you can use CurrentUser in a WHERE clause if you run it in VBA. But I'll typically place the value of CurrentUser in an invisible textbox on the main database form when it opens, and just reference that textbox in any user-specific queries.

    If you want something a bit more in-depth, you may have to pick up an Access book of some sort...I recommend the Access 2000 (or 2002) Developer's Handbook, both volumes. Those are the best books I've found about Access.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •