In the code below I use the Form_Load to connect to a Access database and load a form and combo box. I then use the cboComp_Click to look for a record in the tblToDo table, If found I load it into the form using the FillFields If not found I add a new record. I'm really missing something because when I call an existing record and try to add data, the new data is never added. If I try to use the combo box a second time I get a "the connection can not be used to perform this operation" Run-Time Error 3709. I'm new to VB, can anybody see my errors?
Thanks.
Dim adoconn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub Form_Load()
Dim str As String
Dim sqlName As String
Me.Caption = "Company Database"
Set adoconn = Nothing
'Next three lines opens the Lavin.mdb database.
adoconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Lavin.mdb;Persist Security Info=False"
str = "select * from tblToDo"
rs.Open str, adoconn, adOpenDynamic, adLockOptimistic
'Moves to first record of database
rs.MoveFirst
'Loads whatever is in the first record into the files on the form.
txtToDoCompany.Text = rs(0)
If (rs1) <> "" Then
txtToDo1.Text = rs(1)
End If
rs.Close
'The following code loads the combo box with the company names from the Companies table of'database Corp.mdb
cboCompy.Clear
sqlName = "SELECT Company FROM Companies"
rs.Open sqlName, adoconn, adOpenDynamic, adLockOptimistic
Set cboCompy.DataSource = rs
Do Until rs.EOF
cboCompy.AddItem rs(0)
rs.MoveNext
Loop
rs.Close
End Sub
Private Sub cboCompy_Click()
Dim name As String, str As String
Set rs = Nothing
If Me.cboCompy <> "Combo1" Then
name = Me.cboCompy
str = "select * from tblToDo where NameofComp = '" & name & "'"
Set rs = New Recordset ' Just Added
rs.Open str, adoconn, adOpenForwardOnly, adLockOptimistic
If rs.EOF Then
rs.AddNew
rs(0) = name
txtToDoCompany = name
rs.Update
FillFields
Else
FillFields
End If
End If
rs.Close
Set rs = Nothing
adoconn.Close
Set adconn = Nothing
End Sub
Private Sub FillFields()
txtToDoCompany.Text = rs(0)
If IsNull(rs(1)) Then
txtToDo1.Text = ""
Else: txtToDo1.Text = rs(1)
End If
If IsNull(rs(2)) Then
txtToDo1.Text = ""
Else: txtToDo1.Text = rs(2)
End If
If IsNull(rs(3)) Then
txtToDo1.Text = ""
Else: txtToDo1.Text = rs(3)
End If
If IsNull(rs(4)) Then
txtToDo1.Text = ""
Else: txtToDo1.Text = rs(4)
End If
If IsNull(rs(5)) Then
txtToDo1.Text = ""
Else: txtToDo1.Text = rs(5)
End If
If IsNull(rs(6)) Then
txtToDo1.Text = ""
Else: txtToDo1.Text = rs(6)
End If
