Displaying A Recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can't figure out how to display the results of a query that runs based on user inputs. The code is below.

Private Sub UserInput_Click()
On Error GoTo Err_UserInput_Click

Dim CurConn As New ADODB.Connection
Dim outputTB As New ADODB.Recordset
'Dim curDB As Database
Dim intPMvar As Integer

Dim SQLstmt As String

'Set curDB = CurrentDb

CurConn.Open CurrentProject.Connection

intPMvar = Me![Frame91]

Select Case intPMvar

Case 1 'Returns xxx Total

SQLstmt = "SELECT pm, sum(bps) FROM rpm_data WHERE pm = 'xxx' GROUP BY pm"
outputTB.Open SQLstmt, CurConn, adOpenStatic, adLockReadOnly, adCmdText


Now from here how do I get the results of the query to display.
 
If you only want to display the results of that query, maybe it is enough if
you use a ListBox. However, the code will be much simplier. For example

Private Sub UserInput_Click()
On Error GoTo Err_UserInput_Click

Dim intPMvar As Integer
Dim SQLstmt As String

intPMvar = Me![Frame91]

Select Case intPMvar

Case 1 'Returns xxx Total

SQLstmt = "SELECT pm, sum(bps) FROM rpm_data WHERE pm = 'xxx' GROUP
BY pm"

Me.MyListBox.RowSource SQLstmt
Me.MyListBox.Requery
.... ...

etc.

If you know the query will always have the same number of columns, you can
prepare the listbox in design view with the apropriate properties. If not,
you can add some lines to your code to tell the control the number of
columns using ColumnCount and ColumnWidths properties.

HTH

--
Saludos desde Barcelona
Juan M. Afan de Ribera
<MVP Ms Access>
http://www.juanmafan.tk
http://www.clikear.com/webs4/juanmafan


Bob Ewers said:
I can't figure out how to display the results of a query that runs based
Click to expand...
on user inputs. The code is below.
Private Sub UserInput_Click()
On Error GoTo Err_UserInput_Click

Dim CurConn As New ADODB.Connection
Dim outputTB As New ADODB.Recordset
'Dim curDB As Database
Dim intPMvar As Integer

Dim SQLstmt As String

'Set curDB = CurrentDb

CurConn.Open CurrentProject.Connection

intPMvar = Me![Frame91]

Select Case intPMvar

Case 1 'Returns xxx Total

SQLstmt = "SELECT pm, sum(bps) FROM rpm_data WHERE pm = 'xxx' GROUP BY pm"
outputTB.Open SQLstmt, CurConn, adOpenStatic, adLockReadOnly, adCmdText


Now from here how do I get the results of the query to display.
Click to expand...
 
Back
Top