I should give up programming!

  • Thread starter Thread starter Southern at Heart
  • Start date Start date
S

Southern at Heart

I can't figure it out. Once I have rst set as the query, how do I get the
data out of it?

strSQL = "SELECT DISTINCT tblContacts.CompanyName " & vbCrLf & _
"FROM tblContacts;"
Set rst = CurrentDb.OpenRecordset(strSQL)

....this should give me a list of company names...
so, now I want to loop through them, like this:

do while not rst.EoF
...but now how to get the name
rst!CompanyName doesn't work
deBug.print rst.Fields doesn't work
where is my text?

Pathetically Dumb,
me
 
Are you sure your recordset has records? Debug.Print rst!CompanyName
*should* have worked. You can also try the long-hand version, which would
be Debug.Print rst!CompanyName.Value, just to be safe.

Try adding Debug.Print rst.RecordCount before the Do loop.

Oh and just FYI, "Do While Not <whatever>" is the same as "Do Until
<whatever>". :)


Rob
 
....well, I tried it again, and it works! I know I haven't done anything
different! ...I don't think, anyway...
thanks,
MaybeNotSoDumb :)


maybe MS Access was just out for coffee...
 
A couple tips for future reference:

Any time you're using VB to access tables, use this format:

Set db = CurrentDB()
sql = "SELECT [tbl].[field] FROM [tblName];"
Set rs = db.OpenRecordset(sql)

Obviously the variable names don't have to be the same as what I used, but
that's pretty much the standard recordset definition format most access
programmers use. (Plus it's a lot less typing when you're opening multiple
recordsets in one piece of code.) You can also use * to select all fields
from the table if you want.

Also, there's no need to add a line break in the middle of your SQL
statement. ;)

And any time you want the information in a field of your recordset, the
easiest way to do that is:

..Fields("field name")

which you would usually set up to be the source for a variable, as in:

CompanyName = .Fields("Comany Name")

which would then allow you to do this for your loop:

With rs
.MoveFirst
Do Until .EOF
CompanyName = .Fields("Company Name")
MsgBox "Current company: " & CompanyName
.MoveNext
Loop
End With

or whatever you choose to do with the code inside the loop.
 
I think -

strSQL = "SELECT DISTINCT tblContacts.CompanyName " & vbCrLf & _
"FROM tblContacts;"

should be -

strSQL = "SELECT DISTINCT tblContacts.CompanyName " & _
"FROM tblContacts;"

or, better yet, do away with strSQL and -

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT CompanyName FROM
tblContacts")
 
Perhaps a matter of style, but I much prefer assigning the sql to a string
before using it to open a recordset.

Why?
Because then I can print it to the immediate window and copy and paste it
into a query for debugging.

I can also format "Format" it in the VBA, so I can better visualize the
query.
StrSQL = "SELECT DISTINCT TblContacts.CompanyName" & _
" FROM tblContacts" & _
" ORDER BY CompanyName"

Also, there is really no problem with embedding vbCrLf in the SQL string.
It can make it more readable if you do decide to use debug.print to send it
to the immediate window.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
That's fine ... we all have our ways ... I set up a recordset so -

Set rst = CurrentDb.OpenRecordset( _
"SELECT DISTINCT CompanyName" & _
" FROM tblContacts" & _
" ORDER BY CompanyName"

also for clarity ... when I want to debug a rs's SQL I suspend execution,
copy the code to the Immediate Window, and "print" it ... for me it's better
that way ... in 9 out of 10 instances rs SQL works from the start ... and I
don't have to go back to finished code removing Debug.Print's ... and I'm not
left with clock-cycle-wasting code.

To each her own.
 
Omitted ")" at end of Set rs ...

Frank said:
That's fine ... we all have our ways ... I set up a recordset so -

Set rst = CurrentDb.OpenRecordset( _
"SELECT DISTINCT CompanyName" & _
" FROM tblContacts" & _
" ORDER BY CompanyName"

also for clarity ... when I want to debug a rs's SQL I suspend execution,
copy the code to the Immediate Window, and "print" it ... for me it's better
that way ... in 9 out of 10 instances rs SQL works from the start ... and I
don't have to go back to finished code removing Debug.Print's ... and I'm not
left with clock-cycle-wasting code.

To each her own.
Click to expand...
 
Oops ... omitted ")" at end of Set rs = ...

John Spencer said:
Perhaps a matter of style, but I much prefer assigning the sql to a string
before using it to open a recordset.

Why?
Because then I can print it to the immediate window and copy and paste it
into a query for debugging.

I can also format "Format" it in the VBA, so I can better visualize the
query.
StrSQL = "SELECT DISTINCT TblContacts.CompanyName" & _
" FROM tblContacts" & _
" ORDER BY CompanyName"

Also, there is really no problem with embedding vbCrLf in the SQL string.
It can make it more readable if you do decide to use debug.print to send it
to the immediate window.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
 
I have a lot of code where I'm basically using code to compare two tables in
such a way that a query can't handle, at least not easily (different logic
between different comparisons on the same record depending on what the answer
of the first comparison is, etc.). So for me, setting the SQL to a string is
a lot less confusing:

Set db = CurrentDB()
sql = "SELECT * FROM [tbl1];"
sql2 = "SELECT * FROM [tbl2];"
sql3 = "SELECT * FROM [tbl3];"
Set rs = db.OpenRecordset(sql)
Set rs2 = db.OpenRecordset(sql2)
Set rs3 = db.OpenRecordset(sql3)

It makes it less confusing when you're debugging, at least for me anyway, lol.
 
That's the key ... whatever works for YOU.

Nicholas Scarpinato said:
I have a lot of code where I'm basically using code to compare two tables in
such a way that a query can't handle, at least not easily (different logic
between different comparisons on the same record depending on what the answer
of the first comparison is, etc.). So for me, setting the SQL to a string is
a lot less confusing:

Set db = CurrentDB()
sql = "SELECT * FROM [tbl1];"
sql2 = "SELECT * FROM [tbl2];"
sql3 = "SELECT * FROM [tbl3];"
Set rs = db.OpenRecordset(sql)
Set rs2 = db.OpenRecordset(sql2)
Set rs3 = db.OpenRecordset(sql3)

It makes it less confusing when you're debugging, at least for me anyway, lol.


Frank said:
That's fine ... we all have our ways ... I set up a recordset so -

Set rst = CurrentDb.OpenRecordset( _
"SELECT DISTINCT CompanyName" & _
" FROM tblContacts" & _
" ORDER BY CompanyName"

also for clarity ... when I want to debug a rs's SQL I suspend execution,
copy the code to the Immediate Window, and "print" it ... for me it's better
that way ... in 9 out of 10 instances rs SQL works from the start ... and I
don't have to go back to finished code removing Debug.Print's ... and I'm not
left with clock-cycle-wasting code.

To each her own.
Click to expand...
Click to expand...
 
Very true. :)

Frank said:
That's the key ... whatever works for YOU.

Nicholas Scarpinato said:
I have a lot of code where I'm basically using code to compare two tables in
such a way that a query can't handle, at least not easily (different logic
between different comparisons on the same record depending on what the answer
of the first comparison is, etc.). So for me, setting the SQL to a string is
a lot less confusing:

Set db = CurrentDB()
sql = "SELECT * FROM [tbl1];"
sql2 = "SELECT * FROM [tbl2];"
sql3 = "SELECT * FROM [tbl3];"
Set rs = db.OpenRecordset(sql)
Set rs2 = db.OpenRecordset(sql2)
Set rs3 = db.OpenRecordset(sql3)

It makes it less confusing when you're debugging, at least for me anyway, lol.


Frank said:
That's fine ... we all have our ways ... I set up a recordset so -

Set rst = CurrentDb.OpenRecordset( _
"SELECT DISTINCT CompanyName" & _
" FROM tblContacts" & _
" ORDER BY CompanyName"

also for clarity ... when I want to debug a rs's SQL I suspend execution,
copy the code to the Immediate Window, and "print" it ... for me it's better
that way ... in 9 out of 10 instances rs SQL works from the start ... and I
don't have to go back to finished code removing Debug.Print's ... and I'm not
left with clock-cycle-wasting code.

To each her own.


:

Perhaps a matter of style, but I much prefer assigning the sql to a string
before using it to open a recordset.

Why?
Because then I can print it to the immediate window and copy and paste it
into a query for debugging.

I can also format "Format" it in the VBA, so I can better visualize the
query.
StrSQL = "SELECT DISTINCT TblContacts.CompanyName" & _
" FROM tblContacts" & _
" ORDER BY CompanyName"

Also, there is really no problem with embedding vbCrLf in the SQL string.
It can make it more readable if you do decide to use debug.print to send it
to the immediate window.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I think -

strSQL = "SELECT DISTINCT tblContacts.CompanyName " & vbCrLf & _
"FROM tblContacts;"

should be -

strSQL = "SELECT DISTINCT tblContacts.CompanyName " & _
"FROM tblContacts;"

or, better yet, do away with strSQL and -

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT CompanyName FROM
tblContacts")


:

I can't figure it out. Once I have rst set as the query, how do I get
the
data out of it?

strSQL = "SELECT DISTINCT tblContacts.CompanyName " & vbCrLf & _
"FROM tblContacts;"
Set rst = CurrentDb.OpenRecordset(strSQL)

...this should give me a list of company names...
so, now I want to loop through them, like this:

do while not rst.EoF
...but now how to get the name
rst!CompanyName doesn't work
deBug.print rst.Fields doesn't work
where is my text?

Pathetically Dumb,
me
Click to expand...
Click to expand...
Click to expand...
 
Back
Top