Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: updating a table

    I have a table with four fields and 33 records. One field, if needed has to be updated by the user. The field contains the same value for all 33 records. I also use this table in a query. The user will only have access to the form. What's the best way to have the user change this value through the form? I created a update query but I need the user to change the value as necessary thru a form. I tried to use and unbound control but the option for controls is a combo box or list box and it's not working for me the way I want. I want the user to imput the value in the update query to change the table. Is this possible?
    Thanks

  2. #2
    Join Date
    Apr 2008
    Posts
    13
    Hi slimjen,

    If I understand what you need, it's possible with something called "query by form" (if I'm using the term correctly).

    1 - Add a text box on the form where the user will enter the new data for the field.
    2 - Make an update query that will change the value in the table. The "update to" section of the query field will refer to the form text control - something like "forms![myform].newtext". You can right-click the "update to" section and choose "Build" to help with the syntax.
    3 - Then on the form, add a button that runs the update query (the control wizard will do this for you if you're not into programming).

    Or, it might be easier to redesign the table a bit and use multi-table design. Officially, it's not the best design to have 1 field that is always the same for every record. Instead, you could make a new table with 1 record and 2 fields - "ID" and "Value", for example. Then in the original table change the repeated field to contain the ID# of the record in the new table. Then your form is real simple - it's based on the new table and has only a text box for the value.

    Hope this helps.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If the field contains the same value for all records, there is no need for the field in that table.

    However, you could put a parameter value in your update query... run the query from a button... bit rough, but it should work.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jul 2004
    Posts
    214
    Got it! Thanks for all your help

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What did you end up doing?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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