Help ..Update subform control2 with results from SELECT SQL based on subform control1

[smsscout]

Please excuse my use of MSaccess terminology, in advance. It has been
years since I wrote my last database.

I have an error "Run time 2113. Value entered is not valid for this
field."

For a Sales type database, I want to update the salesprice field on a
subform when the qty field is updated on the same subform. It needs to
lookup the salesprice in the itemstbl (for the itemid in the subform
for the matching itemid in the itemstbl) and update the unitprice field
in the subform.

NOTE 1. itemid field in subform has rowsource "SELECT itemstbl.* FROM
itemstbl ORDER BY itemstbl.itemname", set to 3 columns, display last
column (3 columns in itemstbl: itemid, itembarcode, itemname). 2. Me!
is the subform.

Am I missing someway to output the SQL result set to the control or am
I way off base in how to do this? Any help is appreciated.

Code:
Private Sub quantity_AfterUpdate()
Dim db As DAO.Database
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim blnQueryExists As Boolean
Set db = CurrentDb
blnQueryExists = False
For Each qdf In db.QueryDefs
If qdf.Name = "qGeneric" Then
blnQueryExists = True
Exit For
End If
Next qdf
If blnQueryExists = False Then
Set qdf = db.CreateQueryDef("qGeneric")
Else
Set qdf = db.QueryDefs("qGeneric")
End If

strSQL = "SELECT itemstbl.saleprice FROM itemstbl WHERE
itemstbl.itemid = " & Me![itemid].Column(0)
qdf.sql = strSQL

Me![unitprice] = qdf.sql
Set qdf = Nothing
Set db = Nothing
End Sub

 

[Arvin Meyer [MVP]]

Why don't you add a fourth column, also with a 0 width for the salesprice?
You could then refer to the fourth column in your combo box with something
like:

Me.txtSalesPrice = Me.cboItemID.Column(3)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[smsscout]

Thank you, I could kick myself...that was so much easier than all the
junk I was writing. I appreciate the help.

Kevin Dubois, MCSE