Shannan Casteel via AccessMonster.c om wrote:
I would like to be able to use 4 text boxes (i.e. Part #, Description,
Quantity, Price) for up to 40 parts. However, only the Part # and Quantity
will be entered. The Description and Price will be pulled from a linked
table. The Price field should take the price for the particular Part # and
multiply it by the quantity and return the value.
The linked table is set up with several columns including Part #, Description,
and Price.
I thought about using a combo box, but there are 57,000+ parts.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You can use DLookup() or create a function that runs a query to get the
info you want. I prefer the query 'cuz it is faster than DLookup() when
the table you are searching is > 1000 rows. Here is a function I use to
run queries that return 1 value in 1 row (what you need):
Function getQueryResult( strQuery As String, ParamArray varParams() As
Variant) As Variant
' Purpose:
' Run the indicated query and return the result:
' 1 value in 1 column.
' In:
' strQuery The indicated query
' varParams() The array of parameters.
' 2 elements make up 1 parameter object.
' Format: Parameter name, Parameter value
' Out:
' Variant The result of the query - NULL if no result.
' Errors Return the error to the calling routine.
' Created:
' mgf 25feb2003
' Modified:
'
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim i As Integer
Set db = CurrentDb
' Set up the query
Set qd = db.QueryDefs(st rQuery)
For i = LBound(varParam s) To UBound(varParam s) Step 2
qd.Parameters(v arParams(i)) = varParams(i + 1)
Next i
' Get the data
Set rs = qd.OpenRecordse t()
If Not rs.EOF Then getQueryResult = rs(0)
On Error Resume Next
rs.Close
qd.Close
db.Close
End Function
What you'd do is create a query - something like this:
PARAMETER [this_part_no] Long;
SELECT Price
FROM PartPrices
WHERE Part_no = [this_part_no]
Save it as "PartPrice. " Then in VBA code run the above function like
the following in the AfterUpdate event of the txtQuantity TextBox:
Make sure the column Part_no is indexed - so the query runs fastest.
Private Sub txtQuantity_Aft erUpdate()
If Not IsNull(Me!txtPa rtNo) And Not Is Null(Me!txtQuan tity) Then
Me!txtPrice = getQueryResult( "PartPrice" , _
"this_part_ no", _
Me!txtPartNo) * Me!txtQuantity
End If
End Sub
Add your own error traps and/or messages for wrong part number or
missing data elements.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQtQbsIechKq OuFEgEQIvDQCeLJ vvU9raXOO5eoYLO dsjjne7FaIAoM+p
ujuNKiRfeK7R0N/6RQgKsMKj
=MiLc
-----END PGP SIGNATURE-----
