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?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Yes, I can help, but first of all if there are likely to be more than one part then you should really have a Repair/Parts table and form, which is a Many to Many relationship.
The next problem that you have is what about when a repair uses more than 1 of the same part, for example a car engine may use 4 or 6 spark plugs, so you would need to subtract 4 or 6 from the quantity of parts, so if you have any repairs like that it needs to be a bit more flexible where the user can select the quantity required.
The quantity field can be set to default to 1.
The last problem that you have is when the user select the wrong part and then re-selects the correct part, you now have to put back the quantity to where it was for the first selection.
Also looking at your VBA code if it is possible for the repair to have more than one of the same part you would need to multiply the Cost by the quantity to get an overall cost.
As you can see I have been there and done this many times in the past and you need to cover lots of bases to foolproof the selection for the user.

If you want to go down the more complicated route let me know.
But what ever route you take the VBA code will require using a VBA Recordset to manipulate the Parts table, something like this.

Dim rs As Object, SQL As String
SQL = "SELECT Items.* " & _
"FROM Items " & _
"WHERE [ID] = " & Me.[ItemID]
Set rs = CurrentDb.OpenRecordset(SQL)
If Me.Qty > rs![Quantity in Stock] Then
MsgBox "Insufficient stock for this order. Maximum of " & rs![Quantity in Stock] & " in stock"
Me.Qty = rs![Quantity in Stock]
End If
With rs
.Edit
![Quantity in Stock] = ![Quantity in Stock] + oldqty - Me.Qty
.Update
.Bookmark = .LastModified
End With
rs.Close
Set rs = Nothing
Note that the code checks that there enough parts in stock to meet the requirement, you may or may not need that.
You could also have a "Re-order Level" in the Parts Table and if the stock level drops to or below the the re-order level due to the repair an Order Invoice could be genrated or at least a low stock level warning.
The variable in my code "oldqty" is the quantity that was first entered (it is zero for the first entry) and it resets the stock back to where it was before the entry in case the user changes the value say from 1 to 2.
 

JuniorLearner

Thread Starter
Joined
Jul 10, 2014
Messages
14
Thank you OBD very much for the very useful response...which is a real mind storm for me :)
I am trying to digest your ideas - as am not that gluey in access db as I said..
I will back in office on Tuesday, where I can trial your suggestions
meanwhile,I just saw your point of why do we need Parts - Repair to be many to many!!! as many parts maybe used in one repair records :)....
I will probably get back to you on Tuesday with more questions if you don't mind :)
meanwhile, thank you and have a great weekend :)
 

JuniorLearner

Thread Starter
Joined
Jul 10, 2014
Messages
14
Hi OBP,
I followed your recommendations, I keep receiving this: (Run time error '2465' Database can not find the field '|1' referred to in your expression.
Am stuck, any advices? :(
 

OBP

Joined
Mar 8, 2005
Messages
19,895
You have to ensure that the field names in the VBA code match the field names on your form and in your table or query.
The other point is if are trying to use the "oldqty" value then that has to be set up before you run the VBA code.
 

JuniorLearner

Thread Starter
Joined
Jul 10, 2014
Messages
14
Hi OBP,

I have run the code:

Private Sub Part1Cmb_Change()
'Auto populate Part1 cost based on Part1 Name
Me.Part1Cost.Value = Me.Part1Cmb.Column(1)

Dim rs As Object, SQL As String
SQL = "SELECT PartName " & _
"FROM Parts " & _
"WHERE PartName = " & Me.Part1Cmb
Set rs = CurrentDb.OpenRecordset(SQL)
If Me.Qty > rs![Quantity in Stock] Then
MsgBox "Insufficient stock for this order. Maximum of " & rs![Quantity in Stock] & " in stock"
Me.Qty = rs![Quantity in Stock]
End If
With rs
.Edit
![Quantity in Stock] = ![Quantity in Stock] + oldqty - Me.Qty
.Update
.Bookmark = .LastModified
End With
rs.Close
Set rs = Nothing
End Sub

It seems working at the beginning but the it produced error "Data type mismatch in criteria expression"

Am I right in using (PartName) here? as this is my primary key
Also I was wondering where did we defined oldqty?
As I said I am a beginner in Access db, I know basics, but when it gets to deeper stuff, I drown :(
 

OBP

Joined
Mar 8, 2005
Messages
19,895
The key field should really be an Autonumber field, but as you have set it up with the part name you can use it, providing that the Part1Cmb is also of the same type as the PartName, probably Text in this case.
If so then you can try the following
"WHERE PartName = '" & Me.Part1Cmb & "' "
the addition of the 2 ' changes the combo selection to text.

As far as the oldqty value is concerned that has first of all to be set up as a "Global or Public Variable" to allow it to be stored in memory between records and forms.
To do so you add a Module with the following
Public oldqty As Integer

You have to set it to Zero when you first open the Form, this goes in the Form's "On Current" event procedure
oldqty = 0
It's value then has to be "set" when you move to the Quantity field on the form using the Field's "Got Focus" or "On Enter" event procedure like this
If Not IsNull(Me.Quantity) Then oldqty = Me.Quantity


I have added an old Database with this code and much more for you to take a look at to see where it goes and what it does.
You need to look at the "New Purchase" form to see the code that you are using and when in the VBA Editor open the Module called Module1.
Note that I no longer use Menu Buttons, I use Tabbed forms instead, if you want a more up to date version I can probably find one for you, but I may have to email it to you as it may be too large for attaching on here.
 

Attachments

Last edited:

JuniorLearner

Thread Starter
Joined
Jul 10, 2014
Messages
14
Thank you OBP for all your help and for your patience with me :)
I managed to get this code running as per your instructions:


Private Sub Part1Cmb_Change()
Me.Part1Cost.Value = Me.Part1Cmb.Column(1) 'Auto populate Part1 cost based on Part1 Name
Dim rs As Object, SQL As String
SQL = "SELECT Quantity " & _
"FROM Parts " & _
"WHERE PartName = '" & Me.Part1Cmb & "' "
Set rs = CurrentDb.OpenRecordset(SQL)
If Me.Qty > rs!Quantity Then
MsgBox "Insufficient stock for this order. Maximum of " & rs!Quantity & " in stock"
Me.Qty = rs!Quantity
End If
With rs
.Edit
!Quantity = !Quantity + oldqty - Me.Qty
.Update
.Bookmark = .LastModified
End With
rs.Close
Set rs = Nothing
End Sub
And it's working fine without error codes, the only thing is it sets the Quantity record on Parts table always to 0. no matter what number I put in Qty text box in the form.
I have declared oldqty at public int, set it to 0 on form_oncurrent (), and added the code (If Not IsNull(Me.Quantity) Then oldqty = Me.Quantity) to on Enter event as you suggested, still no change!
Any thoughts?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
The problem may be that you are not setting the Quantity value.
It is not apparent from your main code.
I put that main code for updating the Table in to the Quantity field's After Update event, but you appear to have it in the combo's change event.
So it may be that the code does not what the value of me.Quantity actually is at that point.
 

JuniorLearner

Thread Starter
Joined
Jul 10, 2014
Messages
14
YES !!
I finally got it working :) :)


Thank you OBD you are such a champion...Thank you and thank techguy.org, I don't know what could I've done without you guys :)
Cheeeeeeers :)
 

JuniorLearner

Thread Starter
Joined
Jul 10, 2014
Messages
14
I have started writing a simple login form...seems will be a lot easier.
But if I stuck, I'll call for HELP again :)
Thank you again :)
 

JuniorLearner

Thread Starter
Joined
Jul 10, 2014
Messages
14
Ok OBP, I think I started calibrations too early :(
The code does work, but only for the first combo box!
I have another (Part2Cmb, Part3Cmb..unitl Part6Cmb) these set of combo boxes because there might be different parts used in the same job...I want to subtract 1 from each part type whenever the particular PartCmb is selected. but it kept on giving me the same Qty in the stock for both parts selected!!!
as you can see from this snap shot: attched


Any thoughts?
 

Attachments

Last edited:

OBP

Joined
Mar 8, 2005
Messages
19,895
For multiple parts you need a a sub table and sub form where each record is a part & quantity for that part.
Have a look at the database that I attached and look at the "New Invoice" form to see what I mean.
 

JuniorLearner

Thread Starter
Joined
Jul 10, 2014
Messages
14
Hi OBP
yes, it works perfectly now. I have used a sub form and it seems working just as I want it to be :)
Many thanks Master :)
 
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