Shannan Casteel via AccessMonster.com wrote:[color=blue]
> 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.[/color]
-----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(strQuery)
For i = LBound(varParams) To UBound(varParams) Step 2
qd.Parameters(varParams(i)) = varParams(i + 1)
Next i
' Get the data
Set rs = qd.OpenRecordset()
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_AfterUpdate()
If Not IsNull(Me!txtPartNo) And Not Is Null(Me!txtQuantity) 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQtQbsIechKqOuFEgEQIvDQCeLJvvU9raXOO5eoYLOdsjjn e7FaIAoM+p
ujuNKiRfeK7R0N/6RQgKsMKj
=MiLc
-----END PGP SIGNATURE-----
