How to run update query many time for each item number?? 2

[afekri]

Hi,

I have a table include Item numbers and names ,...
and also another table which is Quantity in and Quantity Out and Quantity in Stock.

I have a Update query which will recalculate the quantity in stock for one item number which i put in criteria.

I have to run this update query one by one for all the item numbers which is in the first table..

How can I do?? Please help me..

Thanks for any idea..
Ali

 

[lameid]

RS.Edit 'can be implied (left out) with ADO Recordset; Required for DAO
RS!FieldName = [i]<value>[/i]
RS.Update

 

[afekri]

It's new things for me.

I have to use these lines in the query?? or in the vba??

I think to be used in vba but....

for example::

if recordset in vba is in record 4:
[item number]=27
[location]=3

so my query criteria in this time to be these values!!!

How query can get these data from VBA??

Thanks
Ali

 

[lameid]

Recordsets are VBA.

One way to run SQL in VBA is the RunSQL method of the Docmd object. strSQL is an SQL string...

Docmd.RunSQL strSQL

I still believe what you want to do can be done in a query.

 

[afekri]

Ok, Thanks to all ...

I used all of your advise and made this:

Dim a As String
Dim rs As New ADODB.Recordset
rs.Open "Select * FROM Inventory", CurrentProject.Connection, adOpenStatic, adLockReadOnly
rs.MoveFirst
Do Until rs.EOF
With rs
a = rs!ItemNumber
End With
DoCmd.RunSQL "INSERT INTO [Inventory items] ( [Item Number] ) SELECT Inventory.ItemNumber FROM Inventory WHERE (((Inventory.ItemNumber)=a));"


rs.MoveNext
Loop


But, I don't know why "itemnumber" value not save to "a" to make append correctly!!!

Please help me to correct it then this thread will close for me.

Thanks again to all.
Ali

 

[lameid]

DoCmd.RunSQL "INSERT INTO [Inventory items] ( [Item Number] ) SELECT Inventory.ItemNumber" & _ 
" FROM Inventory WHERE (((Inventory.ItemNumber)= " & a & "));"

 

[afekri]

Special Thanks to lameid and lespaul....

Finally I found what I need and many thanks to all.

 

[lameid]

I still say this should be all you need... no where no loop.

DoCmd.RunSQL "INSERT INTO [Inventory items] ( [Item Number] ) SELECT Inventory.ItemNumber" & _ 
" FROM Inventory"