Adding multiple records

C

ctva

I have a table where I need to add X new records at one point with one field
having a constant text value and another numeric field starting at value Y
with each new record incrementing this value by one and finishing at value Z.
X, Y and Z are input on a form.

I am new to VBA and would appreciate some help.
 
R

Robert Morley

Hi Chris,

First question: if you know Y and Z, then why do you need to know X?

Second question: Before anybody can give you any code, we'll need to know
if you're using DAO (default for MDB's) or ADO (default for ADP's, but also
usable for MDB's).



Rob
 
C

ctva

I'm using DAO (assuming that is the default for Access 2003). I have the
option to either enter a start number and number of records required in the
sequence or the start and end numbers. Whichever is the easiest.

Manythanks for taking the time.
 
R

Robert Morley

Okay, so try something like the following code (keeping in mind that my DAO
is a little rusty):

Public Sub SomeButton_OnClick()
Const cstrMyConstant = "My Constant Text"

Dim rs As DAO.Recordset
Dim i As Long

If IsNull(Me!MyZField.Value) Then Me!MyZField.Value = Me!MyYField.Value
+ Me!MyXField.Value
Set rs = CurrentDB().OpenRecordset("MyTable", dbOpenTable)
With rs
For i = Me!MyYField.Value To Me!MyZField.Value
.AddNew
!MyNumericField.Value = i
!MyTextField = cstrMyConstant
.Update
Next
.Close
End With
Set rs = Nothing
End Sub


Like I said, my DAO is a little rusty, but I think the above is all correct.
Let me know if you run into any problems. The first line of actual code
fills in the Z field based on X & Y if it's not already filled in...this may
or may not be how you want to go about it, but it's one method of doing
things. The other method is to copy all of your X, Y, and Z to local
variables and do it there.



Rob
 
D

Douglas J. Steele

Note that Set rs = CurrentDB().OpenRecordset("MyTable", dbOpenTable) won't
work in a split database.
 
R

Robert Morley

Good point, Doug. I assumed a monolithic design. If you're working with
linked tables, I believe you can use the following (but like I say, my DAO's
rusty, so I may be wrong):

Set rs = CurrentDB().OpenRecordset("SELECT * FROM MyTable",
dbOpenRecordset)

And in the highly unlikely event that you're using a completely unlinked and
separate database, you probably already know enough to write your own code,
but it would be:

Set rs = <YourDBRef>.OpenRecordset("MyTable", dbOpenTable)

....or similar coding.



Rob
 
D

Douglas J. Steele

I rarely bother with the second argument, trusting Access to come up with
the correct default.
 
C

ctva

Thanks for all you're replies. I've been unable to try them as yet due to
being dumped with other things to do, but will give it a go very soon.
 
Top