Help with Access VB and Recordsets

By Tink! ·
I'm hoping Peconet sees this as I know he's apt to have what I need.

Looking for a link to a good resource for learning how to work with Recordsets in Access VB.

I'm experienced in VB in Access but up until now have avoided using Recordsets to control any of my desired actions. Now, I have certain actions that I know are going to HAVE to use Recordsets in order to work properly and program easier.

Specifically I am having a user enter data that needs to be repeated in several new records based on the quantity they enter. So it's going to be an "If Variable is less than or equal to # entered Then Create New Record" sort of thing.

Thanks in advance!
Tink :)

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Record sets are returned from run or open commands

by Slayer_ In reply to Help with Access VB and R ...

I think Update also returns a record set.

And working with them is really easy. As I recall, I think its just
rs.Fields("Column Name").Value

The usual loop through everything looks like this.

Call rs.MoveFirst
Do Until rs.EOF = True
'Code here
Call rs.MoveNext

Collapse -

Shouldn't require recordsets that.

by Tony Hopkinson In reply to Help with Access VB and R ...

Get them to enter the range.
Create a parameterised query to do the insert and then loop through changing the parameter(s)
Not ideal interms of optimisation for access, but unless you really need the performance boost, your code will be far more future proof.

Collapse -


by Tink! In reply to Shouldn't require records ...

from what I understand based on the brief readings I've been able to fit in at this time: to create a New Record (or actually Add a Record to an existing Recordset) I kind of need to be working within the parameter of the Recordset.

At least to make it work without any weird hitches. I could be wrong. A good point to ponder.

Collapse -

I usually cheat and use the Open command

by Slayer_ In reply to Hm

Create an SQL string with an insert, and use Open.

Collapse -

To add to a recordset yes

by Tony Hopkinson In reply to Hm

to add to table no.
If you aren't using a grid type display which you need dynamically updating it's alot of overhead fro little gain.

Unless this is

Collapse -

How about working in Forms?

by Tink! In reply to To add to a recordset yes

for DBs where the user is inputing data I usually use Forms because it is easier (for me) to create Events based on what they enter or their actions.

This is what I'm trying to do:

In the data entry form the user enters data and then a # for quantity. This number - say 4, is how many records must be created using the data they entered.

- create a New Record
- Fill in/copy the data
- check the number and see if it has reached "4" yet
- If not, create another new Record

etc. etc.

This is the first time I've had to program Access to Create and Fill-in Records based on entered data. Perhaps this is why I'm confused as to why or why not I'm to use Recordsets.

Collapse -

Much of a muchness

by Tony Hopkinson In reply to How about working in Form ...

Given your spec you have a choice, maybe give recordsets a go as a learning experience.

To be honest using a recordset would be the optimum solution in access because it's a desktop database. Just bear in mind if you ever use a full client server dbms , it's about the worst thing you could do.

If you had the table you are filling at the bottom of you input form and wanted to show it "happening" as it happened, then recordset would be the way to go, given you aren't showing it, it can all be hung off a command button or some such on your input form.

Collapse -

Thanks Tony

by Tink! In reply to Much of a muchness

I ended up NOT using Recordsets at all. Simply used a For Next statement based on the number entered by the user.

To create the New Record all I had to do was:

DoCmd.GoToRecord acDataForm, "formname", acNewRec

Thanks for all your input!

Related Discussions

Related Forums