How To Stop Loosing Data When You Add A New Record To A Linked SQL Server Table
When you add a new record to a linked Microsoft SQL Server table and then move to a different record, you may notice one of the following symptoms:
- When you press the TAB key to move to the next new record after entering any part of a new record in a table, the record you just typed disappears.
- When you type a complete new record in a table and then move to an existing record, the record you typed disappears.
- When you type only part of a new record into a table and then move off that record to an existing record, your new record disappears, and the last record in the table appears to be duplicated.
- When you type any part of a record in a form, and then leave the record, the information you type disappears.
If any of the four items above happen to you, then according to Microsoft you can take the following measures to correct it:
- Easiest way: Obtain the Microsoft Jet 4.0 Service Pack 5 (SP5) update from www.microsoft.com's website.
- Or you can use an Access project instead of an Access database. By using an Access project, you do not need to have linked tables to connect to a SQL Server database.
- Or you can use a form, by using Microsoft Visual Basic for Applications code for the BeforeInsert and AfterInsert events to automatically requery the data and move the form to the newly added record. With the sample code below, you can add to the events to automatically refresh the record on the form.
Option Compare Database
Dim mfRequery As Boolean
Private Sub Form_AfterInsert()
If mfRequery = True Then
' If the variable indicates a Requery
' is needed, Requery the form.
' Move back to the record that was just added
DoCmd.GoToRecord acDataForm, Me.Name, acLast
mfRequery = False
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord = True Then
' If this is a new record, set a variable
' to indicate the need to Requery.
mfRequery = True
This problem can also be solved by just refreshing the listing. Don't retype your data just because you see it missing, refresh first or use one of the listed tips to solve the problem permanently.
This tip is extracted from the Microsoft Knowledge Base, Question: #Q251289.
Just click on the button for the Tiproom's Home Page.
Date of last revision: 17 August 2001.