Link to home
Start Free TrialLog in
Avatar of ircpamanager
ircpamanager

asked on

how do i display results from a query where there is multiple rows returned?

i have an app that i need to display query results  on a form. The user then needs to pick which result from the query is correct(this will then take them to another form and populate text boxes with the correct data. The results from the query can be in a textbox or whatever, as long the user can click a button next to the query result they need.
this is what i have so far(this is the results form, it holds the results from the query)
Private Sub Form_Load()
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strUser As String
Dim strSQL As String
Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset
objConn.CursorLocation = adUseServer
objConn.Open sConnPautil

strUser = frmMain.Text1.Text
strSQL = "Select * from tblOwnerData where HomesteadID Like " & "'" & strUser & "%'"
objRS.Open strSQL, objConn, adOpenDynamic, adLockOptimistic


End Sub


Thank in advance.
Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image

You basically loop through the records until you hit EOF:

    While Not objRS.EOF
        '// Code to display value from current record
        text1.Text = rs("AFieldName")      '// Just a sample
    Wend


Obviously the precise contents of the loop will depend on what control(s) you are using to display the info.
Avatar of leclairm
leclairm

Just use a data grid.

Set YourDataGrid.Datasource = objRs

Give this a try:

If not objRS.BOF and not objRS.EOF then
  do while not objRS.EOF
    '  
    txtBox = txtBox & objRS!field & vbcrlf
    objRS.MoveNext
  loop
end if

Preece
<The results from the query can be in a textbox or whatever, as long the user can click a button next to the query result they need. >

I don't think a list of results in a text box will allow the user to < click a button next to the query result they need >

Just go to your components list, add the Microsoft Datagrid control, then put it on your form.

After you load your objRs recordset, put the following:

Set YourDataGrid.Datasource = objRs

You will then have a grid containing a row for each record returned by the query.

You can then use YourDataGrid.columns(1).Value to refer to the currently selected row/value.

You can even use the recordset directly as the record point will be moved each time the user selects a row in the grid.

objRs.Fields("yourfield")

will give you the value of the field currently selected in the grid.
Avatar of ircpamanager

ASKER

ok can get it to display recoeds returned. Now how do allow the user to select the right row to open another form and populate the textboxes with the info from the row the user selected.
Sorry for the horrible grammar, believe it or not English is the only language I speak.
What I meant ot say is:
ok can get it to display records returned. Now how do I allow the user to select the row they need(this will then  open another form and populate the textboxes with the info from the row the user selected.)
<ok can get it to display records>

Are you using a grid????

If so, you could do something like this:

frmDetailForm.Show
frmDetailform.txtName = objRs.fields("Name")
frmDetailform.txtLocation = objRs.fields("Location")

When the user selects a row in the grid, the record point in your recordset will be the same as the row in the grid.
i must be missing something.
frmDetailForm.Show
frmDetailform.txtName = objRs.fields("Name")
frmDetailform.txtLocation = objRs.fields("Location")
Put this on datagrid event or what?
Right now I have a datagrid displaying three results from the query.
The user will then need to select wich result in the datagrid pertains to there situation.
Then when the user selects one of the three rows(just example) it will open frmOwner and popultae textboxes with info like ownerNmae, ownerAdd, etc...
thanks for all the help so far.
If you have a button beside the grid that will open another form, put the code there.

frmDetailForm.Show
frmDetailform.txtName = objRs.fields("Name")  
frmDetailform.txtLocation = objRs.fields("Location")

This was just an example, you would need to replace frmDetailForm with the name of the form you created, along with the actual text boxes on the form which will correspond to the actual fields returned by your query.

Or you could put the code in the double click event of the datagrid.
Therefore it would be:

frmOwner.show
frmOwner.ownername = objRs.fields("ownername")
frmOwner.ownerAdd = objrs.fields("ownerAdd")
....
I put this on datagrid click event. The only problem is that it only returns first row.
I think there is an issue with the click event.  I believe you first need to "click" the row for it to change.  Once it's changed, then your pointer will be to that particular row.  Try putting the code in a button.  The user will click the row he/she wants, then press the button to load the form and populate the text boxes.
Ok  i put this code in a button click:
Dim objRS As ADODB.Recordset
Dim strUser As String
Dim strSQL As String
  Set objConn = New ADODB.Connection
  Set objRS = New ADODB.Recordset
  objConn.CursorLocation = adUseClient
  objConn.Open sConnPautil

  strUser = frmMain.Text1.Text

  strSQL = "Select * from tblOwnerData where HomesteadID Like " & "'" & strUser & "%'"
  objRS.Open strSQL, objConn, adOpenDynamic, adLockOptimisti
frmOwner.Show
frmOwner.ownername = objRS.Fields("OwnerName")
frmOwner.ownername = objRS.Fields("OwnerAdd")

But when i click on desired row in datagrid i still only get first row of query results in textboxes on frmOwner.
Thanks again!!!!!
got it to work with:
frmOwner.Show
frmOwner.ownername = Form1.DataGrid1.Columns("OwnerName")
is this going to be ok?
ASKER CERTIFIED SOLUTION
Avatar of leclairm
leclairm

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
still had to use frmOwner.ownername = Form1.DataGrid1.Columns("OwnerName") to pick the record that the mouse selected.
Well if the recordset isn't public in the form, the button won't be able to access the fields.  But it boils down to the same thing wether you access the recordset directly or if you use the columns in the grid that are pointing to the recordset.

Either way, glad you got it working.