runtime error 91-- object variable or with block variable not set. 2

[miscluce]

I dont understand how I can get this error?
object variable or with block variable not set.
I get this error when I am trying to add a new record.

can someone tell me what to check for in my code?

Sub SaveCurrentRecord()

Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim strSQL As String

If Not rsNames.BOF And Not rsNames.EOF Then

'create a new connection instance and open it using the connection string
Set cnDb = New ADODB.Connection
cnDb.Open strConnection

Dim intCurName As Integer
intCurName = 0

'if adding a new record
If blnAddMode = True Then

'create SQL to insert a new record into the database
'containing the values on the form
strSQL = "INSERT INTO tblName(" & _
"fldLastName, fldFirstName, fldTitle) " & _
"VALUES (" & _
"'" & Me.txtLastName & "', " & _
"'" & Me.txtFirstName & "', " & _
"'" & Me.txtTitle & "') "

Else
'create SQL to update the existing record in the
'database with the values on the form
strSQL = "UPDATE tblName SET " & _
"fldLastName = '" & Me.txtLastName & "', " & _
"fldFirstName = '" & Me.txtFirstName & "', " & _
"fldTitle = '" & Me.txtTitle & "', " & _
"WHERE fldNameId = " & rsNames!fldNameId

'save the id of the current record
intCurName = rsNames!fldNameId
End If

'set the command to the current connection
Set cmdCommand.ActiveConnection = cnDb
'set the insert or update SQL statement to the command text
cmdCommand.CommandText = strSQL
'execute the delete command against the database
cmdCommand.Execute

'while connected to the database, go ahead and
'repopulate the recordset to make sure it contains
'the most current values from the database.
Set rsNames.ActiveConnection = cnDb
rsNames.Requery
Set rsNames.ActiveConnection = Nothing

'move back to the contact that was current before the
'requery
If intCurName > 0 Then
'move back to the contact that was just updated
rsNames.Find "[fldNameId] = " & intCurName
Else
'if just added new record, move to the beginning of
'the recordset
rsNames.MoveFirst
End If

'reset add mode flag to false
blnAddMode = False

'populate the controls on the form
Call PopulateControlsOnForm

End If


End Sub

Private Sub btnAdd_Click()
Call clearControls
blnAddMode = True

End Sub

 

[SkipVought]

Hi,

You don't have rsNames defined as an ADODB.recordset

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[miscluce]

yes, that is declared at the top of my module

 

[SkipVought]

give me a break.

I'm having to define many. which ones DID or did you NOT define.

Please post the module level Dims as well!!!

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[miscluce]

Option Compare Database
Option Explicit

Dim rsNames As ADODB.Recordset
Dim cnDb As ADODB.Connection
Dim strConnection As String
Dim blnAddMode As Boolean

Sub clearControls()
Me.txtNameId = ""
Me.txtFirstName = ""
Me.txtLastName = ""
Me.txtTitle = ""

End Sub

Private Sub btnAdd_Click()
Call clearControls
blnAddMode = True

End Sub

Private Sub btnFirst_Click()
If Not rsNames.BOF And Not rsNames.EOF Then
rsNames.MoveFirst
Call PopulateControlsOnForm
blnAddMode = False
End If
End Sub



Private Sub btnLast_Click()
If Not rsNames.BOF And Not rsNames.EOF Then
rsNames.MoveLast
Call PopulateControlsOnForm
blnAddMode = False
End If
End Sub

Private Sub btnNext_Click()
If Not rsNames.EOF Then
rsNames.MoveNext
Call PopulateControlsOnForm
blnAddMode = False
End If
End Sub

Private Sub btnPrevious_Click()
If Not rsNames.BOF Then
rsNames.MovePrevious
Call PopulateControlsOnForm
blnAddMode = False
End If
End Sub

Private Sub btnSave_Click()
Call SaveCurrentRecord

End Sub

Private Sub Combo22_AfterUpdate()
Me.txtNameId = Combo22.Column(0)
Me.txtFirstName = Combo22.Column(1)
Me.txtLastName = Combo22.Column(2)
Me.txtTitle = Combo22.Column(3)
End Sub

Private Sub Form_Load()
strConnection = "Provider=sqloledb;Data Source=MISFS;" & "Integrated Security=SSPI;Initial Catalog=Sample"

'create a new connection instance and open it using the connection string
Set cnDb = New ADODB.Connection
cnDb.Open strConnection

'create a new instance of a recordset
Set rsNames = New ADODB.Recordset

'set various properties of the recordset
With rsNames
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'open the recordset based on tbl table using the existing connection
.Open "SELECT * FROM tblName", cnDb
'disconnect the recor.dset
.ActiveConnection = Nothing
'sort the recordset
.Sort = "fldLastName, fldFirstName"
End With

'if the recordset is empty
If rsNames.BOF And rsNames.EOF Then
Exit Sub
Else
'move to the first record
rsNames.MoveFirst
'populate the controls on the form
Call PopulateControlsOnForm
End If

'close the database connection and release it from memory
cnDb.Close
Set cnDb = Nothing

End Sub
Sub PopulateControlsOnForm()

'Populate the controls on the form with the values of the
'current record in the local disconnected recordset.
'Use the same field names as the tblContacts table from
'which it was generated.
If Not rsNames.BOF And Not rsNames.EOF Then
Me.txtNameId = rsNames!fldNameId
Me.txtLastName = rsNames!fldLastName
Me.txtFirstName = rsNames!fldFirstName
Me.txtTitle = rsNames!fldTitle
ElseIf rsNames.BOF Then
'past beginning of recordset so move to next record
rsNames.MoveNext
ElseIf rsNames.EOF Then
'past end of recordset so move back to previous record
rsNames.MovePrevious
End If

End Sub
Sub SaveCurrentRecord()

Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim strSQL As String

If Not rsNames.BOF And Not rsNames.EOF Then

'create a new connection instance and open it using the connection string
Set cnDb = New ADODB.Connection
cnDb.Open strConnection

Dim intCurName As Integer
intCurName = 0

'if adding a new record
If blnAddMode = True Then

'create SQL to insert a new record into the database
'containing the values on the form
strSQL = "INSERT INTO tblName(" & _
"fldLastName, fldFirstName, fldTitle) " & _
"VALUES (" & _
"'" & Me.txtLastName & "', " & _
"'" & Me.txtFirstName & "', " & _
"'" & Me.txtTitle & "') "

Else
'create SQL to update the existing record in the
'database with the values on the form
strSQL = "UPDATE tblName SET " & _
"fldLastName = '" & Me.txtLastName & "', " & _
"fldFirstName = '" & Me.txtFirstName & "', " & _
"fldTitle = '" & Me.txtTitle & "', " & _
"WHERE fldNameId = " & rsNames!fldNameId

'save the id of the current record
intCurName = rsNames!fldNameId
End If

'set the command to the current connection
Set cmdCommand.ActiveConnection = cnDb
'set the insert or update SQL statement to the command text
cmdCommand.CommandText = strSQL
'execute the delete command against the database
cmdCommand.Execute

'while connected to the database, go ahead and
'repopulate the recordset to make sure it contains
'the most current values from the database.
Set rsNames.ActiveConnection = cnDb
rsNames.Requery
Set rsNames.ActiveConnection = Nothing

'move back to the contact that was current before the
'requery
If intCurName > 0 Then
'move back to the contact that was just updated
rsNames.Find "[fldNameId] = " & intCurName
Else
'if just added new record, move to the beginning of
'the recordset
rsNames.MoveFirst
End If

'reset add mode flag to false
blnAddMode = False

'populate the controls on the form
Call PopulateControlsOnForm

End If


End Sub

 

[miscluce]

also, when I do an update in my save click event I get an incorrect syntax near the keyword "WHERE".

If you can help me with that , it would be greatly appreciated.

 

[JerryKlmns]

miscluce

Set a breakpoint at the start of your SaveCurrentRecord sub and use F8 to run step by step. You 'll get till the error.

About your incorrect syntax near the keyword "WHERE", when you have passed the strSQL = ..., type in immediate window
?strSQL
and check it there. Copy paste that in a new query SQL view pane and switch to design.

 

[HarleyQuinn]

The problem with your WHERE clause looks to be that you've got a comma after the value (Me.txtTitle) before the WHERE. If you ditch the comma it should work.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[miscluce]

great. thankas this both problems are ifxed.

 

[HarleyQuinn]

Glad to help, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.