Set connection to Back end database:
Dim cn As New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & CurrentProject.Path & "\NameofDatabase.accdb;"
Write the Select Statement to get data:
Dim rsName As ADODB.Recordset ' put this at top
Set rsName = New ADODB.Recordset
With rsName
Set .ActiveConnection = cn
.Source = "Select * From TableName"
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.Open
End With
Fill text boxes on Form with data retrieved:
With rsName
If Not (.EOF) Then
.Find strcriteria
Me.txtTransactionID = !transactionid
Me.txtDateAcquired = !DateAcquired
Me.txtAcquisitionPrice = !AcquisitionPrice
Me.txtAskingPrice = !AskingPrice
Me.txtDateSold = !datesold
Me.txtSalesPrice = !salesprice
End If
End With
Example of Form Load where lsTransaction (Listbox) is filled with transaction ID, Date Acquired and artwork ID:
Dim rsTransactions As ADODB.Recordset
Private Sub Form_Load()
Dim cn As New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & CurrentProject.Path & "\VRG+Art+BE.accdb;"
Set rsTransactions = New ADODB.Recordset
With rsTransactions
Set .ActiveConnection = cn
.Source = "Select * from GalleryTransactions"
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.Open
End With
Do While (Not rsTransactions.EOF)
Me.lsTransactions.AddItem rsTransactions![transactionid] & ", " & rsTransactions![DateAcquired] & ", " & rsTransactions![artworkid]
rsTransactions.MoveNext
Loop
rsTransactions.MoveFirst
Set cn = Nothing
End Sub
