Results 1 to 7 of 7
  1. #1
    Coderama is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    5

    Subtrack from a table when specific value is given in 2 combo boxes

    I would to write some VBA to subtract -1 from a table if a certain value of 2 comboboxes is being entered for each record. I have tried so many things and i am learning vba but im getting a bit lost here.


    Basically what i try to do is something like:
    Code:
    Private Sub ...?_afterupdate
    IF Me.combobox1.value = "yellow" AND Me.combobox2.value = "tshirt" THEN
    -1 FROM table [stock] field [yellowshirts]
    End IF
    End Sub
    I know im getting pretty close with the first line, but i dont get how to formulate the second. Also dont know on wich field i put the afterupdate?
    Please help because im stuck. Thanks in advance guys!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Are you trying to keep track of inventory? What I usually do is store the value of what is on the packing list before the item is received. Then, when it is received, store the quantity received and the quantity damaged in another table. Then, store the value of quantity ordered in another table. Then, store the quantity shipped in another table.

    With all of that, use reports and or queries to determine history and quantities.

  3. #3
    Coderama is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    5
    Hi Itsme! Thank you for the swift reply
    That sounds really good and i would like to do that with another Database i have.
    Right now i only need this to work for only this very article since its a very different process on the background.
    I just want those 2 combo-boxes to subtract 1 from another table field. That would be very sufficient at this moment.
    Just cant reference/adres that table because i dont know how. Could you maybe help me with the code i wrote?

    Maybe like this?
    (doesnt work either )

    Private Sub ...?_afterupdate
    IF Me.combobox1.value = "yellow" AND Me.combobox2.value = "tshirt" THEN
    [stock.yellowshirts] = -1
    End IF
    End Sub

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Hmmmm. I am thinking the only way would be to use DAO and open a recordset of the table. Many factors would need to be understood/defined and your code would be complex. I will dream something up that may help, in part. However, I urge you to look at the fact you are building on a bad design and when you do this, you will always paint yourself into a corner.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You could use a series of queries to retrieve and then update the values. The other option would be to use DAO. Here is an example using DAO.

    Code:
    'Somewhere in the table is a record we need to edit
    Dim lngRecordID As Long
    lngRecordID = 2
    
    Dim a As Long
    Dim b As Long
    
    'validate data
        If Me.txtValueA.Value > 0 Then
            a = Me.txtValueA
        Else
        Exit Sub
        End If
        
            If Me.txtValueB.Value > 0 Then
                b = Me.txtValueB
            Else
            Exit Sub
            End If
    
    'open a dynaset
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblValues", dbOpenDynaset)
    
    'Navigate to the correct record and retrieve the value to be edited
    rs.FindFirst "KeyValue =" & lngRecordID
    
    Dim lngCurrentValue As Long
        If rs![NumberA] > 0 Then
            lngCurrentValue = rs![NumberA]
        Else
            lngCurrentValue = 0
        End If
    
    'If we have a number we can do math on, let's update the record
        If lngCurrentValue > 0 Then
            rs.Edit
            rs![NumberA] = lngCurrentValue - a
            rs.Update
            MsgBox "Update successful"
        End If
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing

  6. #6
    Coderama is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    5
    Geez dont know what to say. Many many thanks would be in order
    Going through length to figure it out. Youre legend! I'll look into it.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Here is a sample DB that includes the table and the correct control names. Since you are trying to reverse engineer it, this will help. Just understand that this is only a guess for part of the solution.
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 09-10-2014, 06:56 PM
  2. Replies: 5
    Last Post: 08-06-2014, 02:39 PM
  3. Replies: 11
    Last Post: 08-06-2014, 09:47 AM
  4. If...Then type statement for combo boxes in table
    By whitneynf08 in forum Access
    Replies: 3
    Last Post: 09-11-2013, 02:56 PM
  5. Pivot table and combo boxes
    By compooper in forum Forms
    Replies: 1
    Last Post: 08-02-2011, 01:10 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums