Access adp does not consistantly save records

[TomGruett]

I have an Access 2003 adp using a SQL Server 2008 backend. There are approx 20 users who are located in a different state and are connnected using the company WAN. Records do not save about 30% of the time. In my efforts to research/resolve this, I have coded a save on the data entry form using the Dirty property on a combo box that is one of the first things a user uses. I have also coded the save on a command button that is one of the last things a user uses. The 1st save works fine, when there is only a litle bit of data in the form. The 2nd one fails about 30% of the time. Several of the fields typically contain about a paragragh or two of text that the user inputs (datatype is text) which are never populated with the 1st save, and always populated with the 2nd. What I end up with is an incomplete record and the user ends up having to close the form and reopen it because he can't get off the record. Any help would be GREATLY appreciated!

 

[PWise]

Is there a trriger on this table that inserts a record to an other table?

 

[TomGruett]

There is no trigger. It's just a simple form linked directly to the table. It does the normal save when you leave the record to go to a new one (using the NewRecord command), plus the two hard coded saves that I added to it.

 

[PWise]

What is the code behind the command button

 

[TomGruett]

On Error GoTo ErrorHandler:


If Not IsNull(Me!cboLetterSelection) Then

'Saves the record
If Me.Dirty = True Then
Me.Dirty = False
End If

MsgBox "Record has been saved.", vbExclamation, "Record Saved"


'Enters log info
Dim SQLUserLogCreate As String
SQLUserLogCreate = "INSERT INTO tblLog ( Form, Button, Lettertype, DateTime, MemLstName, MID, ReqSrv, Path, UserID, RecID ) " & _
"SELECT 'Main', 'Create Letter', '" & Me!cboLetterSelection & "', getdate() AS DateTime, '" & Me!txtpat_last & "', '" & Me!txtmid & "', '" & Me!cboReqService & "', '" & Me!txtPath & "', '" & Environ("UserName") & "', " & Me!txtRecID

DoCmd.RunSQL SQLUserLogCreate

'Checks to see if all required fields are populated

If Me!cboLetterSelection = "2001 Adult Initial" Then
Call RequiredFields_Adult_Initial
ElseIf Me!cboLetterSelection = "2001E Child Initial" Then
Call RequiredFields_Child_Initial
End If


'Checks to see if a letter has already been created
Dim intYesNo As Integer
If Not IsNull(Me!txtPath) Then
intYesNo = MsgBox("A letter has already been created for this member. Do you want to create another one?", vbYesNo, "Letter Already Created")
If intYesNo = 6 Then
Me!txtLetterSent = 0
Call CreateLetter
Else
End
End If
Else
Call CreateLetter
End If

'Saves the record

If Me.Dirty = True Then
Me.Dirty = False
End If


Exit_cmdSend:
Exit Sub

ErrorHandler:
Call ErrorHandler1


*************
I just realized that I have the Dirty command at both the beginning and end of the code, but the problem was occuring before I added that code. Also, this database works perfectly for me when I test it. I am located in the same building as the actual SQL Server. It is the users who are located in other service centers that are having the issues.

Also, the SQL statement that creates the log file also works fine.