Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-05-06
3
Medium Priority
?
173 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:vihar123
1 Comment
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 2000 total points
ID: 13944171
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question