CODEWITHSUNDEEP
vbams-access
EdChama
EdChama

Reputation: 1

How to populate a text box using dlookup from two combo boxes after update

I am trying to populate the textbox txtprice using the cboprod and cboweight as criteria.

Price is currency, product is text and size is number. 

Option Compare Database

Private Sub form_load()
  Me.txtqty = ""

  Dim price As Currency
  price = DLookup([price], "invent", "[product] = & cboprod &" And [size] = txtweight)

  Me.txtprice.Value = price
End Sub

Private Sub cboprod_AfterUpdate()
  DoCmd.Requery
End Sub

Private Sub cboprod_AfterUpdate()
  DoCmd.Requery
End Sub

Upvotes: 0

Views: 77

Answers (1)

AHeyne
AHeyne

Reputation: 3455

First some remarks:

  • I guess your DLookup never worked well:
    • [price] must be provided as a string: "[price]"
    • Your parameters for the condition must be concatenated into the string. You've had them as part of the string.
    • Because product is a string it's condition value must be encapsulated in 's.
    • The conditions syntax was spoiled up a bit...
  • Also it's not recommended to name fields/objects like reserved names: size is such a word.
  • Always try to provide the property you want to use explicitely. In this case it is .Value. It works without, but can cause trouble depending on which variable type it is assigned to. Then it could be assigned as the object you reference it.
  • Add an Option Explicit in the top of each module. This prevents usage of undeclared variables and so makes debugging easier.

I would encapsulate the functionality in a sub procedure and call it where/when needed.

This should be what you want:

Option Compare Database
Option Explicit

Private Sub form_load()
  RefreshPrice
End Sub

Private Sub cboprod_AfterUpdate()
  RefreshPrice
End Sub

Private Sub cboprod_AfterUpdate()
  RefreshPrice
End Sub

Private Sub RefreshPrice()
  txtprice.Value = DLookup("[price]", "invent", "[product] = '" & cboprod.Value & "' And [size] = " & txtweight.Value)
End Sub

Upvotes: 1

Related Questions