Write Conflict error on single user Application

[furnitureheaven]

Hi
I have got a error message in access 2003. I have 2 forms where (in one form user input the data and its store into a table)

So when user clicks the save button it’s save record in table and update a lookup table. And same time it open a 2nd form and shows the last record in the table.

Every thing working Ok, but every time when I click on “Save button” I got a message

“Write Conflict”
This record has been changed by another user since you started editing it. If you save record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

Then three button on

Save Record --------- Copy to Clipboard----------- Don’t Save record

So if I click any of these button, my lookup table record hasn’t update, and if I click on close, its update the lookup table.

How I can remove the “Write Conflict” error, and update record in lookup table

I have past the code here.

[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3]Private Sub cmdsave_Click()[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim stDocName As String
Dim stLinkCriteria As String
Dim it As String[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE LegLook SET LegLook.FRefNo = CSTR(CINT(LegLook.FRefNo) + 1) WHERE (((LegLook.Ftype)= " & [Forms]![LegFile].[Combo8] & "));"
DoCmd.RunSQL "Insert into LegFile (RefNo, FName, Description, OPDate, FHolder, OCNo, OCDate, DeedNo, BoxNo)" _
 & "Values ('" & Forms!LegFile.Combo8 & "0" & Forms!LegFile.frmRefNo & "', '" & Forms!LegFile.frmName & "'," _
 & " '" & Forms!LegFile.frmDescription & "', '" & Forms!LegFile.frmodate & "', '" & Forms!LegFile.frmfholder & "'," _
 & "'" & Forms!LegFile.frmOCNo & "', '" & Forms!LegFile.frmOCDate & "', '" & Forms!LegFile.frmDeedno & "', '" & Forms!LegFile.frmBoxNo & "');"
DoCmd.SetWarnings True
it = "" & [Forms]![LegFile].[Combo8] & 0 & [Forms]![LegFile].[frmRefNo]

stDocName = "Legal Files"
stLinkCriteria = "[RefNo]=" & "'" & it & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
    
DoCmd.Close acForm, "LegFile"
 
End Sub[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]

 

[jal]

I don't understand your code. You have a table called LegFile AND a form by the same name? (Probably not a good idea). You said:

INSERT INTO LegFile...Forms!LegFile.frmRefNo

Also, use an SQL string to capture your query, like this
Dim SQL as string
SQl = "UPDATE LegLook SET...."

Then you can do

MsgBox SQL

to verify your query is coming out right and you can even paste it into SQL view and run it from there. Also paste it here to show us. (Use Control-C to pull it from the msgbox).

 

[lution]

Is the record open in more than one form? If so the other form could have the record (or depending on your row locking setting more than one row) locked so this query can't do the update.

-Lution

 

[furnitureheaven]

I don't understand your code. You have a table called LegFile AND a form by the same name? (Probably not a good idea). You said:

INSERT INTO LegFile...Forms!LegFile.frmRefNo

Also, use an SQL string to capture your query, like this
Dim SQL as string
SQl = "UPDATE LegLook SET...."

Then you can do

MsgBox SQL

to verify your query is coming out right and you can even paste it into SQL view and run it from there. Also paste it here to show us. (Use Control-C to pull it from the msgbox).

hi
i thought if i attach a db then its eassy to give you an idea. please look in the program.
Desperately waiting for suggestions.
Thanks

 

[jal]

I'm having trouble confirming the problem. When I click "Save" the new record is added to the table - except when i tried to add the same new record twice (understandably).

 

[furnitureheaven]

I'm having trouble confirming the problem. When I click "Save" the new record is added to the table - except when i tried to add the same new record twice (understandably).

Sorry, I just missed the code which cause problem in the above attachment file. I have added a code which I have originally on the project. Now you can now check the attach db and problem which I am getting still.

Thanks for looking again.

 

[jal]

My approach is to avoid databinding as much as possible - it is confusing. Avoid wizards, instead use VBA code as much as possible. In other words don't have a recordsource for your forms, or at least not all of them. I think that's what is causing the problem here.

Apparently you have two forms and two tables.
Form1....table1
Form2......table2

Form2 is trying to update both table1 and table2. The result is an error, because Form2 only has rights to table2. That's my theory, anyway.

Make everything unbound and then populate the values using VBA code, for instance in the Form_Load event.