Help with sfrm coding

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Hello everyone. I hope you can help me. I am inexperienced
with VBA.

I have a form set up with our product specifications
displayed, like weight, resin, length, etc. In a subform,
I have the product as a pull-down (from when I set up the
table as a lookup) and I have a field called sample weight.

What I want is I want to use the pull down, grab the right
product, then in the form part, I would like this to
update to the correct product specifications. I was
thinking I could use the property's AfterUpdate feature,
but I don't know what code to put behind it.

Any help would be appreciated.
 
Assuming you have a table tblProducts with the fields
ProdID (Text), UnitWt (Double) and Resin (Text) and that
your form has a ComboBox for products (cboProduct) and
TextBoxes for wt (txtUnitWt) and resin (txtResin).

Check the Help files for the DLookUp method and try
something like this:

Private Sub cboProduct_AfterUpdate()

' if no product was selected, end the sub
If IsNull([cboProduct]) Or [cboProduct]="" Then
Exit Sub
End If

' otherwise...
' declare variables
Dim strProd As String
Dim dblWt As Double
Dim strResin As String

strProd = [cboProduct]

' look up details and populate controls
dblWt = DLookUp("[UnitWt]","tblProducts", _
"[ProdID]='" & strProd & "'")
[txtUnitWt] = dblWt

strResin = DLookUp("[Resin]","tblProducts", _
"[ProdID]='" & strProd & "'")
[txtResin] = strResin

End Sub

Hope this helps!

Howard Brody
 
Back
Top