loop fields to update record using SQL (1 Viewer)

[khodr]

Hello Guys
Using SQL with Double Clicking the PurchaseOrderNo I want to update my data in the tblProducts for the data into pQTYinStock
I wrote the below code when I double click to run the Code everything seems to be OK, and no errors shows up, but my data is not updating enclosed find attached all the needed JPGS for my table design, relations between tables, table data, as well as form view, it is driving me crazy. something wrong in the code but i can't find it out. your help highly appreicated.

Private Sub PurchaseOrderNo_DblClick(Cancel As Integer)
Dim rs As DAO.Recordset
Dim dbData As Database
Dim strSQL As String
Set dbData = CurrentDb
strSQL = "Select * from tblProducts where pName = """ & Me.ProductType & """;"
Set rs = dbData.OpenRecordset(strSQL, dbOpenSnapshot)
Do While Not rs.EOF
rs.Edit
rs!pQtyInStock = Me.Requestedty
rs.Update
rs.MoveNext
Loop
End Sub

 

[JHB]

You can't edit a recordset of type "dbOpenSnapshot" you can use "dbOpenDynaset", but you should have got error message so I think rs.EOF is true, (the code is not running the loop). Because strSQL = "Select * from tblProducts where pName = """ & Me.ProductType & """;" is not correct!

strSQL = "Select * from tblProducts where pName = '" & Me.ProductType & "';"
or
strSQL = "Select * from tblProducts where pName =" & "'" & Me.ProductType & "'" & ";"

 

[khodr]

JHB, thanks for taking the time to go through my Post, I tried your code but still its not updating anything in the tblProducts, the corrected code
Private Sub PurchaseOrderNo_DblClick(Cancel As Integer)
Dim rs As DAO.Recordset
Dim dbData As Database
Dim strSQL As String
Set dbData = CurrentDb
strSQL = "Select * from tblProducts where pName = " & "'" & Me.ProductType & "'" & ";"
Set rs = dbData.OpenRecordset(strSQL)
Do While Not rs.EOF
rs.Edit
rs!pQtyInStock = Me.RequestedQty
rs.Update
rs.MoveNext
Loop
End Sub
but still not working any other way??.

 

[JHB]

Put in a break point in you code, (and you have also taken the dbOpenSnapshot away, but not insert the "dbOpenDynaset"). Take also a look of the strSQL if is as you expected. MsgBox(strSQL)

 

[khodr]

I didn't know what is the point break, and in which line it needs to be placed I inserted the "dbOpenDynaset" but still not working

 

[JHB]

Here is a link to how to set a break point.
http://www.techonthenet.com/access/tutorials/vbadebug/debug02.php

What about then strSQL is as you expected?

 

[khodr]

I will go through the LINK here is the attached file for the result of the sql it providing me a number

 

[JHB]

It is returning "'1'" it should be "Appel", "Orange" or "Carrot" if you want to find it in the "tblProduct" and therefor rs.EOF is true, (no recordset returned).
Take time to read the link I sent you, (5-6 small page with pictures), it will help you in the future and doing your error finding very much easier.