Adding a Record to the Database

The application has a userform where buyers can enter transfers. To make the calls to the Access database as simple as possible, a series of utility modules handle the ADO connection to the database. This way, the userform code can simply call AddTransfer(Style, FromStore, ToStore, Qty).

The technique for adding records, after the connection is defined, is as follows:

1. Open a recordset that points to the table.

2. Use AddNew to add a new record.

3. Update each field in the new record.

4. Use Update to update the recordset.

5. Close the recordset and the connection.

The following code adds a new record to the tblTransfer table.

Sub AddTransfer(Style As Variant, FromStore As Variant, _ ToStore As Variant, Qty As Integer) Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset

MyConn = "J:\transfers.mdb"

1 open the connection

Set cnn = New ADODB.Connection

With cnn

.Provider = "Microsoft.Jet.OLEDB.4.0" .Open MyConn End With

1 Define the Recordset

Set rst = New ADODB.Recordset rst.CursorLocation = adUseServer

' open the table rst.Open Source:="tblTransfer", _ ActiveConnection:=cnn, _ CursorType:=adOpenDynamic, _ LockType:=adLockOptimistic, _ Options:=adCmdTable

' Add a record rst.AddNew

' Set up the values for the fields. The first four fields

' are passed from the calling userform. The date field

' is filled with the current date.

rst("Style") = Style rst("FromStore") = FromStore rst("ToStore") = ToStore rst("Qty") = Qty rst("tDate") = Date rst("Sent") = False rst("Receive") = False

' Write the values to this record rst.Update

1 Close rst.Close cnn.Close

End Sub

0 0

Post a comment