×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access adp does not consistantly save records

Access adp does not consistantly save records

Access adp does not consistantly save records

(OP)
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!

RE: Access adp does not consistantly save records

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

RE: Access adp does not consistantly save records

(OP)
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.

RE: Access adp does not consistantly save records

What is the code behind the command button

RE: Access adp does not consistantly save records

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close