?
Solved

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

Posted on 2006-05-17
17
Medium Priority
?
181 Views
Last Modified: 2010-05-01
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.
0
Comment
Question by:ircpamanager
17 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16700555
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.
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16700836
Just use a data grid.

Set YourDataGrid.Datasource = objRs

0
 
LVL 12

Expert Comment

by:Preece
ID: 16700895
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Expert Comment

by:leclairm
ID: 16701002
<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.
0
 
LVL 5

Author Comment

by:ircpamanager
ID: 16701215
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.
0
 
LVL 5

Author Comment

by:ircpamanager
ID: 16701248
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.)
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16701331
<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.
0
 
LVL 5

Author Comment

by:ircpamanager
ID: 16701519
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.
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16701594
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.
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16701646
Therefore it would be:

frmOwner.show
frmOwner.ownername = objRs.fields("ownername")
frmOwner.ownerAdd = objrs.fields("ownerAdd")
....
0
 
LVL 5

Author Comment

by:ircpamanager
ID: 16701700
I put this on datagrid click event. The only problem is that it only returns first row.
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16701735
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.
0
 
LVL 5

Author Comment

by:ircpamanager
ID: 16701911
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!!!!!
0
 
LVL 5

Author Comment

by:ircpamanager
ID: 16701960
got it to work with:
frmOwner.Show
frmOwner.ownername = Form1.DataGrid1.Columns("OwnerName")
is this going to be ok?
0
 
LVL 11

Accepted Solution

by:
leclairm earned 2000 total points
ID: 16701994
The following code will load your grid:

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, adLockOptimistic
  set datagrid1.datasource = objRS

Now, in the click event of a button, put:

frmOwner.Show
frmOwner.ownername = objRS.Fields("OwnerName")
frmOwner.ownername = objRS.Fields("OwnerAdd")


Once the grid is loaded, select a row, then press the button that loads frmOwner.
0
 
LVL 5

Author Comment

by:ircpamanager
ID: 16702995
still had to use frmOwner.ownername = Form1.DataGrid1.Columns("OwnerName") to pick the record that the mouse selected.
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16718942
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

850 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