Add a textbox value to a value in a table

[ewan97]

I know this is a basic question, I have a form where there is just a combobox and a textbox, the user uses the combobox to select the record they want to change and then they enter a value in the textbox which needs to be added to a field in that record.

this is the vba I tried to write with my very limited knowledge:

StockNumber.Value = (StockNumber.Value) + (DeliveryValue.Value)

I get a runtime error "object required" and I have no idea what to do.
The textbox is called DeliveryValue and the record I want to add it to is called StockNumber in a table called Products.

 

[AOB]

You need to use a recordset, or some SQL, to update the record

What is the combobox called and what is the name of the field in the Product table to which it refers (i.e. in order that the selected item in the combobox identifies a specific record?)

 

[ewan97]

The combobox refers the the ProductName field and is called Combo0, heres a screenshot of the Products table.

 

[AOB]

This is an example recordset method :

Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
 
strSQL = "SELECT [Products].* " & _
           "FROM [Products] " & _
           "WHERE [ProductName] = " & Chr(34) & Me.Combo0.Value & Chr(34)
 
Set dbs = CurrentDb
 
With dbs
 
    Set rst = .OpenRecordset(strSQL)
 
    With rst
 
        If .RecordCount > 0 Then
 
            .Edit
            .Fields("StockNumber") = .Fields("StockNumber") + Me.DeliveryValue.Value
            .Update
 
        End If
 
        .Close
 
    End With
 
End With
 
Set rst = Nothing
Set dbs = Nothing

This is an example SQL method :

Dim dbs As Database
Dim strSQL As String
Dim varCurrentValue As Variant
 
varCurrentValue = DLookup("[StockNumber]", "[Products]", "[ProductName] = " & Chr(34) & Me.Combo0.Value & Chr(34))
 
strSQL = "UPDATE [Products] " & _
           "SET [StockNumber] = " & varCurrentValue + Me.DeliveryValue.Value & " " & _
           "WHERE [ProductName] = " & Chr(34) & Me.Combo0.Value & Chr(34)
 
Set dbs = CurrentDb
dbs.Execute strSQL
Set dbs = Nothing

(Above is complete aircode by the way - not tested so syntax may not be spot on!!)

 

[ewan97]

Sorry nothing happened when I ran the VBA, also I'm putting all this behind a button would that have any effect? I just pasted all of that vba after

Private Sub btnFinish_Click()

Also where do I put SQL for a button? I've never had to do that before.

 

[AOB]

Are you sure nothing happened? The form may not necessarily refresh, did you check the value in the actual table to see if it has been updated?

Putting it in the Click event of a button is perfectly fine

Do you have "Option Explicit" at the top of the form's code module and does the code compile without error? Can you step through the code line by line, adding watches where appropriate, and validate that your variables & controls are holding the expected values as you go through it?

 

[ewan97]

I changed it to option explicit and that did nothing, yes I've checked the values and nothing has changed.

There are no errors when I run the code and sorry I don't know how to step through it and check things

 

[AOB]

Well - for starters, I don't think you (or anybody for that matter) should delve into VBA before understanding how to debug your code. It is nigh-on impossible to isolate problems and identify gaps in your perceived process without the ability to step through it line by line and watch variables.

If you can't debug, then you can't understand what your code is doing. If you can't understand what your code is doing, how can you trust it? And how can you fix it when it goes wrong? Thus, debugging is an absolutely essential aspect of incorporating VBA into a project (be it in Access, or Excel, or any Office application)

Chip Pearson has a good introductory guide to VBA debugging - here's a link

It's more than likely that, once you know how to debug, you'll be able to see what's going wrong and fix it yourself. You will expect a variable (or object, or control, or record...) to hold one value but you'll discover that, at runtime, it actually holds another. Then you can trace it back to some oversight earlier in the sequence and rectify it. Then step through it again to see if the change has made a difference.

If there are no errors when you run the code, and you can't confirm whether or not all the objects and variables are doing what they're supposed to be doing, then it's impossible for me to help you identify what's going wrong. So in order for me to help, I need you to debug it, then come back to me with a specific problem.