Runtime error '3426' with .edit (for recordset) (1 Viewer)

Hi,

I am trying to develop a database with multiple tables and too many forms. But with that aside, I get about half-way through the program and I reach this one bound form (with two option frames and one text box, all bound), but when I try to update the data and then move to another form, also bound to the same table, I receive a "Runtime error '3426': Action cancelled by associated object." When I dubug, the program is stopped on the .edit line. I don't seem to receive this anywhere else in the program. I use the same code throughout and it seems to work fine until this point.

The code is fairly modular in that I call an update snipet anytime I leave a form. I don't see this issue anywhere else, well at least up to this point. I don't know if this helps, but this particular form opens up a relational table for the first time.

The code I use to update is as follows:

Private Sub UpdateMe()
With Me.Recordset
.Edit
.Update
End With
End Sub

This is the first event called when I try to leave the form, there are other actions, but I don't hit those until after this update. Also, if I skip this sub completely, then the data is never entered into the table. But, subsequent data is updated in the next several forms bound to the same table. Any ideas on what I am doing wrong here? I am using Access2000. Thank you in advance for taking the time to look at this.

Scot

.edit runtime error '3426'

Hmmm, I suppose this might be a stumper. Would anyone, perhaps know of another method I could try to work around this issue? I am at a wall and have run short on ideas. Any help would be greatly appreciated. Thanks again,

Scot

My first thought when I originally read this post was "You have to work pretty hard to keep Access from saving data. Why would someone be writing code to save data?" It might be that you have made a problem out of what is a *non* problem in Access. You will probably have to supply additional details before someone could assist in pinning down the problem. For example, why are you using a RecordSet?

Thank you, RuralGuy, for looking at this. I agree with you that it would be pretty hard for me to keep Access from saving data, but alas, I've done it.

Ok, I'll try and explain as best I can the project I am working on.

I am taking an interview used for epidemiological research, (the study and classification of diseases, in this case social problems, such as substance abuse, etc.), anyway, I am computerizing a hard copy survey and there are quite a few skip outs depending upon how a person responds, this is due to certain algorithmic patterns for proper DSM-IV(Diagnostic and Statistical Manual of Mental Disorders, i.e., the psychiatrist's dignosis bible) diagnosis.

Now, that having been said, I have created a relational database. I have tried to normalize as best as possible all of my tables. I am using a primary table with a RespondentID, or RID (No Dups), and an Interviewer ID as my primary Keys. I have some tables that require a free listing from the respondent where they give us a list of items and then they need to be able to rank those items by importance. I am using subforms to do this, because I need to see all of their responses at once. I also need to keep the original order by which the list was given. So, those tables have a 'many' relationship to the Primary table. Access loves to save stuff so multiple records are not an issue here, although Access always seems to create extra records that I have to go back in and clean up (but that is another issue). However, these forms require information from the primary table for certain questions. I use a refresh method based upon the RID to do this. There are seven unique tables and forms within this section.

The next section is another ranking section, however this time the answers are preset so all, I need is to enter the respondents answers. There are four forms connected to the same table hence, as I move from one form to the other, I do not want duplicate records here so, I use a 'find' method to locate my record then update with new information. This works fine.

My third section is based upon alcohol and drug use, abuse, and dependence. The set up is very similar to the second section, except that here, if the respondent does not drink, there is no reason to ask the drinking questions, i.e., a skip out. It is here I have my problem. Now, one thing I was doing at the beginning of a new section and table was appending a new record with the RID to the table, so that I have the correct RID and may update it as I move through the interview. I had to start using recordset because Access was saving everything in a stairstep pattern, where everytime I opened a new form Access would create a new instance in the table. I've corrected that faux pas (set tables keys to no dups) so instead of saving , which sometimes gave me a duplicate error I just update the data, using .recordset.

Further into the interview I have calculated fields and modified fields within a table.

I hope this information helped. If you have anymore questions please donot hesitate to ask. Thanks,

Scot

If you used the BeforeUpdate event of the Form to place the RID you would avoid the additional records all of the time. BeforeUpdate *only* occurs when there is new data to save. If you skipped this section you would not get the event.

There is also the BeforeInsert event of the Form to catch new records.

Again thank you Rural Guy for taking the time to look over this situation. I appreciate the heads up on the BeforeUpdate event. But, before I recode this beast, why is it that I am getting this RT err at this point? I can walk it through and it bogs down everytime right there. Any ideas?

Scot

I still receive the same error with either the BeforeInsert or the BeforeUpdate events. This is what makes programming 'Not Fun' :-(

The code you originally posted was: I have no idea what you expected this code to accomplish. How about posting *all* of the code you place in the BeforeUpdate event from Private Sub...through...End Sub. Maybe we can see something.

Honestly, I expected it to take the current recordset (that being the one bound by the form and or control, hence, the Me.Recordset) and then run through an edit method and then an update method. That simplified code works everywhere else without issue. Therefore, I used it in the BeforeUpdate event. I understood you to place what code I need in the BeforeUpdate event for the form. So I did, same result, of course it tis the same code: Except I wanted it to show me it had updated with the MsgBox.

Private Sub frAU1A_BeforeUpdate(Cancel As Integer)
With Me.Recordset
.Edit
.Update
End With

MsgBox "The value of " & AU1A & " has been entered into the table for " & RID & "."

End Sub

I'm sorry but AFAIK there is no reason to ever use code such as you have constructed. When I mentioned the BeforeUpdate or BeforeInsert events I had in mind code such as:

[RID] = Me.NewRIDcontrol

How are you filling the required Primary Key fields now? If you do it here (in this event) then you will not be creating records unnecessarily since there is other data to store.

I don't doubt that I do not create the most polished code. Unfortunately, there is not one person in this office with nary a clue about programming. Including myself it appears. However, I created the way I have, because that is the only way I could conceive it at the time. Call this program a steep learning curve for me. My original idea was to create global variables and use those to populate the Respondent ID (RID)controls on each form. Unfortunately, Access appears to drop these global variables from time to time, so I refresh them whenever I activate a new form. At that time I populate the RID control so the interviewer can see that they are on the correct record, or ID at least. I don't know, I find Access does some things some times and other things other times. I just do what I can and then put on my fireman's hat and cruise around the program and put out fires. I have no resources other than here and maybe one other forum, and a few books, so everything I do is self taught for the most part.

As far as the run-time error I originally asked about - I have found that in this particular instance Access was updating automatically (most likely because this was a new table and form). Therefore, if I dropped the update all together it works fine, but only here, in the very next form if I do not update the data is lost, it is not automatic as I would expect with bound controls. So in a nutshell, the RT Error was that Access was doing the same thing twice at once.

RT Error resolved.

Again RG thank you for your input, I hope I have not aggravated you too much.

Scot

Aggravation had nothing to do with any of my comments. I was truly interested in what you intended with the code. There are several ways to save records and maybe now I know another. Here are a couple of the methods I use:

DoCmd.RunCommand acCmdSaveRecord

or

Me.Dirty = False

In your first response to me it was obvious you knew a great deal about your subject. What I did not learn and have as yet to learn is how you have constructed your database. As long as you are using bound controls (which I believe you said you were) then if you attempt to move off of that record after one of the bound controls has been changed, Access either barks like a stuck pig or silently saves your changes. Convincing Access to not save those changes means either using *unbound* controls or issuing an UnDo somewhere. It does not sound like you are doing either. Therefore I believe Access has been saving your data all of the time.

When it comes to programming in a complete language such as VBA then there are probably a dozen ways to accomplish any given task. All of the methods probably work just fine. Some just involve a little more code. Access is a very powerful development environment with a ton of built in database and user interface features. If used, these features make project development quite easy in most areas. I have no doubt that you have succeeded in getting your system to do as you desire. I was only trying to point out in my clumsy manner that your tasks could probably be eased greatly or perhaps eliminated completely if you availed yourself of some of the build in features.

Glad you were able to resolve your issue and good luck with the rest of the project.

I thought I did understand, but it is obvious, especially with the errors I am getting that I do not fully comprehend what I am attempting to do. I personally, do not think what I am trying is that complicated, and yet, I have managed to create a 'bear' of a program, and not a trained bear at that. Regardless, I have been attempting to make this program as modular as possible to 1) for me, minimize the coding I have to do (to date, I've probably coded through this damn thing about four times), 2) to allow global changes, and 3) to help with quickly spotting any errors that pop up.

Ahh, the best laid plans.....anyway, I receive these err msgs like 'a hound dog gets ticks' I realize that it has to do with the method with which I am attempting to save my records. An early problem, I had, with just allowing Access to save was that I was either not getting data saved or it was saving a record 'stair stepped' as multiple records within the table, or even worse, I was just writing over the previous record. So, I thought if I could control the save method and insure that Access saved a record when I wanted it to and saved that data to specifically the table and record I told it to, then I would be fine. But, Access reminds me of my last three girlfriends, in that it doesn't like being told what to do. At least by me.

Regardless, I thought if I could wait until I began the event to move out of a form that I could update the data from the form into the correct table and record. Then close that form down and bring up the next form (the next form could possibly be almost any listed in the db, so I never knew which table or form would actually be next. Yet, the flexibility to move around is required.) Well, in trying to figure out how I was going to accomplish all this I came across Recordset manipulation and it appeared to be what I wanted. Problem is I don't always know what Access is doing behind the scenes - thats where I ran into the original RT Err that this thread is based on. I am looking at the 'DoCmd.RunCommand acCmdSave' that you presented. Just prior to reading your last thread I think I had stumbled across a more verbose method, that allowed the update code to remain ( I became attached to it, and wanted to save it if possible) so I had modified it to the following:

Private Sub UpdateMe()
DoCmd.OpenTable "Sect3", acViewNormal, acEdit
With Me.Recordset
.Edit
.Update
End With
DoCmd.Save acTable, "Sect3"
DoCmd.Close acTable, "Sect3", acSaveYes

End Sub

Of course if the method you suggested does the same thing, which I am about to play with, then that may be all I need. I'll let you know if it works for me and if not what issues I run into.

Once again, thank you for taking the time to review this issue. Hopefully, I can salvage this and move on.

Scot

Ok - well I should have remembered the first rule of thumb K.I.S.S - Keep It Simple Stupid. Thank you RG the DoCmd.RunCommand acCmdSave works. Hopefully this will be my 'rosetta' stone and everything else will fall into place.

Scot

Glad to help. Please note: