Code to Update Recordset only works intermittently

[swedbera]

Could someone please take a look at my code and tell me why it only works
intermittently? I cannot figure it out and need another pair of eyes to take
a look. I would think that it would either work or not. I have two other
forms in my database that do the same thing and they both work all of the
time. The difference between the code behind those forms and this form is
that the reference to the index is set differently and the primary key in
both of the other forms is the field that is being updated through code. If
that is the problem, I'm not sure how to fix it.

Thanks,

Arlene

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim msg As String, varNumber As Variant

Set cnn = CurrentProject.Connection
' In other form's code, this is set to rst.Index = "PrimaryKey"
rst.Index = "SupId"
rst.Open "tblSite", cnn, adOpenStatic, adLockOptimistic, adCmdTableDirect
'Set up the For Each loop throught the collection
For Each varNumber In Me.lstSites.ItemsSelected
rst.Seek Me.lstSites.ItemData(varNumber), adSeekFirstEQ
rst!SupID = Me.cboNewSupID
rst.Update
Next
Me.cboCurrentSupId = Me.cboNewSupID
Me.cboNewSupID = Null
strsql = "SELECT tblSite.SupID, tblSupervisor.Supervisor,
tblSite.SiteCode, tblSite.SiteType, tblSite.AcctName FROM tblSupervisor INNER
JOIN tblSite ON tblSupervisor.SupID = tblSite.SupID WHERE
[tblSite].[SupId]='" & Me.cboCurrentSupId & "' ORDER BY [tblSite].[AcctName];
"
Me.lstSites.RowSource = strsql
rst.Close

 

['69 Camaro]

Hi, Arlene.

Could someone please take a look at my code and tell me why it only works
intermittently?

The code assumes that the value sought is actually found. If it's not
found, then the cursor is positioned at the end of the Recordset, not at a
record that has the "old" SupID value. And no error is given to the user.

Also, the code won't work properly if any of the following conditions are
not met:

1.) The data provider must support indexes.
2.) The data provider must support the seek method.
3.) The index must be unique, not allow NULL's in any of the fields of the
index, and all fields in the index must be required fields.
4.) A server-side cursor must be used for the Recordset.
5.) No other process can have the record locked during this update.

the primary key in
both of the other forms is the field that is being updated through code.

You're changing the primary key?!! Unless this is to fix normalization
issues created from the poor design of an inexperienced database developer,
then having malleable primary keys is like spitting into the wind. It will
smack you right in the face -- and yet you'll be utterly surprised when it
happens.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Could someone please take a look at my code and tell me why it only works
intermittently? I cannot figure it out and need another pair of eyes to take
a look. I would think that it would either work or not. I have two other
forms in my database that do the same thing and they both work all of the
time. The difference between the code behind those forms and this form is
that the reference to the index is set differently and the primary key in
both of the other forms is the field that is being updated through code. If
that is the problem, I'm not sure how to fix it.

Thanks,

Arlene

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim msg As String, varNumber As Variant

Set cnn = CurrentProject.Connection
' In other form's code, this is set to rst.Index = "PrimaryKey"
rst.Index = "SupId"
rst.Open "tblSite", cnn, adOpenStatic, adLockOptimistic, adCmdTableDirect
'Set up the For Each loop throught the collection
For Each varNumber In Me.lstSites.ItemsSelected
rst.Seek Me.lstSites.ItemData(varNumber), adSeekFirstEQ
rst!SupID = Me.cboNewSupID
rst.Update
Next
Me.cboCurrentSupId = Me.cboNewSupID
Me.cboNewSupID = Null
strsql = "SELECT tblSite.SupID, tblSupervisor.Supervisor,
tblSite.SiteCode, tblSite.SiteType, tblSite.AcctName FROM tblSupervisor INNER
JOIN tblSite ON tblSupervisor.SupID = tblSite.SupID WHERE
[tblSite].[SupId]='" & Me.cboCurrentSupId & "' ORDER BY [tblSite].[AcctName];
"
Me.lstSites.RowSource = strsql
rst.Close

 

[swedbera]

Hi,

Thank you for responding. I hadn't changed my pk, it was just not the field
that I was looking to update. I thought about your response and changed the
order in which my fields were displayed so that the pk field was always
first. I then changed the line referring to the index and set it to
"PrimaryKey", which is a different field, and it works now.

Thanks again!

Hi, Arlene.

Could someone please take a look at my code and tell me why it only works
intermittently?

The code assumes that the value sought is actually found. If it's not
found, then the cursor is positioned at the end of the Recordset, not at a
record that has the "old" SupID value. And no error is given to the user.

Also, the code won't work properly if any of the following conditions are
not met:

1.) The data provider must support indexes.
2.) The data provider must support the seek method.
3.) The index must be unique, not allow NULL's in any of the fields of the
index, and all fields in the index must be required fields.
4.) A server-side cursor must be used for the Recordset.
5.) No other process can have the record locked during this update.

the primary key in
both of the other forms is the field that is being updated through code.

You're changing the primary key?!! Unless this is to fix normalization
issues created from the poor design of an inexperienced database developer,
then having malleable primary keys is like spitting into the wind. It will
smack you right in the face -- and yet you'll be utterly surprised when it
happens.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Could someone please take a look at my code and tell me why it only works
intermittently? I cannot figure it out and need another pair of eyes to take
a look. I would think that it would either work or not. I have two other
forms in my database that do the same thing and they both work all of the
time. The difference between the code behind those forms and this form is
that the reference to the index is set differently and the primary key in
both of the other forms is the field that is being updated through code. If
that is the problem, I'm not sure how to fix it.

Thanks,

Arlene

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim msg As String, varNumber As Variant

Set cnn = CurrentProject.Connection
' In other form's code, this is set to rst.Index = "PrimaryKey"
rst.Index = "SupId"
rst.Open "tblSite", cnn, adOpenStatic, adLockOptimistic, adCmdTableDirect
'Set up the For Each loop throught the collection
For Each varNumber In Me.lstSites.ItemsSelected
rst.Seek Me.lstSites.ItemData(varNumber), adSeekFirstEQ
rst!SupID = Me.cboNewSupID
rst.Update
Next
Me.cboCurrentSupId = Me.cboNewSupID
Me.cboNewSupID = Null
strsql = "SELECT tblSite.SupID, tblSupervisor.Supervisor,
tblSite.SiteCode, tblSite.SiteType, tblSite.AcctName FROM tblSupervisor INNER
JOIN tblSite ON tblSupervisor.SupID = tblSite.SupID WHERE
[tblSite].[SupId]='" & Me.cboCurrentSupId & "' ORDER BY [tblSite].[AcctName];
"
Me.lstSites.RowSource = strsql
rst.Close

 

['69 Camaro]

You're welcome.

I hadn't changed my pk,

That's a relief. When I read the following:

I took that to mean that your database contains code that changes the value
in the primary key field(s) of at least one table in the database, which
throws up a red flag.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

Hi,

Thank you for responding. I hadn't changed my pk, it was just not the
field
that I was looking to update. I thought about your response and changed
the
order in which my fields were displayed so that the pk field was always
first. I then changed the line referring to the index and set it to
"PrimaryKey", which is a different field, and it works now.

Thanks again!

Hi, Arlene.

Could someone please take a look at my code and tell me why it only
works
intermittently?

The code assumes that the value sought is actually found. If it's not
found, then the cursor is positioned at the end of the Recordset, not at
a
record that has the "old" SupID value. And no error is given to the
user.

Also, the code won't work properly if any of the following conditions are
not met:

1.) The data provider must support indexes.
2.) The data provider must support the seek method.
3.) The index must be unique, not allow NULL's in any of the fields of
the
index, and all fields in the index must be required fields.
4.) A server-side cursor must be used for the Recordset.
5.) No other process can have the record locked during this update.

the primary key in
both of the other forms is the field that is being updated through
code.

You're changing the primary key?!! Unless this is to fix normalization
issues created from the poor design of an inexperienced database
developer,
then having malleable primary keys is like spitting into the wind. It
will
smack you right in the face -- and yet you'll be utterly surprised when
it
happens.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.

Could someone please take a look at my code and tell me why it only
works
intermittently? I cannot figure it out and need another pair of eyes
to take
a look. I would think that it would either work or not. I have two
other
forms in my database that do the same thing and they both work all of
the
time. The difference between the code behind those forms and this
form is
that the reference to the index is set differently and the primary key
in
both of the other forms is the field that is being updated through
code. If
that is the problem, I'm not sure how to fix it.

Thanks,

Arlene

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim msg As String, varNumber As Variant

Set cnn = CurrentProject.Connection
' In other form's code, this is set to rst.Index = "PrimaryKey"
rst.Index = "SupId"
rst.Open "tblSite", cnn, adOpenStatic, adLockOptimistic,
adCmdTableDirect
'Set up the For Each loop throught the collection
For Each varNumber In Me.lstSites.ItemsSelected
rst.Seek Me.lstSites.ItemData(varNumber), adSeekFirstEQ
rst!SupID = Me.cboNewSupID
rst.Update
Next
Me.cboCurrentSupId = Me.cboNewSupID
Me.cboNewSupID = Null
strsql = "SELECT tblSite.SupID, tblSupervisor.Supervisor,
tblSite.SiteCode, tblSite.SiteType, tblSite.AcctName FROM tblSupervisor
INNER
JOIN tblSite ON tblSupervisor.SupID = tblSite.SupID WHERE
[tblSite].[SupId]='" & Me.cboCurrentSupId & "' ORDER BY
[tblSite].[AcctName];
"
Me.lstSites.RowSource = strsql
rst.Close