Updating with VBA

[Coldsteel]

Hello all,

I have a simple table called tblProductCode, with three fields Product, Payout and Value. What I am trying to do is write a simple If statement that will check to see if the cbo box Approved has the word “approved” in it and also compare the value in txt Gear with the Product field in tblProductCode. If they match then apply the value in field Payout to text box Amount. Any help will be greatly appreciated

Example

Table: tblProductCode

Field:
Product Payout Value
Boots 10 5
Hat 20 10

Here is my code.

Private Sub APPROVED_AfterUpdate()


If Me.APPROVED = "Approved" And Gear = [tblProductCode].[Product] Then
Me.Amount = [tblProductCode].[Payout]

End If

End Sub

 

[Galaxiom]

You can't set or test the value of the text box with the entire field column of a table. You need to lookup a specific record. For example:

Me.Amount = DLookUp("[Payout]", "[tblProductCode]", "[tblProductCode].[Payout] = [Forms]![Formname].[Gear]")

You also need more Normalisation. The product field should be using codes rather than their names. Most likely also with "Approved" in the combobox.