walk through results from query 1

[xenofoob]

Hello,

I have a query that get results from my database.

Database 1
+------------------------------------+
| ID | OrderId | Name | AMOUT | Type |
+------------------------------------+

I have another database with the total items in stock

Database 2
+---------------------------+
| ID | Name | STOCK | price |
+---------------------------+

What I do is select all the records from database1 where OrderId = 1

The results are stored in l_rsTmp

I found some code on the web:

      For i = 1 To l_rsTmp.Fields.Count
        MsgBox (l_rsTmp.Fields(i - 1).)
      Next

That should walk through the results. How can I get the ID and the AMOUNT from one row? This information should be used in the second query to decrease the STOCK value for the correct ID.

Suggestions are very welcome!

 

[mrmovie]

For i = 1 To l_rsTmp.Fields.Count
'is it something like
If l_rsTmp.Fields(i - 1).Name = "ID" Then
sID = l_rsTmp.Fields(i - 1).Value
End If
Next

 

[xenofoob]

aaaah.... my l_rsTmp.Fields.Count is per field/column

I have also walk through the rows probably... or not?

 

[mrmovie]

While Not l_rsTmp.EOF
For i = 1 To l_rsTmp.Fields.Count
'is it something like
If l_rsTmp.Fields(i - 1).Name = "ID" Then
sID = l_rsTmp.Fields(i - 1).Value
End If
Next
l_rsTmp.MoveNext
Wend

 

[xenofoob]

Thanks! works great....

//offtopic
What is an effective way to decrease the amount in database2?

Is there somekinde of WHERE IN LIST([items]) command that I can use? Or should I query my database per row.....

While Not l_rsTmp.EOF
    For i = 1 To l_rsTmp.Fields.Count
       'is it something like
       If l_rsTmp.Fields(i - 1).Name = "ID" Then
           sID = l_rsTmp.Fields(i - 1).Value
       End If
    Next
    [QUERY EXECUTED HERE??]
    l_rsTmp.MoveNext
Wend

 

[mrmovie]

sorry i dont understand what you are trying to achieve