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 'Business Applications' started by JuniorLearner, May 14, 2015.

Thread Status:
Not open for further replies.
Advertisement
  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?
     
  2. OBP

    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.
     
  3. JuniorLearner

    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 :)
     
  4. JuniorLearner

    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? :(
     
  5. OBP

    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.
     
  6. JuniorLearner

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

    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.
     

    Attached Files:

    Last edited: May 21, 2015
  8. JuniorLearner

    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?
     
  9. OBP

    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.
     
  10. JuniorLearner

    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 :)
     
  11. JuniorLearner

    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 :)
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well done.
    I have a Pasword version if you want it.
     
  13. JuniorLearner

    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?
     

    Attached Files:

    Last edited: May 25, 2015
  14. OBP

    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.
     
  15. JuniorLearner

    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 :)
     
  16. Sponsor

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!

Loading...
Thread Status:
Not open for further replies.

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

  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