Error Adding record to database

[Matrix03]

I am trying to add this record to the database and get this error. Object Variable or With Block Variable not set.

Private Sub cmdAdd_Click()
On Error GoTo ErrLabel
adoRecordset.AddNew

txtCustomerLastName.Text = ""
txtCustomerFirstName.Text = ""

Exit Sub

ErrLabel:
MsgBox Err.Description, vbInformation
End Sub

 

[HebieBug]

Looks like you are initialising the recordset outside this procedure
If this is the case then you will need to use the .update statement
AdoRecordset.update
If it dose not work do you want to put all the code up on the thread for a better look.. Very sure that this will fix the error

 

[Matrix03]

Dim adoRecordset As Recordset
Private Sub cmdAdd_Click()
On Error GoTo ErrLabel
adoRecordset.Update

txtCustomerLastName.Text = ""
txtCustomerFirstName.Text = ""

Exit Sub

ErrLabel:
MsgBox Err.Description, vbInformation
End Sub





Private Sub Form_Load()

Dim Treespraying As ADODB.Connection 'Sets Treespraying Variable
Dim adoRecordset As ADODB.Recordset 'Sets the Recordset Variable
Set Treespraying = New ADODB.Connection

'Open the connection to database
Treespraying.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\TreeSpraying.mdb"

Set adoRecordset = New ADODB.Recordset
adoRecordset.Open "Select CustomerLastName, CustomerFirstName from Customer", Treespraying, adOpenStatic, adLockOptimistic


Width = Screen.Width * 0.9 ' Set width of form.
Height = Screen.Height * 0.9 ' Set height of form.
Left = (Screen.Width - Width) / 2 ' Center form horizontally.
Top = (Screen.Height - Height) / 2 ' Center form vertically.



End Sub

 

[MatsHulten]

You are trying to use a variable that is declared outside of the current scope.

In a sub, the only variables you can use is the one that is passed into the sub as parameters, the one you declare and assign in the sub, and global variables.

To correct this declare the recordset outside any sub or function, using the Dim or Private keyword, thereby making it a module-level variable.

Good Luck
-Mats Hulten

 

[Matrix03]

Here is the full code. Can you explain what needs to be fixed in real plain english. Thanks Very much!! I am sorry I am really a beginner and everything I have right now is becuase of books and really helpful people like you people on this board.

____________________________________________________________
Dim adoRecordset As Recordset
____________________________________________________________

Private Sub cmdAdd_Click()
On Error GoTo ErrLabel
adoRecordset.AddNew

txtCustomerLastName.Text = ""
txtCustomerFirstName.Text = ""

Exit Sub

ErrLabel:
MsgBox Err.Description, vbInformation
End Sub

____________________________________________________________

Private Sub Form_Load()

Dim Treespraying As ADODB.Connection 'Sets Treespraying Variable
Dim adoRecordset As ADODB.Recordset 'Sets the Recordset Variable
Set Treespraying = New ADODB.Connection

'Open the connection to database
Treespraying.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\TreeSpraying.mdb"

Set adoRecordset = New ADODB.Recordset
adoRecordset.Open "Select CustomerLastName, CustomerFirstName from Customer", Treespraying, adOpenStatic, adLockOptimistic


Width = Screen.Width * 0.9 ' Set width of form.
Height = Screen.Height * 0.9 ' Set height of form.
Left = (Screen.Width - Width) / 2 ' Center form horizontally.
Top = (Screen.Height - Height) / 2 ' Center form vertically.



End Sub

 

[dpaulson]

Try this

Private Sub cmdAdd_Click()
On Error GoTo ErrLabel
adoRecordset.AddNew
adoRecordset!CustomerLastName = txtCustomerLastName
adoRecordset!CustomerFirstName = txtCustomerFirstName
adoRecordset.Update
txtCustomerLastName.Text = ""
txtCustomerFirstName.Text = ""

Exit Sub



ErrLabel:
MsgBox Err.Description, vbInformation
End Sub



David Paulson

 

[SauerC]

also the dim lines for adoRecordset and Treespraying need to be moved to the option explicit area.

 

[Matrix03]

Thanks all who contributed, this worked.

 

[rossifumi]

Dim cn As adodb.Connection
Dim rs As adodb.Recordset
Set cn = New adodb.Connection
cn.ConnectionString = your connection string
cn.Open
Set rs = New adodb.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Source = "your table"
rs.ActiveConnection = cn
rs.Open
rs.addnew
rs.Fields("customerlastname&quot =
rs.Fields("customerfirstname&quot =
rs.update

when done:
rs.close
cn.close