adding new record??

  • Thread starter Thread starter Scott Viney
  • Start date Start date
S

Scott Viney

Afternoon all,

Im just getting into this VBA for access stuff and am a bit confused. If
the database is already open how do you add data to a table in the new
record position? I have read about AddNew...but this doesnt make it any
clearer.

If this was AutoCAD and I wanted to add a line....there is an object
ThisDrawing....so I can use Thisdawing.AddLine method....

Can someone show me some sample code please??

Scott
 
I'm guessing that .AddLine adds an object to a collection of objects. A
table (or recordset) simply isn't the same kind of collection device (and
the object model doesn't view a record as being an object in that sense).
AddNew positions a recordset/table on a new record, which can then be filled
in.

From the ADO Help File entry for AddNew (using AddNew in DAO would be very
similar):

Public Sub AddNewX()

Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strID As String
Dim strFirstName As String
Dim strLastName As String
Dim booRecordAdded As Boolean

' Open a connection.
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=;"
cnn1.Open strCnn

' Open Employee table.
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable

' Get data from the user. The employee ID must be formatted as
' first, middle, and last initial, five numbers, then M or F to
' signify the gender. For example, the employee id for Bill Sornsin
' would be "B-S55555M".
strID = Trim(InputBox("Enter employee ID:"))
strFirstName = Trim(InputBox("Enter first name:"))
strLastName = Trim(InputBox("Enter last name:"))

' Proceed only if the user actually entered something
' for both the first and last names.
If (strID <> "") And (strFirstName <> "") _
And (strLastName <> "") Then

rstEmployees.AddNew
rstEmployees!emp_id = strID
rstEmployees!fname = strFirstName
rstEmployees!lname = strLastName
rstEmployees.Update
booRecordAdded = True

' Show the newly added data.
MsgBox "New record: " & rstEmployees!emp_id & " " & _
rstEmployees!fname & " " & rstEmployees!lname
Else
MsgBox "Please enter an employee ID, " & _
"first name, and last name."
End If

' Delete the new record because this is a demonstration.
cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strID & "'"

rstEmployees.Close
cnn1.Close

End Sub


HTH,
 
Back
Top