1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Updating table value when different table form field updated

Discussion in 'Software Development' started by JuniorLearner, May 14, 2015.

Thread Status:
Not open for further replies.
  1. JuniorLearner

    JuniorLearner Thread Starter

    Joined:
    Jul 10, 2014
    Messages:
    14
    Hi there,
    I am not that cluey with access vba programming :) and I need some help please.
    I have a Table called (Parts) with columns (PartName), (PartNumber), (PartCost), (Quantity)...
    I have (Parts) table linked to (Repairs) table in one to many relationship.
    I have a Form linked to the table (Repairs) with different fields to enter different repair details.
    Among these fields is: combo box (PartsNameCombo) where you can select the Part Name from values listed in (PartName) column in table (Parts), and I have used the following codes to populate the selected part's cost accordingly:

    Private Sub PartNameCombo_Change()
    'Auto populate Part cost based on Part Name
    Me.PartCost.Value = Me.PartNameCombo.Column(2)
    End Sub

    I have repeated the above combo box three times (in case different parts used in one repair).

    My Question: I would like to add code to automatically subtract (1) from part's Quantity in (Parts) table whenever a particular (PartName) is selected in the combo box.
    so for example, if in a repair, I selected: (front case) in the first combo box and (display screen) in second combo box, I want the cases quantity to drop by one and also screens quantity to drop by 1?
    Any one can please help?:confused:
     
  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    At least two ways to do this but I wouldn't make it automatic. What if the wrong Part name was inadvertently selected? I would ad a button to the form "Take one from Inventory" and execute an update query that basically does Quantity= Quantity-1 . This query would need to link to PartNumber on the active form.

    The other way would be to write the SQL to do the update in VBA and run the EXEC SQL command under the new button.
     
  3. Cookiegal

    Cookiegal Administrator Malware Specialist Coordinator

    Joined:
    Aug 27, 2003
    Messages:
    112,595
    Please do not start more than one thread for the same question.

    Closing duplicate.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1148258

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice