Basic concepts about recordsets

  • Thread starter Thread starter Nicola M
  • Start date Start date
N

Nicola M

Hi all! Access 2003

I'm pretty newbie on Access VBA. I use just some well known functions
(format, Ucase, LCase, Trim etc) and some little user defined function
written by myself.
Now I'd like to manage data returned by queries or by entire tables. I read
something about on Help, forums and tutorials and I'm here to ask some
explanation. Please note I'm already able to show set of data using forms
and/or reports.
If I need to select several records (even not in succession) to do some
"operation" on them I understand I should use the recorset that is a perfect
replica of the set of data I have in front of me...
Is it correct? how can I manage recordsets? I'd like to understand this
because I think it is the base to work on data programatically...
Other needs I have could be:
- starting from a data set in a form update all of them (or only someone)
with an update query built at the moment;
- starting from a data set in a form insert some data (usually the ID of
some records) in another table; also i need to rollback the operation;
....
Now I do this things using queries but I'd like to provide "buttons" or
"checkboxes" to my users because they are not familiar with SQL.

Sorry for my English and for the long message.

Thank you in advance for any suggestion or advice.
Nicola M
 
http://www.microsoft.com/communitie...&p=1&tid=eea09742-3c2e-478b-ae3c-a209ad22c9a0

This is a thread started a couple days ago that has some basic examples on
how to use the DAO recordset object.

Keep in mind though (this is also noted in the thread), that you are almost
always better off using queries.

You can always set up a button to run a query off a form:

CurrentDB.Execute ......

If you should decide to use the Recordset, the examples in the above link
can be fairly easily modified to work with the RecordsetClone object of a
form (this is helpful when you want to work with data that is already
filtered by the form... example: if you have a form based on a query, with
only certain records selected for that form's recordset, the RecordsetClone
will pull only those records, where the plain old Recordset object will need
to be queried to return the same records).

Anyway, try and see if you can accomplish this with queries first.
Recordsets are usually a last resort.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain
 
Thank you dymondjack.

I'll see your link.
As I said I'm able enough in managing queries. I have several problems when
I try to get a kind of "automation". Wizard is non so flexible and often I
need to pass parameters at these queries (I'd like also to pass them with a
little form using checkboxes or comboboxes) but this isn't yet my battle
field.

In your opinio is better I try also asking in queries newsgroup or
formscoding?

Again thank you.
Nicola M
 
In your opinio is better I try also asking in queries newsgroup or
formscoding?
Click to expand...

I would definately advise using queries where possible, rather than the
Recordset object. As far as what newsgroup to post in, I don't moniter the
Queries or formscoding groups nearly as much as this one, but we see a lot
of both types of questions here. So, if you were to post questions about
queries here, I doubt you would be crucified for posting in the wrong group.

As far as using checkboxes (or any other controls) to define query
statements, you will end up using vba code behind certain events on the form
to build your query strings. Basically, you insert criteria values into your
query string by reading the value of the controls on the form, and after the
query string is built, execute it. Regarding details on how to interact with
controls on the form and insert their values into a query string, this would
probably not be applicable to the queries newsgroup. But, you could easily
find answer for those questions either here or in formscoding.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain
 
dymondjack said:
I would definately advise using queries where possible, rather than the
Recordset object. As far as what newsgroup to post in, I don't moniter the
Queries or formscoding groups nearly as much as this one, but we see a lot
of both types of questions here. So, if you were to post questions about
queries here, I doubt you would be crucified for posting in the wrong group.
Click to expand...

Definitely thanks! :-)
As far as using checkboxes (or any other controls) to define query
statements, you will end up using vba code behind certain events on the form
to build your query strings. Basically, you insert criteria values into your
query string by reading the value of the controls on the form, and after the
query string is built, execute it. Regarding details on how to interact with
controls on the form and insert their values into a query string, this would
probably not be applicable to the queries newsgroup. But, you could easily
find answer for those questions either here or in formscoding.
Click to expand...

Yes, sir! Again, thanks.
Nicola M

[CUT]
 
Recordsets are for manipulating data programmatically. They don't have an
interface (like a form or datasheet.) So, if the user needs to select stuff
and enter values, you need a form, but if you want the computer to make the
changes (or count, or automatically add or delete), you can use a recordset.

The basic idea is to create a SQL statement that gives you the fields and
records you need. You can then operate on them. Here's a basic example of
opening a recordset and programmatically looping through the records:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

When you open a form, Access does create a recordset behind the scenes that
it uses to serve up the records. You can get at those records via the
RecordsetClone of the form. In a typically example, you can search the clone
set to see if a particular record is there, display it if it is (by setting
the form's Bookmark) or give a message if it's not there. Here's an example
of doing that:
http://allenbrowne.com/ser-03.html

You can perform an 'opereration' on the records by looping through them and
using Edit (or AddNew) and Update, or using Delete. There are cases where
that's a good approach, but if it is a simple operation, you will probably
find it easier to execute an action query instead. Here's some info on how
to do that:
http://allenbrowne.com/ser-60.html

Once you have the basics of recordsets figured out, here's a list of the 10
most common mistakes I see people making when working with recordsets:
http://allenbrowne.com/ser-29.html
 
Back
Top