Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Unanswered: Updating a record when a form field is updated

    Hi I have a subform (orderline) which has pcode (product code) as primary key and also a foreign key from product, nopurch (number purchased) and received (checkbox). When the box is ticked I want the db to add on the number purchased to noinstock in the product record. It appears I do this by creating an after update trigger on the tcikbox but the method appears to either be code or macro. My VB isn't strong especially as I don't know how to use SQL within VBA and the macro is confusing. The books I have say of these methods but not how to do it. Is there any chance of help please. I used to be OK creating a basic trigger in PL/SQL but unfortunately I need to do it in access.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    After Update

    We use unbound MSAccess forms where we utilize a "writeRec", "updateRec", "getRec", and "deleteRec" functions created in a module (i.e. opens the recordset and does its thing). When a record is retrieved to the unbound form (i.e. the "getRec" function is called which opens the recordset and writes the values to the unbound fields on the form), there is also an unbound checkbox on the form called: "DataUpdated". Once the user updates any of the fields on the form, the checkbox is marked as true and upon exiting the form (or a "Save" button is clicked), if the checkbox is marked as true, the update or write function is called. If the user clicks the "Cancel" button, then the form is simply closed.

    Using unbound forms and a mixture of some bound forms (with tables that aren't accessed or kept open by everyone for a long period of time) has worked GREAT for us and has allowed us to work with several gigs of data on our SQL Server with only 512 meg of memory. It also makes it extremely fast for the user (especially in a Terminal Server/Citrix environment) and they can keep the form open on a record for a long period of time without tying up the server. You do have the problem though if 2 users were to retrieve the same record which may or may not be a problem for you (it hasn't for us yet.)

    Hope this helps.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2006
    Posts
    3
    I understand but I want to update a value (number in stock) in the product table by subtracting orderlinesubform!number!ordered from tblproduct.no_in_stk when the orderlinesubform!received tick box is changed from no to yes.
    (the tables are linked by prodcode - the PK in tbleproduct).

    I would like to know how to do it but do you know of any sites or anything that could help me! I don't know whether it would be done using code or macros and although I can create basic macros and coding and have a basic grasp of SQL I don't know the best method to do what I want or how I would create the function as I don't know how to use SQL in VBa if that is the best way,

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are using MS SQL Server then you can use a trigger - I'd suggest you place the post in the SQL Server thread in this forum, not the Access forum.

    If you are not using a server product then you have a problem, JET doesn't suport triggers - If you are using Access to act as a front end (user interface) to a JET database - you can fake a trigger, by placing some code in the forms after update event.
    This code would do something like

    update myproducttable set StockLevel=StockLevel - QtySold

    still begs the question whether its better to have a stock column in the product table or to derive the value from the transaction made.

  5. #5
    Join Date
    Jan 2006
    Posts
    3
    I'm just using access and have just created what I thought would be a fairly simple dB for someone i know but I can't just update the noinstock in product when a product in orderline is marked as received

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes, but you have to implement the 'trigger' in VBA code
    you may be able to do this with a docmd.runsql

    you need to force the trigger in the forms after update event.

Posting Permissions

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