Pulling info from linked table based on text box entry

[Shannan Casteel via AccessMonster.com]

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.

Please Help!

Shannan Casteel
--
Message posted via http://www.accessmonster.com

[MGFoster]

Shannan Casteel via AccessMonster.com 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(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-----

[Shannan Casteel via AccessMonster.com]

MGFoster,

Thanks for your help in this. I made a query with PartNumber,
PartDescription, and Price. Now, do I enter the

PARAMETER [this_part_no] Long;
SELECT Price
FROM PartPrices
WHERE Part_no = [this_part_no]

part under the criteria for PartNumber in the query? I tried doing that and
it kept giving me a slew of errors like incorrect syntax. So I haven't been
able to test your method yet.

Thanks for your help.

Shannan

MGFoster 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

[quoted text clipped - 6 lines]

I thought about using a combo box, but there are 57,000+ parts.

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[MGFoster]

Shannan Casteel via AccessMonster.com wrote:

MGFoster,

Thanks for your help in this. I made a query with PartNumber,
PartDescription, and Price. Now, do I enter the

PARAMETER [this_part_no] Long;
SELECT Price

FROM PartPrices

WHERE Part_no = [this_part_no]

part under the criteria for PartNumber in the query? I tried doing that and
it kept giving me a slew of errors like incorrect syntax. So I haven't been
able to test your method yet.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What I gave you is only an example SQL SELECT command that returns the
price of the indicated part number ([this_part_no] - the criteria). It
is meant to go in the SQL View of a query. It would only work if you
had a table named PartPrices & it was set up with the columns I
indicated in the SELECT command. It was not meant to be used "straight
out of the box." It is only an example. Learn SQL, it will help you a
lot.

In a QBE grid you drag the price column and the part number column onto
the grid. Uncheck the part number's "Show" check box. In the part
number's criteria cell put [this_part_no].

Run the query to see if it works - be sure to use a valid part number in
the parameter prompt (that will appear when you run the query). It
should return only one price for the part number (unless you have a
different PartPrices table set up).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQrUoechKqOuFEgEQLMLgCggQhF0bT1eUuY9Rq5lFd1kM cLAnkAoP40
Mn9X7MSdYSgP+hRmVIAWfvaT
=MzH1
-----END PGP SIGNATURE-----

[Shannan Casteel via AccessMonster.com]

MGFoster,

The SQL code wouldn't work. It won't let me save the query. An error
message appears saying there should be an expected INSERT, DELETE, etc....

MGFoster wrote:

MGFoster,

[quoted text clipped - 11 lines]

it kept giving me a slew of errors like incorrect syntax. So I haven't been
able to test your method yet.

What I gave you is only an example SQL SELECT command that returns the
price of the indicated part number ([this_part_no] - the criteria). It
is meant to go in the SQL View of a query. It would only work if you
had a table named PartPrices & it was set up with the columns I
indicated in the SELECT command. It was not meant to be used "straight
out of the box." It is only an example. Learn SQL, it will help you a
lot.

In a QBE grid you drag the price column and the part number column onto
the grid. Uncheck the part number's "Show" check box. In the part
number's criteria cell put [this_part_no].

Run the query to see if it works - be sure to use a valid part number in
the parameter prompt (that will appear when you run the query). It
should return only one price for the part number (unless you have a
different PartPrices table set up).

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1