how can i bring in two record sets in one while not

Hello,

i need to display values from two tables

so i made two queries with two record sets

so how can i use both the recor sets in same while not loop, when im using and its not giving the results

can somebody please help, thanks

rec1.Open "SELECT  anska.kostdet_mek_l2.ang_nr,anska.kostdet_mek_l2.ver_nr,anska.kostdet_mek_l2.stn_nr, anska.kostdet_mek_l2.line_nr,anska.kostdet_mek_l2.kon_atmo, anska.kostdet_mek_l2.entw_atmo, anska.kostdet_ele_l2.kon_hw,  anska.kostdet_ele_l2.messt_app, anska.kostdet_ele_l2.entw_atmo, anska.kostdet_ele_l2.vision_atmo, anska.kostdet_ele_l2.kon_sw, anska.kostdet_ele_l2.auspr_funkt_atmo, anska.kostdet_mek_l2.montage, anska.kostdet_mek_l2.auspr_atmo, anska.kostdet_ele_l2.instlln, anska.kostdet_ele_l2.schschr_atmo, anska.kostdet_ele_l2.funkt_atmo, anska.kostdet_mek_l2.proz_atmo, anska.kostdet_mek_l2.prufg_anlag_atmo, anska.kostdet_mek_l2.projektleitung FROM anska.kostdet_mek_l2, anska.kostdet_ele_l2 WHERE anska.kostdet_mek_l2.ang_nr(+) = anska.kostdet_ele_l2.ang_nr AND anska.kostdet_mek_l2.ver_nr(+) = anska.kostdet_ele_l2.ver_nr AND anska.kostdet_mek_l2.line_nr(+) = anska.kostdet_ele_l2.line_nr and anska.kostdet_mek_l2.ang_nr ='" & sel & "'", conn1

rec3.Open "SELECT kostdet.ang_nr, kostdet.ver_nr, kostdet.stn_nr, kostdet.line_nr, anska.work_sta.stn_desc, kostdet.ges_preis FROM kostdet, anska.work_sta WHERE kostdet.ang_nr = anska.work_sta.ang_nr AND kostdet.ang_nr = '" & sel & "'", conn1


While Not rec1.EOF And rec3.BOF
     
j = j + 1
   Tabelle2.Cells(j, 3) = (rec3.Fields("stn_nr").Value)
   Tabelle2.Cells(j, 4) = (rec3.Fields("line_nr").Value)
   Tabelle2.Cells(j, 5) = (rec3.Fields("stn_desc").Value)
   Tabelle2.Cells(j, 7) = (rec3.Fields("ges_preis").Value)
   
   Tabelle2.Cells(j, 9) = (rec1.Fields("kon_atmo").Value)
   Tabelle2.Cells(j, 10) = (rec1.Fields("entw_atmo").Value)
   Tabelle2.Cells(j, 11) = (rec1.Fields("kon_hw").Value)
   Tabelle2.Cells(j, 12) = (rec1.Fields("messt_app").Value)


Range("z:A" & j).Select
Selection.ClearOutline
Selection.Rows.Group


 Tabelle2.Cells(j, 1).EntireRow.Select
 
    With Selection.Interior
        .ColorIndex = xlNone
        .Pattern = xlSolid
         
    End With
   
      rec1.MoveNext

     
Selection.HorizontalAlignment = xlCenter
Selection.Font.Bold = True

Wend
z = j + 1
rec2.MoveNext
rec1.MoveFirst

Wend
j = j + 1
Range("z:A" & j).Select
'Selection.ClearOutline
Selection.Rows.Group


thanks in advance
vihar123Asked:
Who is Participating?
 
Arthur_WoodCommented:
in your code, you appear to have 1 WHILE statement:

While Not rec1.EOF And rec3.BOF

but TWO Wend statements, anf this will cause a compile time error. If you want to have two loops, then try this:


While not rec3.BOF
   rec1.MoveFirst
   While Not rec1.EOF
     
      j = j + 1
      Tabelle2.Cells(j, 3) = (rec3.Fields("stn_nr").Value)
      Tabelle2.Cells(j, 4) = (rec3.Fields("line_nr").Value)
      Tabelle2.Cells(j, 5) = (rec3.Fields("stn_desc").Value)
      Tabelle2.Cells(j, 7) = (rec3.Fields("ges_preis").Value)
   
      Tabelle2.Cells(j, 9) = (rec1.Fields("kon_atmo").Value)
      Tabelle2.Cells(j, 10) = (rec1.Fields("entw_atmo").Value)
      Tabelle2.Cells(j, 11) = (rec1.Fields("kon_hw").Value)
      Tabelle2.Cells(j, 12) = (rec1.Fields("messt_app").Value)


      Range("z:A" & j).Select
      Selection.ClearOutline
      Selection.Rows.Group


     Tabelle2.Cells(j, 1).EntireRow.Select
 
     With Selection.Interior
        .ColorIndex = xlNone
        .Pattern = xlSolid
         
     End With
   
      rec1.MoveNext

     
     Selection.HorizontalAlignment = xlCenter
     Selection.Font.Bold = True

   Wend ' End the rec1 loop, for each rec3 record
   z = j + 1

   rec3.MoveNext

Wend ' end the loop on rec3


AW
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.