Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Assigning a value in a table with VBA (Access03)

    I have a table holding values regarding the company. With VBA how do I change a value in the table.

    When the user clicks the Exit button it brings up a form reminding the user to backup the database. If it is Wednesday the form displays, if the table will be backuped the user puts a check in a check box. If it is any day other than Wednesday the box doesn't display. I need the YES value in the company fact table changed to NO on Thursday so the message is available on Wednesday. How do I make it happen.

    Also how do I restrict a table to only one record, but allow edits to that row?

    Thank you.

    Fay

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Assigning a value in a table with VBA (Access03)

    There are a few ways to update a value in a table, behind the scenes. The easiest is probably to run an update query .

    Create a query based on the table, select the field that contains the yes/no value, then choose Query...Update from the menus,
    The query grid will then gain an extra line update to On this line put No for the field .

    You then need code to run this query: Docmd.openquery "qryname"
    Because it is an action query , this will cause it to run, rather than open.

    To avoid the warning message, precede this line of code with Docmd.SetWarnings False, and then follow it with Docmd.SetWarnings True (unless you have already turned warnings off in tools..options.)

    What I am not sure about is what event you want to trigger this code to run.

    You can't limit a table to just one row! You just have to make sure that nothing ever creates a new record. If you have a form that displays the data from that table, you can use a range of form measures to stop new records being created - AllowAdditions = false etc.
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning a value in a table with VBA (Access03)

    Thanks John I never thought about that approach. But I got it nailed down with your help. Have a great evening.

    Fay

Posting Permissions

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