Re: Modifying the current record in code

Hi Robbie

You should be able to modify the current record, either using an update
query or using a DAO .Edit/.Update, provided the current record is not
dirty.

I suggest you force a save record before you run your update code:

If Me.Dirty Then RunCommand acCmdSaveRecord
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Robbie" <Robbie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:69207063-E3B6-4F67-89D9-4DDC51462F13@xxxxxxxxxxxxxxxx
> Thank you for the help. As noted at the end of my post, I have
> successfully
> used code such as "LastStatus = Status" (I usually leave off the "me"s).
> It
> is my fault for not being more clear on the problem I was asking since you
> did answer the question I asked even though I already had the answer.
>
> While the method of directly assigning the variables works for the current
> record, I continue to get the same error message while iterating through a
> recordset.
>
> However, I believe I finally found the problem. I think that iterating
> through the recordset would hit the current record and cause the problem
> (though I believe other records still threw the error; I still haven't
> figured the exact cause).
>
> Because rs.FindFirst doesn't appear to support subqueries (i.e. (Field IN
> (SELECT ...))) I think the best thing to do is run an update query and the
> requery the form.
>
> "Graham Mandeno" wrote:
>
>> Hi Robbie
>>
>> You don't need to open another recordset - in fact that is what is
>> causing
>> the problem. Refer to the fields directly via the fom's own recordset:
>>
>> If (complete) Then
>> Me!LastStatus = Me!Status
>> Me!Status = CStatus
>> Else
>> Me!Status = Me!LastStatus
>> Me!LastStatus = CStatus
>> End If
>>
>> You should remove the Edit and Update also.
>> --
>> Good Luck!
>>
>> Graham Mandeno [Access MVP]
>> Auckland, New Zealand
>>
>> "Robbie" <Robbie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:B1FAEAC3-91FC-45BF-843A-688720F53CAB@xxxxxxxxxxxxxxxx
>> >I am trying to modify values of the currently selected record of a form
>> >in
>> >VB
>> > Code. The following gets called when the check box is clicked on a
>> > treeview
>> > on the form:
>> >
>> > rs.Edit
>> > If (complete) Then
>> > rs("LastStatus") = rs("Status")
>> > rs("Status") = CStatus
>> > Else
>> > rs("Status") = rs("LastStatus")
>> > rs("LastStatus") = CStatus
>> > End If
>> > rs.Update
>> >
>> > I get the error message, "Run-time error '31'97': The Microsoft Jet
>> > database
>> > engine stopped the provess because you and another user are attempting
>> > to
>> > change the same data at the same time." on the rs.Update.
>> >
>> > I thought that rs.Update dbUpdateCurrentRecord might solve the problem,
>> > but
>> > I get an "Invalid Argument" error on that call.
>> >
>> > I have tried setting rs = Me.recordset and Me.recordsetclone; neither
>> > seem
>> > to work. I have also tried calling me.refresh first and setting
>> > me.dirty
>> > =
>> > false all to no avail.
>> >
>> > I am assuming that since the mouse_click event code sets the current
>> > record
>> > based on which node was clicked in the treeview, that access is using
>> > something akin to "Me.recordset.edit" as soon as it changes to the
>> > current
>> > record. This "locks" the current record and I am not allowed to
>> > concurrently
>> > edit the data.
>> >
>> > Unfortunately, I cannot put code in the "current" event procedure to
>> > disallow edits since I have no way of knowing if the node has been
>> > "checked"
>> > or just simply clicked.
>> >
>> > I realize that I can access the fields of the recordset directly in VB
>> > (i.e.
>> > "LastStatus = Status"). This would be fine in most cases, but there
>> > are
>> > others in which I am forced to iterate over the records in the
>> > recordset
>> > and
>> > this may include the "current record" that is open on the form.
>> > Perhaps I
>> > could just check for the condition that the current record in the
>> > recordset
>> > is the same as the one in the form, but I would really like to avoid
>> > this.
>> > I
>> > have a hard time believing and can't force access to temporarily
>> > relinquish
>> > its lock on the current record.
>> >
>> > So, my question is, how can I programmtically edit the values of fields
>> > in
>> > the current record of the form using a recordset?
>>
>>
>>


.

Relevant Pages

  • Re: Form seemed Read-Only
    ... modify a record. ... If it's a query, try opening the ... datasheet view: is there a blank new record at the bottom? ... can you edit ...
    (microsoft.public.access.forms)
  • Cant edit record on a form
    ... i have a form use to edit two table that are linked by a field. ... So the form recordsource is a query ... Recently i modify the link between the two table ... Is there a way to edit with a multiple field link or is it impossible ...
    (microsoft.public.access.formscoding)
  • Re: Keeping Tracking of whos adding new data..
    ... Macro to open the form in Add mode, but no edit mode. ... You should be able to use your form query to set the RC criteria to... ... Not sure what you're doing with the TempLue. ... Candia Computer Consulting. ...
    (microsoft.public.access.formscoding)
  • Re: DB Result Wizard Error
    ... open the hidden folder /_fpclass/ and edit the ... > For instance Equipment Serial Number, Equipment Model Number, Cost Accounting ... > to display the new equipment information. ... > is in the table was added to the query. ...
    (microsoft.public.frontpage.programming)
  • Re: Making sure data is saved on closing application
    ... >functions that modify the values, ... >this guarantees that you can't ever make a change without causing the modified flag to be ... >>>The application lets you edit and add records to a database (as well ... >MVP Tips: http://www.flounder.com/mvp_tips.htm ...
    (microsoft.public.vc.mfc)