Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2007
    Posts
    35

    Unanswered: Change a value in a table after_insert

    Hey there,

    I have a form and I would like to create a module that will be run when I insert a new record.
    I figured out I can create an event procedure for the After Insert event.

    Now I want the module to change a specific value stored in a table (a sort of a counter that I would like to increment when I insert a new record), how can I do that?

    Thanks much!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The simplest would probably be to execute an UPDATE query that incremented the value by one.
    Paul

  3. #3
    Join Date
    Nov 2007
    Posts
    35
    Thanks for the tip, I managed to create an update query, which is something new.
    My need is a bit specific though - I want to update only one record at a time, based on my form's combobox value.
    Can the update query's criteria filter records like so?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    you 'just' set the update criteira correctly using a where clause...
    bearing in mind that every table in a fully realtional db will have a primary key which by defintion is unique you specify that primary key
    .

    SQL itself doesn't care on how the records are filtered or limited it applies whatever changes you want to what ever records pass the criteira you specify

    so update products set price = price * 1.175 .. means update all products by 17.5%

    if a manufacturer announced an accross the board price increase on their products of say 10%
    update products set price = price * 1.1 where manufacturer = "ronh corp"

    if you wanted to change a specific item then you would need whatever makes that record unique...
    it could be your internal porduct no
    update products set price = price * 1.175 where productno=12345
    it could tbe the manufacturers stock number
    update products set price = price * 1.175 where manufacturer = "ronh corp" and manufacturers stockno = "abc12345"

    it could be a product description
    update products set price = price * 1.175 where product description="super duper widget"

    it all depends
    but make no mistake its far easier to use the rows priamry key.. there is less risk of problems

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    And in your specific case:

    WHERE FieldName = Forms!FormName.ComboName
    Paul

  6. #6
    Join Date
    Nov 2007
    Posts
    35
    Thank you both so much, this seems to work when I simply run the query and enter an ID.
    What is the event procedure to run the query from whithin the form though?

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I think you were correct, the after insert event seems appropriate.
    Paul

  8. #8
    Join Date
    Nov 2007
    Posts
    35
    Yes I figured out which event, but what is the VBA procedure to execute a query? I can't seem to find a function to do so.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by ronh
    a counter that I would like to increment when I insert a new record
    Is the value supposed to equal the number of records? If so, you can find this out "on th fly" whenever you need through a far more efficient (not to mention accurate) method.

    More explanation needed on what this field is used for
    George
    Home | Blog

  10. #10
    Join Date
    Nov 2007
    Posts
    35
    I used the function RunSQL and it worked (sometimes I just need to rtfm), thanks.

    George- The database practically logs tests I do. There are several different offices I do the tests at, and each one of the offices has a private counter for the number of tests done in that particular office (since the beginning of the year).
    So basically I distinguish between the tests by giving every on of them 3 variables:
    [offices private test counter] , [office name] , [year]
    Or for example, "Test16, Office1, 2007".

    What I currently have is a table that lists the office names, and the tests counter for each one of the office. If I'm doing something very wrong I will certainly appreciate design tips.

    Thank you,
    Ronh

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you are storing the office name against the test then you immediately know the number of tests done at that location
    Code:
    SELECT Count(officeName) As [numberOfTests]
    FROM   myTable
    WHERE  officeName = 'Glasgow Branch'
    Therefore using a calculated field is not only pointless - but may cause innacuracies because it relies on application code to retain integrity!
    (What would happen if you entered a test against the Glasgow Branch by mistake - so you adjusted it to the Leeds Branch, or even deleted it and created a new one for Leeds?

    If any of what I have said doesn't make sense - please ask questions! I am more than happy to explain if needs be
    George
    Home | Blog

Posting Permissions

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