call recordset from recordset

[Goh]

i have a table wirth two columns. column one for eg. has 5 rows of number 1. column 2 has A,B,C,D,E for each row. then there is for eg 3 rows of the number 2 in column one. and the respectively rows have values F,G,H..and so on....now i want to display on a form for eg a textbox with number 1 shown, and 5 rows of data (ie A,B,C,D,E) shown on 5 textbox(visible). when a next button is click, number2 is shown and the respective 3 data shown in the textboxes( from 5 texboxes change to 3 textbox visible).....i am using ADO recordset to extract the data from many tables...ie 5 rows of 1 and 3 rows of number2...how do i continmue from here to achieve the goal?

[leinad31]

Is this a heirarchical recordset or a join table?

[Goh]

it is the join table hwich i show previously..

[leinad31]

The records are already order, grouped?

Use a control array of textboxe, best if you used a listview instead so you wont have to worry about positioning and for cases wherein there are lots of records (textboxes wont fit anymore)

Use a flag, you keep track of the value of the flag. The flag is set to value in column 1. You movenext through the recordset until column 1 is no longer equal to flag. In which case you stop extracting info for "record 2" and do a moveprevious to position to the lst record for value flag.

When your gonna view the next batch, you update the flag to the new "column 1, record 1" value. You clear the listview or unload all textboxes except textbox(0).

BTW, initialize the flag to the first record in "column 1" and the listview to its respective records.

[Goh]

i roughly understand wat you mean and if there are any sample codes or guides or examples on website..i would really appreciate....

[VB_client]

try this one.......


First have 2 Label controls one with Index as 0.

Private sub Btn_Click()
rs1=select distinct(col 1) from table
do until rs1.eof
rs2=select (col 2) from table where (col 1) = rs1(0)
--clear all arrays of label2
if Not rs2.eof then
intLoad=Label2.ubound
load label2(ubound) and set the visible=true and top=(value)
label1.caption=rs1(col 1)
label2(ubound).caption=rs2(col 2)
end if
rs2.close
rs1.movenext
loop
rs1.close
End Sub

[Goh]

may i know wat is this intload=??

[leinad31]

He didnt put the code for clearing the control array... but anyway, lets assume you left with just Label(0).

What he does is store the upperbound of the control array to variable intLoad. He should have then loaded another instance of Label at Label.Ubound + 1... guess he forgot the + 1... for each "record 2"your gonna display. So if you have 3 "record 2", you will create Label(1), Label(2), Label(3) control instances and assign record value to each after each control's creation (Load statement).

[VB_client]

thank you leinad31

that's right...