Dom McMillan
Dom McMillan

Reputation: 1

getting syntax error using microsoft access we have built

syntax error on the area of code E at the start 6th line

Private Sub Combo21_AfterUpdate()
 ' Update the row source of the cboProducts combo box
 ' when the user makes a selection in the cboCategories
 ' combo box.
 Dim mybuyprice, myproduct, myunits
**E   If DLookup("[Buy Price]", "GoodsIn_Buy_Price") = Null Then mybuyprice = "0" Else mybuyprice = DLookup("[Buy Price]", "GoodsIn_Buy_Price")
Me.Buy_Price = mybuyprice**

If DLookup("[Product]", "GoodsIn_Buy_Price") = Null Then myproduct = "Null" Else myproduct = DLookup("[Product]", "GoodsIn_Buy_Price")
Me.Product = myproduct

If DLookup("[Unit of Measure]", "Product_Unit_Check") = Null Then myunits = "0" Else myunits = DLookup("[Unit of Measure]", "Product_Unit_Check")
Me.Unit = myunits
   
Me.Refresh

Upvotes: 0

Views: 40

Answers (1)

Tim Williams
Tim Williams

Reputation: 166800

You have a bunch of repeated logic which would be better factored out into a separate function:

Private Sub Combo21_AfterUpdate()
 ' Update the row source of the cboProducts combo box when the
 '  user makes a selection in the cboCategories combo box.
    Me.Buy_Price = IfNull(DLookup("[Buy Price]", "GoodsIn_Buy_Price"), "0")
    Me.Product = IfNull(DLookup("[Product]", "GoodsIn_Buy_Price"), "Null")
    Me.Unit = IfNull(DLookup("[Unit of Measure]", "Product_Unit_Check"), "0")
    Me.Refresh
End Sub

'return `exp` if it's not Null, otherwise return `default`
Function IfNull(exp, default)
    If IsNull(exp) Then
        IfNull = default
    Else
        IfNull = exp
    End If
End Function

See also https://codekabinett.com/rdumps.php?Lang=2&targetDoc=coalesce-function-paramarray-keyword-vba-argument for a more flexible version.

Upvotes: 1

Related Questions