Rookie SQL inside of VB question - MSAccess 2003

Question

Hey guys, can anyone help me with a simple question. I have this SQL statement below in a sub in VB within access from a button click, and I am new to this.

Here is what I typed:

Private Sub Command0_Click()
  Dim rs As Recordset
  Dim sql As String
  Dim db As Database

  Set db = CurrentDb

  sql = "SELECT * FROM Transactions"

  Set rs = db.OpenRecordset(sql)

  Do Until rs.EOF
    rs.MoveNext

  Loop
  If Not rs.EOF Then

    MsgBox "test"
  End If

End Sub

Ok, so how do I populate this?? Essentially I am justing starting out with this, so I am wondering how do I take this simple code, and run it like a query so that the resulting recordset opens.

Thanks!

Answer 1

Depending on what you are trying to do, you may be over-complicating this. A better approach would be to set the recordsource of the form (in the property sheet) to the transactions table then drop the fields you want on the form using the visual designer.

HOWEVER, If you really must do it this way, here is the code that will replace what you have and open a spreadsheet like view of the data in the transactions table.

Private Sub Command0_Click()
   docmd.Opentable "transactions"
End Sub

If you want to limit the results to a query, then first build the query and save it then use the following code.

Private Sub Command0_Click()
   docmd.OpenQuery "MyQueryName"
End Sub

To be extremely literal, your original code DID populate a recordset (in the rs object). You can access the fields by name using code in your while loop such as

debug.print rs("Field1")

Answer 2

you get at the columns of the record for the recordset like rs(0) or rs("columnname")....

if your transactions table has three columns named a, b, c you could get to it like:

rs(0)
rs(1)
rs(2)

or

rs("a")
rs("b")
rs("c")

Answer 3

You put your code inside the Do..Loop. This code will be evaluated for each record that is encountered.

Do Until rs.EOF
   Msgbox "The value for MyField is " & rst!MyField
   rs.MoveNext
Loop

Answer 4

some other remarks and advices:

1) Always indicate which type of recordset you are using. Here it seems to be a DAO recordset, so go for a complete declaration like:

Dim rs as DAO.recordset

Runing on another computer, and depending on the declaration order of ADODB and DAO libraries, the very same code can generate a bug.

2) To avoid any disturbing error message if no record is available, you can add an extra test, something like

if rs.recordcount = 0 then
Else
    rs.moveFirst
    ....

3) To browse the complete recordset with debug.print, you could do it this way. Just ad a 'm_debugLine' as string, and a 'fld' as DAO.Field in your declarations.

rs.MoveFirst
do while not rs.eof
    m_debugLine = ""
    for each fld in rs.fields
        m_debugLine = m_debugLine + vbTab + fld.value
    next fld
    debug.print m_debugLine
    rs.movenext
loop

4) you could even add a debug.print line to print out the field names before printing the data. I guess you'll find this one