Autofill info from a table

[Leesha]

Hi,
I have a form with control txtItemID. Item Id is derived from a variety of input controls on the form. This form is bound to tblLandSales_Items. This is working fine. txtItemID corresponds to [ItemCode ]in tblSalesPrice. My goal is when the user hits cmdEnter, the corresponding price from tblSalesPrice fills into the control. This is a shopping cart type of a page. Is this possible and if so what would the code on the click method look like?

Thanks,
Leesha

[HansV]

Do you have a specific reason for wanting the user to click a button instead of looking up the price automatically?

[Leesha]

There is other code behind the button so I just figured I'd put it there. I don't have any preference other than I want the price to autofill from the tagle I mentioned vs the user selecting the price.

Leesha

[HansV]

A relatively easy solution would be to create a query based on tblLandSales_Items and tblSalesPrice, with a left join on Item ID vs ItemCode. Add all fields from tblLandSales_Items to the query grid, plus the price field from tblSalesPrice. Use this query as control source for your form. Add a text box bound to the price field to the form, and lock this text box.

If you prefer to use the button, you could add code like this to its On Click event procedure (you'll have to substitute the correct names):

Code: Select all

  If IsNull(Me.txtItemID) Then
    Me.txtPrice = Null
  Else
    Me.txtPrice = DLookUp("Price", "tblSalesPrice", "ItemCode=" & Me.txtItem)
  End If

This assumes that ItemCode is a number field. If it is a text field, use

Code: Select all

    Me.txtPrice = DLookUp("Price", "tblSalesPrice", "ItemCode=" & Chr(34) & Me.txtItem & Chr(34))

[Leesha]

Thanks for the two options Hans! I went with the button click since the form was already set up but will save the other option for future things.

Thanks,
Leesha