Updating table value when different table form field updated

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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:
 
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.
 

Cookiegal

Karen
Administrator
Malware Specialist Coordinator
Joined
Aug 27, 2003
Messages
117,488
Please do not start more than one thread for the same question.

Closing duplicate.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top