Record Locks between SQL Linked Table and Access Form

[accvbalearner]

Hello All, hope there is someone out there who has had a similar issue and can let me know where I went wrong!

I have an Access front end using linked tables through a DSN connection to SQL Tables. I have created a form for entering data into a table. The form seems to work great, but after the data is entered the records are locked on the SQL table and can't be edited. There are existing records in the table that are fine, only the records that have been added by the form are locked.

Any ideas on how to fix this? Any help is much appreciated!

Here is the situation:

1) Form referencing two tables: Table1 with an ID number I need and Table2 using that ID and adding some data.

2) Form has a main form and two subforms: Main form just has a combobox to select the ID I need, Sub1 gets data from Table1 and shows details about the ID to help the user make sure they've selected the right one. Sub2 is for entering data into Table2.

3) The user selects the ID in the Main Form and whey they exit the combobox Sub1 Updates and the cursor jumps to Sub2 for entry.

4) The user enters the required data and it goes in to Table2 no problem.

Now the data over time changes and needs updating, so when the user goes back to update it in the form I get the Write Conflict. The same thing happens when trying to enter the update manually in the table in Access. In the actual SQL Table in SQL Studio Manager I can change the data no problem, I can also change the data using a Passthrough Query or a regular Update query in Access. I just can't change the data in the form once it is entered or in the table once it is entered.

 

[theDBguy]

Hi. A write conflict error is not uncommon when there's a code and manual updates going on at the same time. I think we'll have to see your code in order to help.

 

[accvbalearner]

Thanks for the quick reply, I am sure that is what is happening, but I just don't know how to trace it.

I'm trying to figure out how to show you the code, what should I get for you to review?

If the below info isn't what you need, just let me know where to go....

Here is what I think you can use, it is from the VBA for the Forms:

From the Main Form:
Private Sub Form_Load()
'This clears the Work Order Details Subform on Open
Dim strFullSQL As String
Dim strRecSrce As String

'Store Recordsource as a variable
strRecSrce = ("[" & Me.RecordSource & "]")

'Build SQL Statement
strFullSQL = "SELECT * FROM " & strRecSrce & " WHERE FALSE;"

Me.F12aWOSubform.Form.RecordSource = strFullSQL

End Sub
Private Sub WONbr_LostFocus()
'Filters subform for Work Order # Selected
Dim selWONbr As String
Dim strFullSQL As String
Dim strRecSrce As String

'Stores the Record Source as a variable
strRecSrce = ("[" & Me.RecordSource & "]")

'Store the Combobox selection as a variable
If Len(Me.WONbr) <> 0 Then
selWONbr = " WHERE [WONbr] = '" & Me.WONbr & "'"
fmCap3 = Me.WONbr
Else
selWONbr = " WHERE FALSE;"
End If

'Build SQL Statement to run against the table to get the right Work Order
strFullSQL = "SELECT * FROM " & strRecSrce & selWONbr

'MsgBox "strFullSQL = " & strFullSQL
'Debug.Print strFullSQL
'MsgBox "fmcap3 = " & fmCap3
Me!F12aWOSubform.Form.RecordSource = strFullSQL

End Sub
Private Sub WONbr_NotInList(NewData As String, Response As Integer)
Dim intReply As Integer
intReply = MsgBox("The Work Order # '" & NewData & "' is not in the list. Would you like to add?", vbYesNo)

If intReply = vbYes Then
'This sets the form name so that F10a-WorkScopeEntryDE knows which form to go back to.
Dim currentFormName As Form
Set currentFormName = Screen.ActiveForm

fmName = currentFormName.Name
fmCap3 = Screen.ActiveControl.Name
'These fmCap's are capturing form information to send to the form being opened
fmCap2 = NewData
'MsgBox ("Form Name = " & fmName & " and the Field being updated = " & fmCap3)

' -- Open F10a-WorkScopeEntryDE form in data entry mode, passing the new data as
' -- an OpenArg
DoCmd.OpenForm "F10a-WorkScopeEntryDE", , , , acFormAdd, acDialog, NewData

'-- Record added, so cancel Access's default processing
Response = acDataErrAdded
Else
MsgBox "Please Select an item in the list."

'-- We have handled the error, so tell Access not to put up
'-- it's own default error
Response = acDataErrContinue
End If
End Sub

From the Subform
Option Compare Database
Private Sub Form_Deactivate()
DoCmd.RunCommand acCmdSaveRecord
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
Private Sub ID_Exit(Cancel As Integer)
' This will jump the cursor back to the Job Number
' in the Main Form after you've entered a record
Me.Parent.Form.sel_JNbr.SetFocus

End Sub
Private Sub WO__GotFocus()
' This sub is to get the data from the main form
' and use it in the WONbr column in the Subform
Dim selWONbr As String
selWONbr = fmCap3
If Len(selWONbr) <> 0 Then
Me.WONbr = selWONbr
Else
IsNull (Me.WONbr)
End If
'MsgBox selWONbr
End Sub

fmCap3 is a Public String in a Module

 

[theDBguy]

Hi. Thanks for trying. Every time you have something like this:

Me.ControlName = SomeValue

You are modifying the record using code. If you don't save the edited record before you 'manually" try to change something in the same record, then you'll get a write conflict error.

 

[arnelgp]

what is the Recordsource of the Main form?
can you just hardcode the recordsource.
the code right now is building a recordsource on same recordsource.
sub form will have this recordset in the end:

select * from [select * from [TableX] where false]
where [WONbr] ="????"