Error 3197 Please Help!

[Lantern Sky]

Here is the error that I'm getting. EDIT: THIS IS ACCESS 2003

Run-time error '3197':
The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

Alright so lets start from the beginning. At the moment I have a form who's Records Source is a table called Selected. This Form has three pages. On the first page the user searches for a record that they want to edit and they append it to the Selected Table. On the first page there is a list box showing the contents of "Selected". Thats all you need to know about the first page.

On the second page is where the user actually edits the record. On this page I have a very similar list box except this one was made with the 'Find a record' option. I was told that this was how I wanted to make it if I wanted to be able to select a record in that list box and have the corresponding values appear in the appropriate text boxes below the list box. The text boxes all have the appropriate field values from "Selected" as their Control Source in their properties. Finally both the list boxes on the first and second page are refreshed (re queried) whenever "Selected" has a record appended to it or removed from it.

Thats how its all setup now when I run the form and I go to the second page when I click a record in the list box none of the text boxes show any values they remain blank. Also when I go back to Design Mode to make sure everything is properly setup and go back and click on a record again that error shows up and when I go into debug mode it highlights this line of code.
(Code in red is highlighted)

Private Sub List165_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Connectivity ID] = '" & Me![List165] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Am I taking the wrong approach to achieve what I want to take place on my form? Hope someone out there has an idea about what to do if more detail is necessary I will of coarse be happy to accommodate. However comment propreitary info is a factor in my work so I will be unable to send anything pertaining to the actual file.

EDIT: Ok now here is the really weird part when I go into design mode and go under the properties for the form if I reselect the "Selected" table as the Record Source than go back to form view the values start popping up in the text boxes! Do I need to re query the form every time I want to go to the Edit Page? (page 2)

[lagbolt]

I don't see why you need two lists. If the page one list appends a record, presumably from a different table, for editting, then page two should already know which record to edit--the one appended on page one.
If instead, page two selects an existing record in the "Selected" table to be editted, then you don't need the page one selection process.
Though this doesn't directly address your problem, my suspicion is that activity on pages one and two are in conflict, which is your problem.
If you need both these functions, design so that both cannot be used at the same time.

Also, consider the following possibility for navigating in a form...

Me.Recordset.FindFirst "YourFieldName = " & yourData

[Lantern Sky]

Damn alright please check the forum tomorrow my friend I gotta go now or I'll be late from class I'll put up a decent reply later cause I feel you might be onto something with this interference thing and I think I should elaborate a bit more on the selection page. (first page)

Have a good one.

[Lantern Sky]

Alright here's the deal with the first page, lets just call it selection. The user has two different queries they can run to search for record(s) that they want to edit. Both of these queries display their results onto two different list boxes (LB 1, LB 2) where the user double clicks on the record(s) they want to edit. It is this double click event that actually does the append to the "selected" table. Which is of course displayed by that list box we were talking about yesterday (LB 3).

So for those of you keeping score at home on the first page there are 3 list boxes two small ones display the results of the queries run by the user (LB1 and LB 2) and the last one displays the contents of the selected table (LB 3). Now lets head over to the second page (edit). Here we have a list box (LB 4) setup with the "find a record" option its displaying as many records as the user appended to the Selected table not just one. What I am trying to achieve is whatever record I have selected (highlighted) in that LB 4 should have its field values show up in the text boxes below LB 4. Already I have set the selected table as the record source for this form and the text boxes control sources all point to fields from the Selected table.

Hope that explains a bit more I think you thought that I was just trying to edit one record well thats not the case hell there might be some guy who wants to edit 5,000 of the records. To accommodate this I have the user select all the records they want to edit and append them to the selected table think of it as a an intermediate table used for editing. Like a work bench, I want to have whatever record I select up in LB 4 to have its values shown in the text boxes below LB 4. Then when I'm done editing that record I want to be able to select the record below it and edit it and so on until I have edited all the records I wanted to.

As for that line of code you gave me I have no idea what that does navigate a form I know where to put it, it obviously looks like generic code for the line that the debugger highlights. But I don't know what to fill in the places for YourFieldName and yourData so could you elaborate a bit more on it?

[neileg]

I don't understand why you want to append the records to another table to do the editing. Why can't you just query the records in the original table and edit them in the native table? You can lock the record while it is being edited

In your approach, what if two users want to edit the same record at more or less the same time? Whoever saves their record back to the main table will have their changes overwritten when the second user saves it back.

[lagbolt]

Agree with Neil. You seem to be doing way more work than you need to.

[Lantern Sky]

I understand what you guys are saying and I'm not the one who designed this thing but I am the one asked to make it to certain specs. All I know is this is what they want and I'm not about to go out on a tangent and give them something they didn't ask for. I found a solution to the problem turns out since during the forms operation its record source has records appended to it unless you actually tell the form to re query the record source it wont and it will just stay blank. Which is something I overlooked since everything else on the form was requerying just not the form itself. Thanx again for.... well I guess caring to look at my thread.

[neileg]

I would say it's your job to give them what they need, not what they have asked for. Users don't design databases.

[boblarson]

Originally Posted by neileg

I would say it's your job to give them what they need, not what they have asked for. Users don't design databases.

I have to agree. There are many times where I've been asked for something and I have to tell them that either the way they want it done is not good database design or that it isn't possible to do the way they asked.

I make the argument that they don't want bad database design because
1. it will come back to bite them later
2. I may not be here to fix it so you want something that other experienced Access users will be able to understand and fix.
3. And in my case I can remind them that I know a whole lot more about the subject than they do and if they aren't willing to take my advice then I do not guarantee that any of it will work and they are on their own should it break.

Usually one, or more, of the three will take care of the situation.