Code problem

S

Sandy

I recently purchased a book "Beginning Access 2003 VBA" in an attempt to
learn more about - naturally enough - Access VBA.

The book gives examples but this particular one has an error (I think). If I
delete the first record then all is fine however if I delete the last
record then I receive :

"Run-time error ('3021')
Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record."

I have indicated the problem piece of code between aterisks.

Code :-

Sub DeleteRecord()

'don't let the user issue a delete command if in add mode
If blnAddMode = True Then
Exit Sub
End If

Dim intResponse As Integer

'confirm that user really wants to delete record
intResponse = MsgBox("Are you sure you want to delete this record?",
vbYesNo)

'if the user cancels delete, then exit this procedure
If intResponse = vbNo Then
Exit Sub
End If

'declare and create new command object
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command

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

'declare variable to store current contact
Dim intCurContact As Integer
intCurContact = 0

'generate SQL command to delete current record
Dim strSQL As String
strSQL = "DELETE FROM tblContacts WHERE intContactId = " &
rsContacts!intContactId

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


***************************************
'move to the next record in the local recordset since the
'current one is being deleted
If Not rsContacts.EOF Then
rsContacts.MoveNext
'save the id of the current (next) record
intCurContact = rsContacts!intContactId
End If
****************************************

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

'move back to the contact that was current before the
'requery
rsContacts.Find "[intContactId] = " & intCurContact


'populate the controls on the form
Call PopulateControlsOnForm

End Sub

Thanks
Sandy
 
S

Sandy

I did try this :-

If Not rsContacts.EOF Then
rsContacts.MoveNext
'save the id of the current (next) record
intCurContact = rsContacts!intContactId
ElseIf rsContacts.EOF Then
rsContacts.MovePrevious
'save the id of the current (previous) record
intCurContact = rsContacts!intContactId
End If

to no avail.
 
G

George Nicholson

Seems like a fairly common misconception is at work here.

..EOF is False when you are on the last record, or on any record for that
matter. It becomes True only when you move PAST the last record. BOF acts
the same way. If either EOF or BOF are True, you are NOT on a record. If
they are both True, there are no records at all in the Recordset.

So, *if* you are already on the last record then
If Not rsContacts.EOF Then
rsContacts.MoveNext
Click to expand...
*would* move you Past the last record. EOF would then become True and
causing
intCurContact = rsContacts!intContactId
Click to expand...
to generate a "No Current Record" error when you try to read the "current"
(non-existant) record.

Therefore, try something like:

' Move to the next record. If no next record, move to Last record. Exit if
there are no records.
If Not rsContacts.EOF Then
rsContacts.MoveNext
If rsContacts.EOF Then
rsContacts.MoveLast
End If
Else
If rsContacts.BOF Then
'No records remain. Using MoveXxxx will cause error.
Exit Sub '?? Not sure what you would want to do
Else
rsContacts.MoveLast
End If
End If
intCurContact = rsContacts!intContactId

'Now you can do the requery and reposition the recordset on intContactID

--
HTH,
George

Sandy said:
I did try this :-

If Not rsContacts.EOF Then
rsContacts.MoveNext
'save the id of the current (next) record
intCurContact = rsContacts!intContactId
ElseIf rsContacts.EOF Then
rsContacts.MovePrevious
'save the id of the current (previous) record
intCurContact = rsContacts!intContactId
End If

to no avail.

Sandy said:
I recently purchased a book "Beginning Access 2003 VBA" in an attempt to
learn more about - naturally enough - Access VBA.

The book gives examples but this particular one has an error (I think).
If I delete the first record then all is fine however if I delete the
last record then I receive :

"Run-time error ('3021')
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record."

I have indicated the problem piece of code between aterisks.

Code :-

Sub DeleteRecord()

'don't let the user issue a delete command if in add mode
If blnAddMode = True Then
Exit Sub
End If

Dim intResponse As Integer

'confirm that user really wants to delete record
intResponse = MsgBox("Are you sure you want to delete this record?",
vbYesNo)

'if the user cancels delete, then exit this procedure
If intResponse = vbNo Then
Exit Sub
End If

'declare and create new command object
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command

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

'declare variable to store current contact
Dim intCurContact As Integer
intCurContact = 0

'generate SQL command to delete current record
Dim strSQL As String
strSQL = "DELETE FROM tblContacts WHERE intContactId = " &
rsContacts!intContactId

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


***************************************
'move to the next record in the local recordset since the
'current one is being deleted
If Not rsContacts.EOF Then
rsContacts.MoveNext
'save the id of the current (next) record
intCurContact = rsContacts!intContactId
End If
****************************************

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

'move back to the contact that was current before the
'requery
rsContacts.Find "[intContactId] = " & intCurContact


'populate the controls on the form
Call PopulateControlsOnForm

End Sub

Thanks
Sandy
Click to expand...
Click to expand...
 
G

George Nicholson

Correction.

Since you are deleting the current record from the source rather than the
recordset, I would suggest using the following instead:

' Move to the next record. If no next record, move to Previous record. Exit
if there are no records.
If Not rsContacts.EOF Then
rsContacts.MoveNext
If rsContacts.EOF Then
rsContacts.MovePrevious
rsContacts.MovePrevious
End If
Else
' Don't think this could happen under these circumstances...
rsContacts.MovePrevious
End If
If rsContacts.BOF Then
' There will be no records remaining after requery.
Exit Sub
Else
intCurContact = rsContacts!intContactId
End If

'Now you can do the requery and reposition the recordset on intContactID

--
HTH,
George


George Nicholson said:
Seems like a fairly common misconception is at work here.

.EOF is False when you are on the last record, or on any record for that
matter. It becomes True only when you move PAST the last record. BOF acts
the same way. If either EOF or BOF are True, you are NOT on a record. If
they are both True, there are no records at all in the Recordset.

So, *if* you are already on the last record then
If Not rsContacts.EOF Then
rsContacts.MoveNext
Click to expand...
*would* move you Past the last record. EOF would then become True and
causing
intCurContact = rsContacts!intContactId
Click to expand...
to generate a "No Current Record" error when you try to read the "current"
(non-existant) record.

Therefore, try something like:

' Move to the next record. If no next record, move to Last record. Exit if
there are no records.
If Not rsContacts.EOF Then
rsContacts.MoveNext
If rsContacts.EOF Then
rsContacts.MoveLast
End If
Else
If rsContacts.BOF Then
'No records remain. Using MoveXxxx will cause error.
Exit Sub '?? Not sure what you would want to do
Else
rsContacts.MoveLast
End If
End If
intCurContact = rsContacts!intContactId

'Now you can do the requery and reposition the recordset on intContactID

--
HTH,
George

Sandy said:
I did try this :-

If Not rsContacts.EOF Then
rsContacts.MoveNext
'save the id of the current (next) record
intCurContact = rsContacts!intContactId
ElseIf rsContacts.EOF Then
rsContacts.MovePrevious
'save the id of the current (previous) record
intCurContact = rsContacts!intContactId
End If

to no avail.

Sandy said:
I recently purchased a book "Beginning Access 2003 VBA" in an attempt to
learn more about - naturally enough - Access VBA.

The book gives examples but this particular one has an error (I think).
If I delete the first record then all is fine however if I delete the
last record then I receive :

"Run-time error ('3021')
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record."

I have indicated the problem piece of code between aterisks.

Code :-

Sub DeleteRecord()

'don't let the user issue a delete command if in add mode
If blnAddMode = True Then
Exit Sub
End If

Dim intResponse As Integer

'confirm that user really wants to delete record
intResponse = MsgBox("Are you sure you want to delete this record?",
vbYesNo)

'if the user cancels delete, then exit this procedure
If intResponse = vbNo Then
Exit Sub
End If

'declare and create new command object
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command

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

'declare variable to store current contact
Dim intCurContact As Integer
intCurContact = 0

'generate SQL command to delete current record
Dim strSQL As String
strSQL = "DELETE FROM tblContacts WHERE intContactId = " &
rsContacts!intContactId

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


***************************************
'move to the next record in the local recordset since the
'current one is being deleted
If Not rsContacts.EOF Then
rsContacts.MoveNext
'save the id of the current (next) record
intCurContact = rsContacts!intContactId
End If
****************************************

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

'move back to the contact that was current before the
'requery
rsContacts.Find "[intContactId] = " & intCurContact


'populate the controls on the form
Call PopulateControlsOnForm

End Sub

Thanks
Sandy
Click to expand...
Click to expand...
Click to expand...
 
S

Sandy

George

Thank you it works perfectly

Sandy

George Nicholson said:
Seems like a fairly common misconception is at work here.

.EOF is False when you are on the last record, or on any record for that
matter. It becomes True only when you move PAST the last record. BOF acts
the same way. If either EOF or BOF are True, you are NOT on a record. If
they are both True, there are no records at all in the Recordset.

So, *if* you are already on the last record then
If Not rsContacts.EOF Then
rsContacts.MoveNext
Click to expand...
*would* move you Past the last record. EOF would then become True and
causing
intCurContact = rsContacts!intContactId
Click to expand...
to generate a "No Current Record" error when you try to read the "current"
(non-existant) record.

Therefore, try something like:

' Move to the next record. If no next record, move to Last record. Exit if
there are no records.
If Not rsContacts.EOF Then
rsContacts.MoveNext
If rsContacts.EOF Then
rsContacts.MoveLast
End If
Else
If rsContacts.BOF Then
'No records remain. Using MoveXxxx will cause error.
Exit Sub '?? Not sure what you would want to do
Else
rsContacts.MoveLast
End If
End If
intCurContact = rsContacts!intContactId

'Now you can do the requery and reposition the recordset on intContactID

--
HTH,
George

Sandy said:
I did try this :-

If Not rsContacts.EOF Then
rsContacts.MoveNext
'save the id of the current (next) record
intCurContact = rsContacts!intContactId
ElseIf rsContacts.EOF Then
rsContacts.MovePrevious
'save the id of the current (previous) record
intCurContact = rsContacts!intContactId
End If

to no avail.

Sandy said:
I recently purchased a book "Beginning Access 2003 VBA" in an attempt to
learn more about - naturally enough - Access VBA.

The book gives examples but this particular one has an error (I think).
If I delete the first record then all is fine however if I delete the
last record then I receive :

"Run-time error ('3021')
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record."

I have indicated the problem piece of code between aterisks.

Code :-

Sub DeleteRecord()

'don't let the user issue a delete command if in add mode
If blnAddMode = True Then
Exit Sub
End If

Dim intResponse As Integer

'confirm that user really wants to delete record
intResponse = MsgBox("Are you sure you want to delete this record?",
vbYesNo)

'if the user cancels delete, then exit this procedure
If intResponse = vbNo Then
Exit Sub
End If

'declare and create new command object
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command

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

'declare variable to store current contact
Dim intCurContact As Integer
intCurContact = 0

'generate SQL command to delete current record
Dim strSQL As String
strSQL = "DELETE FROM tblContacts WHERE intContactId = " &
rsContacts!intContactId

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


***************************************
'move to the next record in the local recordset since the
'current one is being deleted
If Not rsContacts.EOF Then
rsContacts.MoveNext
'save the id of the current (next) record
intCurContact = rsContacts!intContactId
End If
****************************************

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

'move back to the contact that was current before the
'requery
rsContacts.Find "[intContactId] = " & intCurContact


'populate the controls on the form
Call PopulateControlsOnForm

End Sub

Thanks
Sandy
Click to expand...
Click to expand...
Click to expand...
 
S

Sandy

But this one works even better.
Sandy

George Nicholson said:
Correction.

Since you are deleting the current record from the source rather than the
recordset, I would suggest using the following instead:

' Move to the next record. If no next record, move to Previous record.
Exit if there are no records.
If Not rsContacts.EOF Then
rsContacts.MoveNext
If rsContacts.EOF Then
rsContacts.MovePrevious
rsContacts.MovePrevious
End If
Else
' Don't think this could happen under these circumstances...
rsContacts.MovePrevious
End If
If rsContacts.BOF Then
' There will be no records remaining after requery.
Exit Sub
Else
intCurContact = rsContacts!intContactId
End If

'Now you can do the requery and reposition the recordset on intContactID

--
HTH,
George


George Nicholson said:
Seems like a fairly common misconception is at work here.

.EOF is False when you are on the last record, or on any record for that
matter. It becomes True only when you move PAST the last record. BOF acts
the same way. If either EOF or BOF are True, you are NOT on a record. If
they are both True, there are no records at all in the Recordset.

So, *if* you are already on the last record then
If Not rsContacts.EOF Then
rsContacts.MoveNext
Click to expand...
*would* move you Past the last record. EOF would then become True and
causing
intCurContact = rsContacts!intContactId
Click to expand...
to generate a "No Current Record" error when you try to read the
"current" (non-existant) record.

Therefore, try something like:

' Move to the next record. If no next record, move to Last record. Exit
if there are no records.
If Not rsContacts.EOF Then
rsContacts.MoveNext
If rsContacts.EOF Then
rsContacts.MoveLast
End If
Else
If rsContacts.BOF Then
'No records remain. Using MoveXxxx will cause error.
Exit Sub '?? Not sure what you would want to do
Else
rsContacts.MoveLast
End If
End If
intCurContact = rsContacts!intContactId

'Now you can do the requery and reposition the recordset on intContactID

--
HTH,
George

Sandy said:
I did try this :-

If Not rsContacts.EOF Then
rsContacts.MoveNext
'save the id of the current (next) record
intCurContact = rsContacts!intContactId
ElseIf rsContacts.EOF Then
rsContacts.MovePrevious
'save the id of the current (previous) record
intCurContact = rsContacts!intContactId
End If

to no avail.

I recently purchased a book "Beginning Access 2003 VBA" in an attempt
to learn more about - naturally enough - Access VBA.

The book gives examples but this particular one has an error (I think).
If I delete the first record then all is fine however if I delete the
last record then I receive :

"Run-time error ('3021')
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record."

I have indicated the problem piece of code between aterisks.

Code :-

Sub DeleteRecord()

'don't let the user issue a delete command if in add mode
If blnAddMode = True Then
Exit Sub
End If

Dim intResponse As Integer

'confirm that user really wants to delete record
intResponse = MsgBox("Are you sure you want to delete this record?",
vbYesNo)

'if the user cancels delete, then exit this procedure
If intResponse = vbNo Then
Exit Sub
End If

'declare and create new command object
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command

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

'declare variable to store current contact
Dim intCurContact As Integer
intCurContact = 0

'generate SQL command to delete current record
Dim strSQL As String
strSQL = "DELETE FROM tblContacts WHERE intContactId = " &
rsContacts!intContactId

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


***************************************
'move to the next record in the local recordset since the
'current one is being deleted
If Not rsContacts.EOF Then
rsContacts.MoveNext
'save the id of the current (next) record
intCurContact = rsContacts!intContactId
End If
****************************************

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

'move back to the contact that was current before the
'requery
rsContacts.Find "[intContactId] = " & intCurContact


'populate the controls on the form
Call PopulateControlsOnForm

End Sub

Thanks
Sandy
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find Method on Unbound Form Help 4
E-mail Button in Access Form 2
[ODBC Driver Manager] Data source name not found and no default dr 8
Allen Browne's Audit Trail Not Working 1
delete sub and stay on same parent record 4
Help with code 3
ADO Query Question 1
New Record 8

Top