saving data on the basis of relation

[firoz.raj]

Can anyone tell me .why i am getting error item cannot be found in the collection corresponding to the requested name or ordinal.error
is generating on bold line.Here is the code what i have written.

Private Sub Command1_Click()
y = MsgBox("do you add this record", vbYesNo + vbQuestion, "message")
If y = vbYes Then
Set rs = New ADODB.Recordset
'rs.Open "Select * from ProductProduction where Item_ID=" & Val(Text1.Text), con, adOpenDynamic, adLockOptimistic
rs.Open "Select * from ProductProduction inner join products on productproduction.id=products.item_id where products.item_code='" & Text1.Text & "'", con, adOpenDynamic, adLockOptimistic
rs.AddNew
rs.Fields("ProductProduction.item_ID") = Val(Text1.Text)
[B]rs.Fields("products.productname") = Text2.Text[/B]
rs.Fields("Incomming") = Text3.Text  'PURCHASE QTY
rs.Fields("req_by") = Text4.Text
rs.Fields("po_no") = Text5.Text
'rs.Fields("products.Unit") = Text6.Text
[B]rs.Fields("qty") = Val(Text7.Text) + Val(Text3.Text) [/B]'AVAILABLE QTY
rs.Fields("trans_date") = Date
rs.Update
rs.Close
MsgBox ("Data Saved")
Text1.Text = " "
Text2.Text = " "
Text3.Text = " "
Text4.Text = " "
Text5.Text = " "
Text6.Text = " "
Text7.Text = " "
'Label7.Visible = False
Text1.SetFocus
ElseIf y = vbNo Then
Text1.Text = " "
Text2.Text = " "
Text3.Text = " "
Text4.Text = " "
Text5.Text = " "
Text6.Text = " "
Text7.Text = " "
Text1.SetFocus
Else
MsgBox Err.Description
Text1.SetFocus
Exit Sub
End If
End Sub

[debasisdas]

you may not directly update the view , again that depends on your table structure.

[vb5prgrmr]

What it is saying in MS-Speak is that the field "Qty" does not exist. Make sure you have it spelled correctly and does exist within the database.

Good Luck