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.
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.
Just use a data grid.
Set YourDataGrid.Datasource = objRs
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
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).Va lue 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.
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).Va
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.
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.
ASKER
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.)
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.
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.
ASKER
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.
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.
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")
....
frmOwner.show
frmOwner.ownername = objRs.fields("ownername")
frmOwner.ownerAdd = objrs.fields("ownerAdd")
....
ASKER
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.
ASKER
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!!!!!
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!!!!!
ASKER
got it to work with:
frmOwner.Show
frmOwner.ownername = Form1.DataGrid1.Columns("O wnerName")
is this going to be ok?
frmOwner.Show
frmOwner.ownername = Form1.DataGrid1.Columns("O
is this going to be ok?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
still had to use frmOwner.ownername = Form1.DataGrid1.Columns("O wnerName") 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.
Either way, glad you got it working.
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.