When you are dealing with a regular form, then you don't need to use ado, or
dao recordset to modify, or work with the forms data.
So, if you are trying to "remember" the other way, well...for the most part
your code can use the data on the particular form.
The only time you need to create a recordset is for processing code that
needs to operate on more then one record. And, further, *often* you can use
sql in place of that record set.
For example, lets say we need to change all City occurrences of "N.Y" to New
York. We could create a recordset, loop through each record as
dim rst as dao.RecordSet
set rst = currentdb.OpenReocrdSet("tblCustomers")
do while rst.Eof = false
if rst!City = "N.Y" then
rst.Edit
rst!City = "New York"
rst.Update
end if
rst.MoveNext
loop
rst.Close
set rst = nothing
On the other hand, could shorten the above code by using some sql to help
use. A sorter version could be:
dim rst as dao.RecordSet
dim strSql as string
strSql = "select * from tblCustomers where City = 'N.Y.' "
set rst = currentdb.OpenReocrdSet("strSql")
do while rst.Eof = false
rst.Edit
rst!City = "New York"
rst.Update
rst.MoveNext
loop
rst.Close
set rst = nothing
Of course, we could even eliminate he use of a recordset loop, and use
following PREFERRED code
strSql = "update tblCustomers set City = 'New York' where City = 'N.Y.'"
currentdb.Execute strSql,dbFailOnError
So, in those cases were you can use sql to do all the work for you, you
should, and you don't have to 'loop' through each record in many cases.
However, as the 2nd recordset loop shows, you can use a mix of sql to limit
the records, and still use looping code.
Simple forms seem avoid any ADO...is that just coded behind the
scenes by Access?
Well, actually, ms-access is rather smart. If you declare and use ado
reocrdsets, then ms-access will automatically "cast" the recordset from the
form
into ado for you (and, if you use dao...it returns dao so, the underlying
recordset is which ever you want!!). Note that all forms have their own
recordset, and even a recordset clone also. So, we don't actually have to
"open" and create a recordset, since a form has its own.
If we wanted to count the number of records attached to the current form, we
could put behind a button the following.
me.ReocrdSetClone.MoveLast
msgbox "current record count = " & me.RecordSetClone
Question:
is that built in recordset a dao, or ado recordset?
Answer, you often don't know!! If you are not going to use ado, then
remove the ref. However, if you are using both ado, and dao, then you often
need to disambiguate the above.
dim rst as dao.RecordSet
set rst = me.recordSetClone
Now, there is NO confusing here, as you defined the recordset. Note how we
did not have to open, or setup a connection, since the reocrdsetclone is
made for you when the form opens. Also note that you can use me.Recordset
(this feature is only available in a2000 and later).
me.RecordSetClone.MoveNext
me.ReocrdSet.MoveNext
In the 2nd movenext, the form will actually "jump", and "move" along as you
move the record pointer. However, with the ReocrdSetClone, the form does NOT
follow the recordsetclone pointer. And, don't confuse the above with
me.ReocrdSet . Clone (I put a space around the "." so you can see the
difference.
me.ReocrdSetClone (this is built into forms)
me.ReocrdSet (this is built into form, but only a2000 and
later)
me.ReocrdSet.Clone this is a methoed of the recordset to "clone"
set rst = me.RecordSet.Clone (if you move the rst pointer..the
form will jump).
So, for general processing routines, I tend to use the reocrdsetclone..as
you can do things with it...but not make the form flicker and jump around.
On the other hand, you might want the form to jump
me.RecordSet.FindFirst "City = 'Edmonton' "
The above would find the first city = Edmonton...and the form would jump
me.RecordSetClone.FindFirst "City = 'Edmonton'"
if me.ReocrdSetClone.NoMatch = false then
me.BookMark = me.ReocrdSetClone.BookMark
end if
Hum, never tried, but I suppose the above book mark code could be
me.RecordSet.BookMark = me.ReocrdSetClone.BookMark
I hope the above jogs the memory....
