VBA Loop

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I posted this question in Forms Coding but couldn't get it answered. Probably
my fault for not explaining properly. Here is what I am trying to do. With
the results I am going to use the SendObject method to send the result info
to different users. All I want to do is dump text in an email message...no
attachments. This I can do. I am having trouble getting the results though.

I have a table "Jobs" and a table that is related to it "Orders". Each job
may have many orders. I want to create a seperate email for each job listing
associated with outstanding orders. I cannot figure out the syntax and always
get confused with looping.

Here is an example of what I need:

Loop through jobs with orders
Debug.print JobNumber
debug.print each OrderNumber associated
with this job


EG

RESULT 1:
Job 1000
Order 1
Order 3
Order 17


RESULT 2:
Job 1375
Order 4
Order 8

RESULT 3:
Job 2567
Order 17
Order 32
Order 52



etc.


Thanks
 
EG,

If you have a normalized database it should be easy.

Start by selecting all jobs using a SQL select statement and create a
recordset. Then loop until end of file (EOF). Next create another record
set of orders that are associated with the current job and is outstanding.
Loop through this recordset until EOF. Keep looping until no more jobs.
Notice the movenext statement, that is required to move the cursor to the
next row. Here is some code. I wrote it off the top of my head and have
not tested it.

strsql = "Select JobID, OrderID from tblJobs"
set rst = dbs.openrecordset(strsql)
while not rst.eof
strsql = "select * from tblOrders where JobID = " & rst!JobID & "
OrderStatus = 'outstanding'"
set rst2 = dbs.openrecordset(strsql)
while not rst2.eof
put together you email message
rst2.movenext
loop
rst.movenext
loop

Two loops, and inner to manage orders and an outer to manage jobs.

I showed this example in DAO. You can do the same thing in ADO just
different syntax for the recordset creation.

I left out the dim statements and the "set dbs=currentdb()".

I hope that helps.
 
Back
Top