Option Explicit
Private cn As ADODB.Connection 'this is the connection
Private rs As ADODB.Recordset 'this is the recordset
Private Sub Form_Load()
Me.MousePointer = 11 'this makes the mouse pointer the hourglass
Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
App.Path & "\db1.mdb" 'this is the connection string explained in the notes section.
'The next line is where I get the error
cn.Open
Set rs = New ADODB.Recordset 'as we did with the connection
rs.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening the recordset explained in the notes
rs.MoveFirst 'moves to the first record
Do Until rs.EOF = True 'this is the Loop to add items to the combo box
combo1.AddItem rs.Fields("field1") 'this adds items from field1 into the combo box
rs.MoveNext 'moves next record
Loop
rs.MoveFirst
fillfields 'i'll explain this later on.
Me.MousePointer = 0 'sets the mouse pointer to the normal arrow
End Sub
Public Sub fillfields()
If Not (rs.BOF = True Or rs.EOF = True) Then 'Checks if we are at the first or last record. This is use a lot.
Text1.Text = rs.Fields("Field2") 'text1 = field2 and display that data
Text2.Text = rs.Fields("Field3") 'as above
combo1.Text = rs.Fields("Field1") 'as above
Else
MsgBox "Either you are at the first record or the last record.", vbExclamation, "Cannot Move"
End If
End Sub
Private Sub cmdPrev_Click()
If Not (rs.BOF = True) Then
rs.MovePrevious 'move previous record
fillfields 'fill the controls
End If
End Sub
Private Sub cmdNext_Click()
If Not (rs.EOF = True) Then
rs.MoveNext 'move to next record
fillfields 'fill the controls
End If
End Sub
Private Sub cmdAdd_Click()
With rs
.AddNew 'adding new record
.Fields("field2") = Text1.Text 'setting field2 = whatever is typed in text1
.Fields("field3") = Text2.Text 'as above
.Fields("field1") = combo1.Text 'as above
.Update 'this updates the recordset etc.
End With
End Sub
Private Sub cmdDelete_Click()
If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then 'check if you really want to delete this record
Exit Sub 'exit the command
Else
If Not (rs.BOF = True Or rs.EOF = True) Then
rs.Delete 'delete the current record
If Not (rs.BOF = True Or rs.EOF = True) Then
rs.MoveNext 'move next
If rs.EOF Then rs.MoveLast
fillfields
End If
End If
End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
rs.Close 'close the recordset
cn.Close 'close the connection
Set rs = Nothing 'set them to nothing
Set cn = Nothing 'as above
End Sub