RE: Code to Update Recordset only works intermittently
- From: "swedbera" <swedbera@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 13 Oct 2005 04:47:05 -0700
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!
"'69 Camaro" wrote:
> 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
.
- References:
- Code to Update Recordset only works intermittently
- From: swedbera
- RE: Code to Update Recordset only works intermittently
- From: '69 Camaro
- Code to Update Recordset only works intermittently
- Prev by Date: Re: Copy Files using VBA
- Next by Date: Re: Late binding with the VBA or Access libraries
- Previous by thread: RE: Code to Update Recordset only works intermittently
- Next by thread: RE: User Defined Type - Not Defined
- Index(es):
Relevant Pages
|
