Add records with VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 ")
 
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
 
Back
Top