How do I output the results of a recordset to a form

[bryant89]

This is my code.
Private Sub btnRunQuery_Click()

Dim dbs As Database, rst As Recordset

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("C:\AccessDB-equinoxserver\Equinoxserver.mdb&quot

' Count the number of records with a PostalCode
' value and return the total in the Tally field.
Set rst = dbs.OpenRecordset("SELECT Count " _
& "(copName) As Tally FROM dbo_tblContProj;&quot

' Populate the Recordset.
rst.MoveLast

With rst
Do While Not .EOF
Debug.Print "Contents of recordset #" & intCount
Debug.Print , .Fields(0), .Fields(1), .Fields(2)
.MoveNext
intCount = intCount + 1
Loop
End With
dbs.Close

End Sub

I dont have any errors but for some reason the debug.print statements are not working. Is it possible that this is shut off somewhere.

Any help would be appreciated.

 

[MissyEd]

Hello there,

Your problem lies here:

*** snip ***
' Populate the Recordset.
rst.MoveLast

With rst
Do While Not .EOF
*** snip ***

Because you have moved to the last record, the code after 'With rst' does not execute. Try changing the 'rst.Movelast' to 'rst.MoveFirst'

Good Luck! Missy Ed
Looking to exchange ideas and tips on VB and MS Access development as well as office 97 development. Drop me a line: msedbbw@hotmail.com

 

[bryant89]

cool thanks...
I have modified my code to look like this
With rs_sp

If .RecordCount = 0 Then
txtlogServiceLevel = ""
txtProjName = ""
txtFirmName = ""
Else
Do While Not .EOF
'Debug.Print "Contents of recordset #" & intCount
'Debug.Print , .Fields(0), .Fields(1), .Fields(2)
'MsgBox "Contents of recordset # " & Trim(.Fields(0)) & " " & Trim(.Fields(1)) & " " & Trim(.Fields(2)) & " " & .RecordCount
txtlogServiceLevel = Trim(.Fields(2))
txtProjName = Trim(.Fields(3))
txtFirmName = Trim(.Fields(4))
.MoveNext
intCount = intCount + 1
Loop
End If

End With

This works fine now but I want to be able to populate a drop down box with the values in the .Fields
As you can see I am populating text boxes but if I could populate a drop down box with the first 2 fields as the key for that record so once one of these records is selected by the user it will generate a report based on the record they selected but to generate the report 2 keys need to be passed.

I hope this make sense and thanks for your help.
I really appreciate it.

 

[Quehay]

If I understand your question (I admit I'm skimming). . .

Why not just populate combos with SQL

cboListValues.Row Source = "SELECT * FROM tblTable1"
cboListValues.ListRows = cbolistValues.RowCount

For the Record Count text Boxes why not set them equal to the values in a Stored/Compiled query--you could bind the form to it if you don't need values that can't be produced in the Aggregate query.

*Also are you Declaring and Setting the intCount variable somewhere?

 

[bryant89]

The reason why I am doing it this way is because I am making use of SQL server 2000 stored procedures that I have already written for the web part of this project. I need to produce flashy reports based on the data online so I have used access to connect to the sql server 2000 tables and I thought rather than trying to reproduce the exact same queries in Access I could reuse the stored procedures I have in SQL Server 2000. the values that are being returned are based on the stored procedure in SQL. Let me paste the the 2 functions I have. This way you will see exactly what I am trying to do.

I will admit this code isnt set up in the most appropriate way but... here it is
Option Compare Database
Option Explicit
Private Sub btnSearch_Click()
'Declare values used to store values input by the user
Dim gKeyword
Dim gProv As String
Dim gDisc As Integer

'Declare a string to store the value of the sql stored procedure to be executed and any paramaters that may need to be passed to it.
Dim SP_SQL As String

If IsNull(txtKeyword) Or Trim(txtKeyword) = "" Then
'This is set to from_Accessdb98ysLGw20gnl83ja9f because the SQL Server 2000 stored procedure will not accept a blank string
gKeyword = "from_Accessdb98ysLGw20gnl83ja9f"
Else
'Set the value of gKeyword to what the user has entered in the text field for the keyword
gKeyword = txtKeyword
End If

If IsNull(cmbProv) Or Trim(cmbProv) = "" Then
'This is set to 99 because the stored procedure in SQL Server 2000 accepts this value as being not selected
gProv = "99"
Else
'Otherwise Set the value to what the user has selected from the combo box
gProv = cmbProv
End If

If IsNull(cmbDisc) Or Trim(cmbProv) = "" Then
'This is set to 0 because the stored procedure in SQL Server 2000 accepts this value as being not selected
gDisc = 0
Else
'Otherwise Set the value to what the user has selected from the combo box
gDisc = cmbDisc
End If



'Set the value of SP_SQL to the string of what SQL server stored procedure to execute and any variables that need to be passed to that
'procedure
SP_SQL = "Execute stpProjSearchAllParams " & gKeyword & ", " & gProv & ", " & gDisc

'Call to the method ExecuteSPT passing it the value of SP_SQL
ExecuteSPT (SP_SQL)

End Sub

'This function will connect to the existing SQL Server 2000 database and execute the stored procedure that is passed to this function
'and return any records that exist
Function ExecuteSPT(sqlstr As String) As Integer

'Declare a counter to count how many records are returned.
Dim intCount As Integer

'Declare variables to store a Recordset, Query Definition and a Database.
Dim rs_sp As Recordset
Dim qdf As QueryDef
Dim db As Database

'Declare a variable to store the string being passed to this function.
Dim SP_SQL As String

'Declare a variable used to store the connection string
Dim gstrconnect As String
gstrconnect = "ODBC;DRIVER=SQL Server;SERVER=EQUINOXSERVER;UID=IUSR_EQUINOXSERVER;PWD=;WSID=EQUINOXSERVER;DATABASE=NETWORK"

'Set the Database variable to the current db
Set db = CurrentDb()

'Create a query definition object and assign the appropriate connection paramaters
Set qdf = db.CreateQueryDef("&quot
qdf.ReturnsRecords = True
qdf.Connect = gstrconnect
qdf.SQL = sqlstr
qdf.ODBCTimeout = 15

'Set the recordset variable to the querydefinition returned records
Set rs_sp = qdf.OpenRecordset
'Close the query definition
'qdf.Close

' Try printing results from each of the three SELECT
' statements.
intCount = 0

With rs_sp

If .RecordCount = 0 Then
txtlogServiceLevel = ""
txtProjName = ""
txtFirmName = ""
Else
Do While Not .EOF
'Need to populate combo box here with field values somehow. Rather than the txtboxes
'Otherwise only the last record will be shown in the text boxes
txtlogServiceLevel = Trim(.Fields(2))
txtProjName = Trim(.Fields(3))
txtFirmName = Trim(.Fields(4))
.MoveNext
intCount = intCount + 1
Loop
End If

End With
'for testing purposes show how many records are returned by setting intCount to the value of the txtbox
txtRecordCount = intCount
End Function