Help with compile error

[letsomebodyknow]

My basic code below function is to add a new a new record set for a table called Mood and which has "moodId | mood | color" as its fields.

The Form has 3 txt box and they are named moodID, mood, and color.......

One command button named cmdAdd...........

I have been having problems within the highlighted area

********************************************************


Dim remoteConnection As New ADODB.Connection
Dim rsMood As New ADODB.Connection

Private Sub Form_Load()
Connect
SetRecordset
End Sub

Private Sub Form_Unload(Cancel As Integer)


On Error GoTo ErrorClosing
remoteConection.Close
Exit Sub

ErrorClosing:
'Do nothing

End Sub

Public Sub Disconnect()

On Error GoTo ConnectionError

rsMood.Close
remoteConnection.Close

Exit Sub

ConnectionError:
MsgBox "There was an error closing the databas." & _
Err.Number & "," & Err.Description

End Sub

Private Sub Connect()
On Error GoTo ConnectionError

With remoteConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "H:\database\VBA\Mood.mdb"

End With

Exit Sub

ConnectionError:

MsgBox "There was an error connecting to the databas." & _
Err.Number & "," & Err.Description

End Sub
Public Sub SetRecordset()



Dim sql As String

On Error GoTo DbError


sql = "select * from mood"

rsMood.CursorType = adOpenKeyset
rsMood.LockType = adLockReadOnly

rsMood.Open sql, remoteConnection _
, , adCmdText


If rsMood.EOF = False Then
Me.moodID = rsMood!moodID
Me.Mood = rsMood.Fields.Item("mood")
Me.Color = rsMood.Fields.Item("color")

End If
Exit Sub

DbError:

MsgBox "There was an error retrieving information from the database." & Err.Number & "," & Err.Description


End Sub





Private Sub cmdadd_Click()
Dim sql As String
Dim rsADD As New ADODB.Recordset

On Error GoTo DbError

'Assign updatable curser and lock type properties.
rsADD.CursorType = adOpenDynamic
reAdd.LockType = adLockOptimistic


rsADD.Open "mood", remoteConnection, , , adCmdTable

With rsADD

.AddNew
!Mood = Me.Mood
!Color = Me.Color
.Update
.Close

End With

MsgBox "Record Added. ", vbInformation

rsMood.Close
SetRecordset

Exit Sub

DbError:

MsgBox "There was an error adding the record." & Err.Number & "," & Err.Description


End Sub

*******************************************************
Thanks
Tom

 

[gemma-the-husky]

using dao, not ado. assuming you have an unbound form

sub btnadd_click()

dim rst as recordset

     on error goto fail

     set rst=currentdb.openrecordset("mood") 'opens the table

     rst.addnew 'add a new record
     rst!moodid = moodid 'assign the table field value to the text box 
     rst!mood = mood 'ditto
     rst!color = color 'ditto
     rst.update 'write the record
     rst.close 'close the data set

exithere:
    exit sub

fail:
    'report error here
    resume exithere:

end sub

i imagine the ado code is not dissiimilar. the only error handling you wil lneed is if this fails due to a duplicate key error, or an input error in one of your fields

 

[letsomebodyknow]

I had to delete a part from the old file for it to work???

Thanks a bunch

This is the part I deleted. I do not know why I had to delete it. I will buy a book today. Do you have any suggestions

????????????????????????????????????????????????????????????????
I use this code to get a start on VB Access so that I can build a prototype.
I found many problems with this code and it is from a book called
"Microsoft Access Vba Programming For The Absolute Beginner Second Edition.pdf"


The code
***********************************************************

Public Sub SetRecordset()



Dim sql As String

On Error GoTo DbError


sql = "select * from Mood"

rsMood.CursorType = adOpenKeyset
rsMood.LockType = adLockReadOnly

rsMood.Open sql, remoteConnection, , , adCmdText






If rsMood.EOF = False Then
Me.moodID = rsMood!moodID
Me.Mood = rsMood.Fields.Item("mood")
Me.Color = rsMood.Fields.Item("color")

End If
Exit Sub

DbError:

MsgBox "There was an error retrieving information from the database." & Err.Number & "," & Err.Description


End Sub