Update Query to subtract a field in one table from a field in another

[LanguidAnomie]

Hi,

I am creating a relational database for my A-level ICT coursework project (this is the first time I've ever used database software) and am trying to create an update query. Essentially, I have a table of parts (tblPart) and a table of parts that have been used (tblPartsUsed) and I need to create a query that will subtract the value in the 'Quantity' field in tblPartsUsed from the value in the 'QuantityInStock' field in tblPart. I assume I need to put something into the "Update To:" field in the query design, but I don't really know how to do this.

For instance, if there are three Seagate Momentus HDDs in stock, and one is sold, I need the value in the 'QuantityInStock' field in tblPart to decrease to two.

Thanks in advance for any help.

[June7]

Trying to maintain aggregate (balances) data in tables can be problematic. Ideally, would enter in/out transaction records and use queries and reports with aggregate calcs to derive the balance info whenever needed.

Saving the new calculated balance does not necessaritly call for an UPDATE action. If the Quantity field is included in the RecordSource of the form, can simply set value of the field: Me!QuantityInStock = Me!QuantityInStock - Me.textboxQuantity.
The trick is figuring out what event to put this code in so that the value isn't altered multiple times. You don't want it in the AfterUpdate event of textboxQuantity because if user errors and changes the entry, it will calculate each time.

If the field is not part of the form RecordSource, then UPDATE action would be like:
CurrentDb.Execute "UPDATE tblPart SET QuantityInStock = QuantityInStock - " & Me.textboxQuantity & " WHERE PartID=" & Me.PartID
Same considertions about what event to put code in.

Examples are VBA syntax, I don't use macros.

[LanguidAnomie]

Trying to maintain aggregate (balances) data in tables can be problematic. Ideally, would enter in/out transaction records and use queries and reports with aggregate calcs to derive the balance info whenever needed.

Saving the new calculated balance does not necessaritly call for an UPDATE action. If the Quantity field is included in the RecordSource of the form, can simply set value of the field: Me!QuantityInStock = Me!QuantityInStock - Me.textboxQuantity.
The trick is figuring out what event to put this code in so that the value isn't altered multiple times. You don't want it in the AfterUpdate event of textboxQuantity because if user errors and changes the entry, it will calculate each time.

If the field is not part of the form RecordSource, then UPDATE action would be like:
CurrentDb.Execute "UPDATE tblPart SET QuantityInStock = QuantityInStock - " & Me.textboxQuantity & " WHERE PartID=" & Me.PartID
Same considertions about what event to put code in.

Examples are VBA syntax, I don't use macros.

Hey thanks for the quick response.

The field is not part of the form's RecordSource. Where do I put:
CurrentDb.Execute "UPDATE tblPart SET QuantityInStock = QuantityInStock - " & Me.textboxQuantity & " WHERE PartID=" & Me.PartID?

Do I create a VBA thing and then decide what event to put the code in? I've never used VBA before but have always wanted to try.

[June7]

Yes, code needs to be in some event. As stated, this is tricky. Maybe a button click. Create button, in Properties dialog Event tab, select [Event Procedure] in Click event. Double click the ellipses (...), this will put you in the procedure in the VBA editor. Type code.

[LanguidAnomie]

Yes, code needs to be in some event. As stated, this is tricky. Maybe a button click. Create button, in Properties dialog Event tab, select [Event Procedure] in Click event. Double click the ellipses (...), this will put you in the procedure in the VBA editor. Type code.

I have a button in a form (frmJobPart) that, when pressed, I want to activate the code. I've put the code as Event On Click but it fails. If I send you my project again would that make it easier?

Here it is if you want to take a look.

[June7]

The code is not in the "Update Stock" button click event. It is is in an event with a name of a button that doesn't exist. Form in design view, select the Update Stock button, double click the OnClick event property ellipses, type code in the event.

Also this is a form/subform. The button is on main form and Quantity and PartID are on subform. So reference to Quantity and PartID is trickier. Give the subform container control a name different from the form it holds, like ctrJobParts. Then:
CurrentDb.Execute "UPDATE tblPart SET QuantityInStock = QuantityInStock - " & Me.ctrJobParts.Form.Text11 & " WHERE PartID='" & Me.ctrJobParts.Form.Combo4 & "'"
or
CurrentDb.Execute "UPDATE tblPart SET QuantityInStock = QuantityInStock - " & Me.ctrJobParts!Quantity & " WHERE PartID='" & Me.ctrJobParts!PartID & "'"

PartID is text so apostrophe delimiters are needed.

[LanguidAnomie]

The code is not in the "Update Stock" button click event. It is is in an event with a name of a button that doesn't exist. Form in design view, select the Update Stock button, double click the OnClick event property ellipses, type code in the event.

Also this is a form/subform. The button is on main form and Quantity and PartID are on subform. So reference to Quantity and PartID is trickier. Give the subform container control a name different from the form it holds, like ctrJobParts. Then:
CurrentDb.Execute "UPDATE tblPart SET QuantityInStock = QuantityInStock - " & Me.ctrJobParts.Form.Text11 & " WHERE PartID='" & Me.ctrJobParts.Form.Combo4 & "'"
or
CurrentDb.Execute "UPDATE tblPart SET QuantityInStock = QuantityInStock - " & Me.ctrJobParts!Quantity & " WHERE PartID='" & Me.ctrJobParts!PartID & "'"

PartID is text so apostrophe delimiters are needed.

So here's what I'm doing:

• frmJobPart Design View
• Selecting button "Update Stock"
• Opening the "Code Builder" using the ellipses in the "On Click" field of the button's Property Sheet
• Typing in the code so I have:

Private Sub Update_Stock_Click()
CurrentDb.Execute "UPDATE tblPart SET QuantityInStock = QuantityInStock - " & Me.ctrJobParts.Form.Text11 & " WHERE PartID='" & Me.ctrJobParts.Form.Combo4 & "'"
End Sub

• Saving the code

But when I try to run the code via the button I just get an error message saying "Compile Error: Invalid outside procedure". I don't know what I'm doing wrong.

Thanks for all your help so far, you've been great.

[June7]

Forgive my typo. The name of quantity textbox is Text10. Or you might want to give the controls more meaningful names, like tbxQty and tbxPart.

Also, either rename the subform container control or use in the code the name you already assigned to it.

Why do you have the PartID combobox show part type and not the partID?

[LanguidAnomie]

Forgive my typo. The name of quantity textbox is Text10. Or you might want to give the controls more meaningful names, like tbxQty and tbxPart.

Also, either rename the subform container control or use in the code the name you already assigned to it.

Why do you have the PartID combobox show part type and not the partID?

How do I rename the subform container control? I changed the Caption, but I don't think that's right.

It displays Type by default and I don't know how to change it. It'd definitely be better if it showed PartID instead though, how can I do that?

[June7]

Form/subform in design view, click on the 'subform'. Should see a yellowish box around the subform. This means the container control is selected and the Properties dialog will show its properties. Click again and now the object in the container is selected and will see its properties. When the container is selected, look at the Name property. What does it say? As I said, I always name the container different from the object it holds. The code must refer to the container name, not the form that the container holds. Sometimes if the names are the same, this can confuse the code.

The PartID is not showing in the combobox because it is the first column and the ColumnWidths property has the first column width set to 0. Change the 0 to some other number.

[LanguidAnomie]

Form/subform in design view, click on the 'subform'. Should see a yellowish box around the subform. This means the container control is selected and the Properties dialog will show its properties. Click again and now the object in the container is selected and will see its properties. When the container is selected, look at the Name property. What does it say? As I said, I always name the container different from the object it holds. The code must refer to the container name, not the form that the container holds. Sometimes if the names are the same, this can confuse the code.

The PartID is not showing in the combobox because it is the first column and the ColumnWidths property has the first column width set to 0. Change the 0 to some other number.

Okay thanks. I've named the container ctrJobPart, as suggested. I also changed the name of Text10 to tbxQty, and the name of Combo4 to cboPartID. The code still isn't working though; I get the same error as before. When the error message appears, it also highlights this bit of code:
"UPDATE tblPart SET QuantityInStock = QuantityInStock - "
Is that of some significance?

Here's the code I'm using now:

Private Sub Update_Stock_Click()
CurrentDb.Execute "UPDATE tblPart SET QuantityInStock = QuantityInStock - " & Me.ctrJobPart.Form.tbxQty & " WHERE PartID='" & Me.ctrJobPart.Form.cboPartID & "'"
End Sub

[June7]

That certainly appears to be everything that I did. Here is the revised project that works for me. Also, be sure to have a record in the subform selected before clicking the button if you really want QuantityInStock to be changed. Otherwise, just nothing will happen. EDIT: Purpose served, file removed.

[LanguidAnomie]

That certainly appears to be everything that I did. Here is the revised project that works for me. Also, be sure to have a record in the subform selected before clicking the button if you really want QuantityInStock to be changed. Otherwise, just nothing will happen.

Thank you! That works perfectly. Thanks for all your help, it's been so useful. Is there some way I can give you rep or something?

Edit: Never mind, reputation has been submitted.