Rookie SQL inside VB question - MSAccess 2003
Hey guys can someone help me with a simple question. I have this SQL statement below in a sub in VB accessed by button click and I am new to this.
This 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 can I fill this in? Basically, this is where I start, so I'm wondering how I can take this simple code and run it as a query to open the resulting recordset.
Thank!
source to share
some other notes and tips:
1) Always indicate what type of recordset you are using. Here it appears to be a DAO Recordset, so skip to the full declaration like:
Dim rs as DAO.recordset
Running on a different computer, and depending on the order in which the ADODB and DAO libraries are declared, the same code may generate an error.
2) To avoid the error message if the entry is not available, you can add an additional test like
if rs.recordcount = 0 then
Else
rs.moveFirst
....
3) To view the complete set of records using debug.print, you can do it this way. Just declare 'm_debugLine' as string and '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 the field names before printing the data. I think you will find this
source to share
Depending on what you are trying to do, you may be overcomplicating it. A better approach would be to set the source of the form's records (in the property sheet) to the transactions table and then discard the desired fields in the form using the visual designer.
HOWEVER, if you really need to do this, here is the code to replace what you have and open the table, for example the data view in the transaction table.
Private Sub Command0_Click()
docmd.Opentable "transactions"
End Sub
If you want to limit the results to a query, first create a query and save it, then use the following code.
Private Sub Command0_Click()
docmd.OpenQuery "MyQueryName"
End Sub
To be as literal as possible, your DID source code populates the recordset (in the rs object). You can access the fields by name using the code in your while loop like
debug.print rs("Field1")
source to share