I need to add some reords to my DB application and I think I will need to di
using VBA but I'm unsure how.

I have a data table that wil record about six fields worth of information
along with a serial number. The users will have to make entries in the data
table to record a block of serial numbers against specific data that's held
in the form.

So, if there is a batch of ten componets, the user will enter all the
details in the form, (And all the details will be identical for each
component) and request that a block of ten serial numbers (The next ten in
the table) be allocated.

I have set up an unbound form that asks for the relevent data, and also has
a box for the number of items in the batch, but I'm struggling to work out
how to add entries to my data table using VBA.

I relaise that Ineed to do a For..Next loop to get the required number of
entries to the table etc and I know how to check what the last numbe ris,
it's all the table entry stuff I'm stuck on.

Are there any good and easy to follow web (or paper) based resources that
explain in easy terms how I can go about this.

Thanks in advance.

Neil

The two most common approaches are to open a recordset and use the AddNew
method for each new row, or to use an INSERT INTO SQL statement:

Dim rsCurr As DAO.Recordset

Set rsCurr = CurrentDb().OpenRecordset("SELECT * FROM MyTable")
With rsCurr
.AddNew
!Field1 = MyValue
!Field2 = MyOtherValue
.Update
End With

or

Dim strSQL As String

strSQL = "INSERT INTO MyTable(Field1, Field2) " & _
"VALUES(" & MyValue & ", " & Chr$(34) & MyOtherValue & _
Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError

Note that I'm assuming that Field2 is a text field, hence the need for
quotes around the value (Chr$(34) is the equivalent of ")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Neil" <(E-Mail Removed)> wrote in message
newsBBBC9BC-C63D-464B-96B6-(E-Mail Removed)...
>I need to add some reords to my DB application and I think I will need to
>di
> using VBA but I'm unsure how.
>
> I have a data table that wil record about six fields worth of information
> along with a serial number. The users will have to make entries in the
> data
> table to record a block of serial numbers against specific data that's
> held
> in the form.
>
> So, if there is a batch of ten componets, the user will enter all the
> details in the form, (And all the details will be identical for each
> component) and request that a block of ten serial numbers (The next ten in
> the table) be allocated.
>
> I have set up an unbound form that asks for the relevent data, and also
> has
> a box for the number of items in the batch, but I'm struggling to work out
> how to add entries to my data table using VBA.
>
> I relaise that Ineed to do a For..Next loop to get the required number of
> entries to the table etc and I know how to check what the last numbe ris,
> it's all the table entry stuff I'm stuck on.
>
> Are there any good and easy to follow web (or paper) based resources that
> explain in easy terms how I can go about this.
>
> Thanks in advance.
>
> Neil

Thanks for your help Doug, the first method worked fine (Once I realised my
error message was being caused by a missing reference).

That's the easy bit done now, all I need to do is make it user proof!

Thanks again


Neil


"Douglas J. Steele" wrote:

> The two most common approaches are to open a recordset and use the AddNew
> method for each new row, or to use an INSERT INTO SQL statement:
>
> Dim rsCurr As DAO.Recordset
>
> Set rsCurr = CurrentDb().OpenRecordset("SELECT * FROM MyTable")
> With rsCurr
> .AddNew
> !Field1 = MyValue
> !Field2 = MyOtherValue
> .Update
> End With
>
> or
>
> Dim strSQL As String
>
> strSQL = "INSERT INTO MyTable(Field1, Field2) " & _
> "VALUES(" & MyValue & ", " & Chr$(34) & MyOtherValue & _
> Chr$(34) & ")"
> CurrentDb.Execute strSQL, dbFailOnError
>
> Note that I'm assuming that Field2 is a text field, hence the need for
> quotes around the value (Chr$(34) is the equivalent of ")
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Neil" <(E-Mail Removed)> wrote in message
> newsBBBC9BC-C63D-464B-96B6-(E-Mail Removed)...
> >I need to add some reords to my DB application and I think I will need to
> >di
> > using VBA but I'm unsure how.
> >
> > I have a data table that wil record about six fields worth of information
> > along with a serial number. The users will have to make entries in the
> > data
> > table to record a block of serial numbers against specific data that's
> > held
> > in the form.
> >
> > So, if there is a batch of ten componets, the user will enter all the
> > details in the form, (And all the details will be identical for each
> > component) and request that a block of ten serial numbers (The next ten in
> > the table) be allocated.
> >
> > I have set up an unbound form that asks for the relevent data, and also
> > has
> > a box for the number of items in the batch, but I'm struggling to work out
> > how to add entries to my data table using VBA.
> >
> > I relaise that Ineed to do a For..Next loop to get the required number of
> > entries to the table etc and I know how to check what the last numbe ris,
> > it's all the table entry stuff I'm stuck on.
> >
> > Are there any good and easy to follow web (or paper) based resources that
> > explain in easy terms how I can go about this.
> >
> > Thanks in advance.
> >
> > Neil
>
>
>