Solved: How is a Record identified in a Recordset, is there a set PK?

[ibgreat]

I am a bit confused by the text I am using. I set up a form using vba. However, I am not clear on how it is identifying the record number of the recordset. In the example in my text there appears to be a variable intContactId, but it is not defined in the code. The book's example works, but again my own does not. I switched the variable to match my recordset name (intPersonID). What am I missing? Here is sample procedure that uses the reference. I am getting an error when set strSQL.



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 cnABCdb = New ADODB.Connection
cnABCdb.Open strConnection
'declare variable to store current contact
Dim intCurContact As Integer
'not clear why intCurContact is set to 0
intCurContact = 0
'generate SQL command to delete current record
Dim strSql As String
strSql = "DELETE FROM [tblPerson-D] WHERE [PersonID] = " & _
rsPerson!intPersonID
'set the command to the current connection
Set cmdCommand.ActiveConnection = cnABCdb
'set the delete SQL statement to the command text
cmdCommand.CommandText = strSql
'execute the delete command against the database
cmdCommand.Execute
'move to the previous record in the local recordset since the
'current one is being deleted
If Not rsPerson.BOF Then
rsPerson.MovePrevious
'save the id of the current (previous) record
intCurContact = rsPerson!intPersonID
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 rsPerson.ActiveConnection = cnABCdb
rsPerson.Requery
Set rsPerson.ActiveConnection = Nothing
'move back to the contact that was current before the
'requery
rsPerson.Find "[intPersonID] = " & intCurContact
'populate the controls on the form
Call PopulateControlsOnForm
End Sub

[CreganTur]

In the example in my text there appears to be a variable intContactId, but it is not defined in the code.
Are you using Option Explicit? This is an option that forces you to Declare any variables you use in Code. It's something you should be using if you're not.

In the VBE click on Tools -> Options and put a checkmark next to 'Require Variable Declaration'

Also, when you post VBA code use the VBA button- it'll wrap your code in VBA tags and format it according to VBIDE- it'll make it easier to read.


am getting an error when set strSQL.
What error?

You talk about a recordset, but I can't find the declaration for your recordset- I'm guessing that's what the variable 'rsPerson' is supposed to be... Or is rsPersons the name of the form that this code is behind?

I can see what you're trying to do with this code, but the ADO Conection methods are all mixed up. I can try to rewrite it and simplify it for you, but I'll need to see what you're using for your connection string (I can see where you are using strConnection, but no value is assigned to that variable in this sub)


The book's example works
What book? (Just curious)

[ibgreat]

Sorry, hadn't noticed the VBA button.


What book? (Just curious)
The book is "Beginning Access 2003 VBA" By WROX. I'm always open to additional suggestions for reading material.


What error?

You talk about a recordset, but I can't find the declaration for your recordset- I'm guessing that's what the variable 'rsPerson' is supposed to be.


The error is "The item cannot be found in the collection corresponding to the requested name or ordinal."

Here is the General Declarations and the Procedure for Form_Load that include the info you were looking for. Please clarify what you mean by the ADO Connection Methods are all mixed up. I believe these to be consistent with my book, I will double check the references.


Option Compare Database
Option Explicit
Dim rsPerson As ADODB.Recordset
Dim cnABCdb As ADODB.Connection
Dim strConnection As String
Dim blnAddMode As Boolean

Private Sub Form_Load()
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\BehaviorDatabaseWorking.mdb;"
'create a new connection instance and open it using the connection string
Set cnABCdb = New ADODB.Connection
cnABCdb.Open strConnection
'create a new instance of a recordset
Set rsPerson = New ADODB.Recordset
'set various properties of the recordset
With rsPerson
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'open the recordset based on tblPerson-D table using the existing connection
.Open "[tblPerson-D]", cnABCdb
'disconnect the recordset
.ActiveConnection = Nothing
End With
'if the recordset is empty
If rsPerson.BOF And rsPerson.EOF Then
MsgBox ("The recordset is empty.")
Exit Sub
Else
'move to the first record
rsPerson.MoveFirst
'populate the controls on the form
Call PopulateControlsOnForm
End If
'close the database connection and release it from memory
cnABCdb.Close
Set cnABCdb = Nothing
End Sub

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 cnABCdb = New ADODB.Connection
cnABCdb.Open strConnection
'declare variable to store current contact
Dim intCurContact As Integer
'not clear why intCurContact is set to 0
intCurContact = 0
'generate SQL command to delete current record
Dim strSql As String
strSql = "DELETE FROM [tblPerson-D] WHERE [PersonID] = " & _
rsPerson!intPersonID
'set the command to the current connection
Set cmdCommand.ActiveConnection = cnABCdb
'set the delete SQL statement to the command text
cmdCommand.CommandText = strSql
'execute the delete command against the database
cmdCommand.Execute
'move to the previous record in the local recordset since the
'current one is being deleted
If Not rsPerson.BOF Then
rsPerson.MovePrevious
'save the id of the current (previous) record
intCurContact = rsPerson!intPersonID
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 rsPerson.ActiveConnection = cnABCdb
rsPerson.Requery
Set rsPerson.ActiveConnection = Nothing
'move back to the contact that was current before the
'requery
rsPerson.Find "[intPersonID] = " & intCurContact
'populate the controls on the form
Call PopulateControlsOnForm
End Sub


Thanks, I appreciate your time and explanations vs. just fixing code.:bow:

[ibgreat]

Forgot to mention, the rsPerson is the recordset, but I am sure you figured that out looking at the code.

Also, when back through the code to ensure I was using the ADO connections consistent with my text examples and I believe I am.

[CreganTur]

The error is "The item cannot be found in the collection corresponding to the requested name or ordinal."
That error means that your SQL statement is referring to a table or field that doesn't exist... I'm wondering if it has to do with your referring to the ADO Recordset as a part of the WHERE clause.

Or, it could have something to do with the oddities of using VBA variables and methods within SQL strings. If you use a VBA variable in a SQL string you have to wrap it with symbols that 'declare' the data type of the variable. For strings you use single quotes:
SELECT * FROM tblNAme WHERE tblName.FieldName = '" & vbaVariable & "'
For dates you use pound signs:
SELECT * FROM tblNAme WHERE tblName.FieldName = #" & vbaDate & "#

You could try this(assuming you're using a string value):
strSql = "DELETE FROM [tblPerson-D] WHERE [PersonID] = '" & _
rsPerson!intPersonID & "'"

Or you can try declaring a new variable to hold the recordset's value, and reference the variable in your SQL String... like this:
Dim strPersID As String
strPersID = rsPerson!intPersonID
strSql = "DELETE FROM [tblPerson-D] WHERE [PersonID] = '" & strPersID & "'"


Please clarify what you mean by the ADO Connection Methods are all mixed up.
When I was looking at the code in your first post I saw that you had Dim statments scattered throughout the entirety of the code, and that made me think that something was wrong. Generally all of your Dim statements are in one section somewhere after your Sub or Function is created... it makes it easier to read that way... but it's also personal preference.

Your connection methods look sound. You could replace:
'set the command to the current connection
Set cmdCommand.ActiveConnection = cnABCdb
'set the delete SQL statement to the command text
cmdCommand.CommandText = strSql
'execute the delete command against the database
cmdCommand.Execute
With this:
With cmdCommand
.ActiveConnection = cnABCdb '<<<set the command to the current connection
.CommandText = strSql '<<<set the delete SQL statement to the command text
.Execute '<<<execute the delete command against the database
End With

[Mavyak]

'generate SQL command to delete current record
Dim strSql As String
strSql = "DELETE FROM [tblPerson-D] WHERE [PersonID] = " & _
rsPerson!intPersonID
Should that last line have the "int" portion in it? I suspect that "inPersonID" is a variable of type Integer while the actual field name in the table is probably "PersonId" (without the "int"). If the "int" portion is, in fact, part of the field name then the second to the last line should have it in the WHERE clause.

[ibgreat]

I don't think it has to do with the WHERE statement. The book example uses the same WHERE statment without a problem. I can consider how to modify further.

Your other suggestion were helpful, but did not provide any change. Additional, to the first thought it seems to be a problem with the rsPerson record. When I tried to place rsPerson.intPersonID into a variable the problem came up there.

Mavyaks post just popped up as well and it probably explains the problem. I didn't initially realize the rsContacts took the field names of the table, but this makes sense. I had changed this elsewhere in the code to differentiate, but did not change it here. Thanks for the catch Took a look and indeed the code is passing the statement know. But now I get a data type mismatch when the command is executed. When the data is passed from the table to the recordset it obviously remains in the same format. Could this be because my form data types are just set to text?

[ibgreat]

Could this be because my form data types are just set to text?

Oops, double checked, they actually are not set to anything.


Also, forgot to mention to CreganTur the I appreciate the With statement on the command object. This is cleaner, thanks for the catch.

[ibgreat]

Got it, just had to play with the sql statement a bit.

I appreciate your help, while I'm trying to learn the basics... the feedback is invaluable.:clap: