DAO rst Methods in OnCurrent Event - Unwise?

[Tim Marshall]

In A2003, I have a bound continuous form (OK, actually a sub form of an
unbound main form, but I don't think that matters here). The record
source consists of an Jet SQL statement that returns one record per the
entity that I want to display on the form. There is another table, not
included with the form's SQL source that has a one to many relationship
with the table in the form's SQL statement that gives additional
information about one or more (or no) records each one of the records in
the form may be linked to.

What I'd like to have is a text box or two in the footer of the form
that shows this additional information. My intent would be to use the
on current event of the form to populate the text box, something like
the air code that follows.

But wouldn't this be a large drain on resources? Or is it an acceptable
technique?

dim StrSql as string
dim rst as dao.recordset
dim dbs as dao.database
dim strData as string

strSql = "Select Stuff from OtherTable where Pointer = " &
me.txtMainFormField

set dbs = access.currentdb
set rst = rst.openrecordset(strsql, dbopensnapshot)

strData = ""

with rst

if .eof then
goto exit_proc
else
.movefirst

do while .eof = false

if strData <> "" then strData = strData & vbcrlf

strData = strData & .fields!Stuff

.movenext

loop

.close

end if

Exit_Proc:

'close stuff and end
--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Steve Jorgensen]

The resource drain should not be too bad. You could make it a little faster
by keeping the dbs variable at the form module level, and also using a
parameterized query in a temporary querydef, also held at the form level.

On Sun, 14 Nov 2004 10:56:52 -0330, Tim Marshall <TI****@antarctic.flowerpots>
wrote:

In A2003, I have a bound continuous form (OK, actually a sub form of an
unbound main form, but I don't think that matters here). The record
source consists of an Jet SQL statement that returns one record per the
entity that I want to display on the form. There is another table, not
included with the form's SQL source that has a one to many relationship
with the table in the form's SQL statement that gives additional
information about one or more (or no) records each one of the records in
the form may be linked to.

What I'd like to have is a text box or two in the footer of the form
that shows this additional information. My intent would be to use the
on current event of the form to populate the text box, something like
the air code that follows.

But wouldn't this be a large drain on resources? Or is it an acceptable
technique?

dim StrSql as string
dim rst as dao.recordset
dim dbs as dao.database
dim strData as string

strSql = "Select Stuff from OtherTable where Pointer = " &
me.txtMainFormField

set dbs = access.currentdb
set rst = rst.openrecordset(strsql, dbopensnapshot)

strData = ""

with rst

if .eof then
goto exit_proc
else
.movefirst

do while .eof = false

if strData <> "" then strData = strData & vbcrlf

strData = strData & .fields!Stuff

.movenext

loop

.close

end if

Exit_Proc:

'close stuff and end

[Tim Marshall]

Steve Jorgensen wrote:

The resource drain should not be too bad. You could make it a little faster
by keeping the dbs variable at the form module level, and also using a
parameterized query in a temporary querydef, also held at the form level.

Thank you Steve. I regularly wonder about these things - due to the
huge amount of reporting and manipulation of data I do, I demanded a PC
with 2 gigs of ram. I have to remember the most anyone in our
department has beside me is 512 and most, less than that.

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Trevor Best]

Tim Marshall wrote:

As well as Steve's suggestions...

set rst = rst.openrecordset(strsql, dbopensnapshot)
Apart from the obvious typo :-) Consider:
set rst = dbs.openrecordset(strsql, dbopenDynaset,dbReadOnly)
if strData <> "" then strData = strData & vbcrlf

Consider:
If Len(strData) Then

But then I see what you're doing in that loop, you may also consider
just adding the VbCrLf in any case and stripping it off at the end, e.g.

Do...
strData = strData & .fields!Stuff & vbCrLf
...
Loop
strData = Left$(strData,Len(strData)-2)

I realise the second lot is all string stuff and millisecond stuff, but
then I don't know how long your loop will last.

--
This sig left intentionally blank