Figure 19.4

This userform displays particular records from the Access recordset. When the buyer selects certain records and then chooses the Confirm button, you'll have to use ADO's Update method to update the Sent field on the selected records.

The code attached to the Confirm button follows. The key to making this work is being able to build a SQL statement that uniquely identifies one record. Including the ID field in the fields returned in the prior example is important if you want to narrow the information down to a single record:

Private Sub cbConfirm_Click() Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset

1 If nothing is selected, warn them CountSelect = 0

For x = 0 To Me.lbXlt.ListCount - 1 If Me.lbXlt.Selected(x) Then

CountSelect = CountSelect + 1 End If Next x

If CountSelect = 0 Then

MsgBox "There were no transfers selected. " & _

"To exit without confirming any tranfers, use Cancel." Exit Sub End If

' Establish a connection transfers.mdb 1 Path to Transfers.mdb is on Menu MyConn = "J:\transfers.mdb"

Set cnn = New ADODB.Connection

With cnn

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

' Mark as complete For x = 0 To Me.lbXlt.ListCount - 1 If Me.lbXlt.Selected(x) Then

ThisID = Cells(2 + x, 1).Value ' Mark ThisID as complete 'Build SQL String sSQL = "SELECT * FROM tblTransfer Where ID=" & ThisID Set rst = New ADODB.Recordset With rst

.Open Source:=sSQL, ActiveConnection:=cnn, _

CursorType:=adOpenKeyset, LockType:=adLockOptimistic 1 Update the field .Fields("Sent").Value = True .Update .Close End With End If Next x

' Close the connection cnn.Close Set rst = Nothing Set cnn = Nothing

' Close the userform Unload Me

End Sub

0 0

Post a comment