RE: Code to Update Recordset only works intermittently
- From: "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@xxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 12 Oct 2005 11:41:04 -0700
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.
"swedbera" wrote:
> 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
.
- Follow-Ups:
- RE: Code to Update Recordset only works intermittently
- From: swedbera
- RE: Code to Update Recordset only works intermittently
- References:
- Code to Update Recordset only works intermittently
- From: swedbera
- Code to Update Recordset only works intermittently
- Prev by Date: Re: Strange VBA Editor Problems
- Next by Date: Re: Strange VBA Editor Problems
- Previous by thread: Code to Update Recordset only works intermittently
- Next by thread: RE: Code to Update Recordset only works intermittently
- Index(es):
Relevant Pages
|
