Save Record

  • Thread starter Thread starter Trisha
  • Start date Start date
T

Trisha

We have a database that only one person uses. Recently
when any changes are made to that database we receive an
error saying that someone has changed that record and this
will overwrite those changes. Which is fine, but only one
person uses this database and we've never received this
error before. it's just kind of annoying since it doesn't
apply. Is there an option to get rid of this message? I
don't know why it started to appear.

Thanks,
Trisha
 
Close the database and then check for an .ldb file in the database's
directory with the same name as the .mdb file. If no one is using the
database and the .ldb file is still there, delete it. If this doesn't solve
your problem, then it sounds as if you have some corruption. Make a backup
copy of your database then try a compact and a repair. In Access 97, these
are separate items, in 2000 and newer they are a single menu item. If that
doesn't do it, try creating a new database and importing the objects from
the current one. If that still doesn't work, here is some information on
database corruption.

http://members.iinet.net.au/~allenbrowne/ser-47.html
 
I tried repair/compact, didn't work. I also imported all
of the objects into a new database and that didn't work as
well. I read the article with the link you added, but
nothing in there really applied to this problem in order
to fix it.

Any other ideas that could help?

Thanks,
Trisha
 
Try removing the record locks property. Go to "Tools - Options -Advanced
(tab)" and set the default record locking to "no locks". Not sure why
you're getting the message, but that should remove it.
-Ed
 
That was already selected. But it still continues to give
me the same message.

Thanks,
Trisha
 
We have a database that only one person uses. Recently
when any changes are made to that database we receive an
error saying that someone has changed that record and this
will overwrite those changes. Which is fine, but only one
person uses this database and we've never received this
error before. it's just kind of annoying since it doesn't
apply. Is there an option to get rid of this message? I
don't know why it started to appear.
Click to expand...

See the following KB articles for possible answers:

ACC2002: Unexpected Error Occurs When Two Users Edit the Same Data in a Related
Table
http://support.microsoft.com/default.aspx?kbid=282382

ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL
Server Table
http://support.microsoft.com/default.aspx?kbid=280730

Unexpected Write Conflict Error When You Close a Form
http://support.microsoft.com/default.aspx?kbid=304181

If none of these articles seem to help, post back and include your version of
Access and a description of what action is taking place when this error message
appears.
 
I am using Access 97. What happens is this was developed
for this person to build a catalog for their department.
The first step is to get the file with the previous
catalog information. It includes sku, section it was in
and the item description. Once this is imported the user
can then move sku's around, add sku's, delete sku's,
whatever he wants to build the new catalog. This error
occurs when he tries to do anything with the sku's. For
example, when you open the form that allows you to move a
sku, you first receive an error that says: Error
inserting: NO current record, Error #, 3021. This one
however, goes away when I tried to import the data myself
starting fresh. But I still don't think he should get
that one. The one I have been talking about though comes
after that. Once you say ok and click off the record you
receive a message stating: "WRITE CONFLICT This record
has been changed by another user since you started
editing. If you save the record, you will overwrite the
changes. Save the previous changes to the clipboard to
view previous changes." Then you have the option to Save
Record, Copy to Clipboard or Cancel. If you select Save
Record it's fine, but if you Copy to Clipboard to View
previous changes it says: "You can't save this record at
this time. Microsoft may have encountered an error. If
you close your changes will not be saved." When you press
ok it closes the form.

This is the second catalog he has used this database for
and it was fine last time and started out to be fine this
time. So, that's what I don't understand. I did the
coding for the database so I know it worked. The sku is a
valid sku, I've already checked that out.

Thanks for your help.

Trisha
 
I am using Access 97. What happens is this was developed
for this person to build a catalog for their department.
The first step is to get the file with the previous
catalog information. It includes sku, section it was in
and the item description. Once this is imported the user
can then move sku's around, add sku's, delete sku's,
whatever he wants to build the new catalog. This error
occurs when he tries to do anything with the sku's. For
example, when you open the form that allows you to move a
sku, you first receive an error that says: Error
inserting: NO current record, Error #, 3021.
Click to expand...

***This error usually occurs when your code tries to move previous to .BOF,
beyond .EOF or attempts to work with or move to a record when .BOF and/or .EOF
is True. Is there some such code that is being executed in the form's Open, Load
or Current event? How is the "previous sku" information being imported? Is the
form based only on SKUs that are most recently imported, or on all SKUs? There
are a lot of questions that would need to be answered before a "diagnosis" could
be made.
This one
however, goes away when I tried to import the data myself
starting fresh. But I still don't think he should get
that one. The one I have been talking about though comes
after that. Once you say ok and click off the record you
receive a message stating: "WRITE CONFLICT This record
has been changed by another user since you started
editing. If you save the record, you will overwrite the
changes. Save the previous changes to the clipboard to
view previous changes." Then you have the option to Save
Record, Copy to Clipboard or Cancel. If you select Save
Record it's fine, but if you Copy to Clipboard to View
previous changes it says: "You can't save this record at
this time. Microsoft may have encountered an error. If
you close your changes will not be saved." When you press
ok it closes the form.
Click to expand...

***This usually occurs when you have made modifications to a record in a form
and, before those modifications have been saved to the table, you either open a
second form based on that record and make changes or you run code that makes
changes to that record. The resolution, in that case, is to make sure that you
commit the changes made in the form before opening the second form or running
such code:

If Me.Dirty then _
Me.Dirty = False
This is the second catalog he has used this database for
and it was fine last time and started out to be fine this
time. So, that's what I don't understand. I did the
coding for the database so I know it worked. The sku is a
valid sku, I've already checked that out.
Click to expand...

***I suppose there's a possibility that your project is suffering from
corruption of some sort. If it is corruption in the VBA project, you might be
able to recover using the /decompile command-line switch (be very careful with
this one and if you need more info on this, just say so) and if it's in tables,
forms, etc., I recommend visiting the following page at Tony Toews' web site:

Corrupt Microsoft Access MDBs FAQ
http://www.granite.ab.ca/access/corruptmdbs.htm

If nothing above seems to help, go ahead and post a reply to this message with
more details on what is happening, and when, and we'll try to work it out.
 
Here is the code from the two forms:

First form which is where you select the sku you want to
move:

Public strSku As String ' Variable used to
store the sku that InCom would like to add to the catalog.


Private Sub Form_BeforeUpdate(Cancel As Integer)
txt_user.Value = fOSUserName
txt_Date.Value = Date & " " & Time()

End Sub

Private Sub cmb_Download_Click()

' Allows InCom to control which sku's they would like
to see. All is every sku that
' either was in the catalog before or is now going to
be in the catalog. And then they
' can choose Downloaded or Not Downloaded also.

'Filter to a specific file or "*" for all records
If cmb_Download.Text = "All" Then
Form.Filter = ""
txt_LdDate.Enabled = True
ElseIf cmb_Download.Text = "Downloaded" Then
Form.Filter = "((tbl_Download_Catalog.Downloaded)
= Yes)"
txt_LdDate.Enabled = False
ElseIf cmb_Download.Text = "Not Downloaded" Then
Form.Filter = "((tbl_Download_Catalog.Downloaded)
= No)"
txt_LdDate.Enabled = False
End If
Form.FilterOn = True
Form.Requery
End Sub

Private Sub cmdMoveSku_Click()

On Error GoTo Err_cmdMoveSku_Click
msgbox "If you do not move the sku then you will need to
check the download box again " & _
"in order for the sku to be in the next catalog."

Downloaded = False

DoCmd.OpenForm "frm_Additional_Skus", acNormal, , ,
acReadOnly, , txt_Sku.Value

Exit Sub
Err_cmdMoveSku_Click:
msgbox Err.Description


End Sub

Second Form which is where you say you want the sku to be
moved:

Dim strMySql As String ' A Select query that
sorts data and then is called from other locations
Dim ListRecords As Recordset ' Verify the records
are there to count.
Dim lngSequence_Number As Long ' Long integer used to
update the sequence so a new sku can be inserted yet still
keep the order for the catalog.
Dim strAfterSection As String ' Verifies section
after new sku to match against previous section.
Dim strBeforeSection As String ' Verifies section
before the new sku to match against the section afterwards.
Dim strSection As String ' This is the section
that will be inserted with the new sku.
Dim strBeforeProductHeader As String ' This determines
what the Product Header was before the sku that is added.
Dim strAfterProductHeader As String ' This determines
what the Product Header was after the sku that is added.
Dim strProductHeader As String ' This is the Product
Header that will appear in the table to be downloaded.
Dim strWhse As String ' Variable to define
warehouse. This is used in case there are ever any
additional warehouses added.
Dim strNotFound As String ' Variable to find
first warehouse to update Me.OpenArgs for one warehouse
only.
Dim strCount As String ' Variable to make
sure it has found one warehouse to update the sku with
that information.
Dim ListWarehouse As Recordset ' Verify the records
are there for a valid warehouse.
'This shows who edited the vendor cost or the maximum sku
amounts to charge and when.
Private Sub Form_BeforeUpdate(Cancel As Integer)
txt_user.Value = fOSUserName
txt_Date.Value = Date & " " & Time()

End Sub


Private Sub cmd_Insert_Additional_Sku_Click()

On Error GoTo FileError

' The variable takes the section and product header which
will be after the sku that is being added to use later
' on to verify which section and product header should be
used for the sku that is being added.

strAfterSection = txtSection
strAfterProductHeader = txtProduct_Header

' This updates every sequence number after where the new
sku will be in order to keep the sku's
' in the proper order for the catalog.

DoCmd.RunSQL "UPDATE tbl_Download_Catalog SET
tbl_Download_Catalog.Sequence_Number =
[tbl_Download_Catalog].[Sequence_Number]+1 " & _
"WHERE (((tbl_Download_Catalog.Sequence_Number)>=" &
txtSequence_Number & "));"
' This is used in case a sku is inserted before sequence
number 1 which would error out
' because it can't subtract the two to see what is ahead
of the sku that was in the first spot.
If txtSequence_Number <= 2 Then
strSection = strAfterSection
strProductHeader = strAfterProductHeader
Else
' This select statement selects the section and
product header that are BEFORE the new sku
' that is being added in order to check if they are
the same as the ones after the new sku.
strMySql = "SELECT tbl_Download_Catalog.Section,
tbl_Download_Catalog.Product_Header " & _
"FROM tbl_Download_Catalog " & _
"WHERE (((tbl_Download_Catalog.Sequence_Number)="
& txtSequence_Number - 2 & "));"

Set ListRecords = CurrentDb.OpenRecordset(strMySql)
ListRecords.MoveFirst
strBeforeSection = ListRecords.Fields("Section")
strBeforeProductHeader = ListRecords.Fields
("Product_Header")

' This verifies the section and product header are the
same before and after the sku. If they
' are the same then that will be inserted with the sku
that is added. If they are not the same
' the user will be prompted to determine what they
would like in those fields. It does not
' check to see if either of the before or after are
entered in case there is a new section
' or product header added to the catalog.

If strAfterSection = strBeforeSection Then
strSection = strAfterSection
Else
strSection = InputBox("What section would you like
the new sku to be in? Would you like " & strBeforeSection
& " or " & strAfterSection & "?")
End If

If strAfterProductHeader = strBeforeProductHeader Then
strProductHeader = strAfterProductHeader
Else
strProductHeader = InputBox("What Product Header
would you like the new sku to be in? Would you like " &
strBeforeProductHeader & " or " & strAfterProductHeader
& "?")
End If
End If

' This inserts the new sequence number, sku, section,
product header and checks yes for the download.

DoCmd.RunSQL "INSERT INTO tbl_Download_Catalog " & _
"(Sequence_Number,Sku, Section, Product_Header,
Downloaded, Who_Edited) " & _
" VALUES(txtSequence_Number - 1," & Me.OpenArgs
& "," & strSection & ",""" & strProductHeader & """,
Yes, """ & fOSUserName & """);"

strMySql = "SELECT dbo_ITM_WhseInfo.WhseCode,
dbo_ITM_WhseInfo.WhseNumber " & _
"FROM dbo_ITM_WhseInfo " & _
"WHERE (((dbo_ITM_WhseInfo.WhseNumber)
<> ""0"")) " & _
"ORDER BY dbo_ITM_WhseInfo.WhseNumber;"

Set ListRecords = CurrentDb.OpenRecordset(strMySql)
ListRecords.MoveFirst

'This updates all of the item information needed from the
ITM_RegWhse table in VAMPIRe
'in the tbl_Download_catalog_info table so keep the
information up-to-date and also
'so InCom can quickly see information so they can
determine if the sku's should be kept in
'the catalog or not. The BeginTrans and CommitTrans allows
the update to go a little faster.
strNotFound = True
DBEngine.Workspaces(0).BeginTrans
Do While Not ListRecords.EOF And strNotFound = True
strWhse = ListRecords.Fields("WhseCode")
strCount = "Select Count(*) as count " & _
"From dbo_ITM_RegWhse " & _
"WHERE (((dbo_ITM_RegWhse.item_number)
= """ & Me.OpenArgs & """))AND ((dbo_ITM_RegWhse.Warehouse)
= """ & strWhse & """);"
Set ListWarehouse = CurrentDb.OpenRecordset(strCount)
If ListWarehouse.Fields("count") = 1 Then

' This updates all of the item information for the
additional sku that was just added.
DoCmd.RunSQL "UPDATE (dbo_ITM_RegWhse LEFT JOIN
tbl_Download_Catalog ON dbo_ITM_RegWhse.Item_Number =
tbl_Download_Catalog.Sku) INNER JOIN dbo_VDR_Vendor " & _
"ON dbo_ITM_RegWhse.Vendor =
dbo_VDR_Vendor.Vendor SET tbl_Download_Catalog.Department
= [dbo_itm_regwhse].[department], " & _
"tbl_Download_Catalog.Vendor_Number =
[dbo_ITM_RegWhse].[vendor],
tbl_Download_Catalog.Vendor_Name = [dbo_VDR_Vendor].
[VdrName], " & _
"tbl_Download_Catalog.Manufacturers_Description
= [dbo_ITM_RegWhse].[mfgDescription],
tbl_Download_Catalog.Warehouse = [dbo_ITM_RegWhse].
[Warehouse], " & _
"tbl_Download_Catalog.Member_Cost =
[dbo_ITM_RegWhse].[MbrCostClassicMult3],
tbl_Download_Catalog.Retail = [dbo_ITM_RegWhse].
[Retail], " & _
"tbl_Download_Catalog.Status =
[dbo_ITM_RegWhse].[Status], tbl_Download_Catalog.Sub_1 =
[dbo_ITM_RegWhse].[Sub_Item_1], " & _
"tbl_Download_Catalog.Sub_2 =
[dbo_ITM_RegWhse].[Sub_Item_2],
tbl_Download_Catalog.Load_Date = [dbo_ITM_RegWhse].
[LoadDate] " & _
"WHERE ((tbl_Download_Catalog.Sku)= """ &
Me.OpenArgs & """) AND ((dbo_ITM_RegWhse.Warehouse)= """ &
strWhse & """);"
strNotFound = False
End If
ListRecords.MoveNext
Loop
DBEngine.Workspaces(0).CommitTrans

' The form will automatically close since there is nothing
the user can do without choosing
' a sku from another form first.

DoCmd.Close acForm, "frm_Additional_Skus", acSaveYes

Exit Sub
FileError:
msgbox ("Error inserting : " & Err.Description
& ", Error # : " & Err.Number)
Screen.MousePointer = vbNormal

End Sub

The previous sku's are being imported through a .csv file
once they are imported all of the sku's are checked to
verify they are current sku's in our system and not
discontinued. The form is only bases on the sku's that
were most recently imported. The old sku's are deleted
prior to the import.

I am not familiar with /decompile command-line switch. If
you have an article I could read that would be helpful, I
would appreciate it. I don't think there is corruption in
the tables because I imported them all to a new database
and did not receive any error messages. In the past, I
have received errors with corrupted tables.

I hate to start changing code if the database is
corrupted. Should I try to import all objects into the
database and just copy the lines of code and create new
modules and past in the lines rather than importing? Will
that fix the VBA corruption if there is any?

If I haven't provided enough information, please let me
know, but I hate to provide too much and waste your time.

Thanks so much for all of your help,
Trisha
 
Responses interspersed.
Here is the code from the two forms:

First form which is where you select the sku you want to
move:

Public strSku As String ' Variable used to
store the sku that InCom would like to add to the catalog.
[...]

Private Sub cmdMoveSku_Click()

On Error GoTo Err_cmdMoveSku_Click
msgbox "If you do not move the sku then you will need to
check the download box again " & _
"in order for the sku to be in the next catalog."
Click to expand...

****Field value is vet in this form ("Downloaded" is a field name) and the
form's record remains "dirty" while the code proceeds.

'******************************
Downloaded = False '******************************


DoCmd.OpenForm "frm_Additional_Skus", acNormal, , ,
acReadOnly, , txt_Sku.Value

Exit Sub
Err_cmdMoveSku_Click:
msgbox Err.Description


End Sub

Second Form which is where you say you want the sku to be
moved:
[...]

Private Sub cmd_Insert_Additional_Sku_Click()

On Error GoTo FileError

' The variable takes the section and product header which
will be after the sku that is being added to use later
' on to verify which section and product header should be
used for the sku that is being added.

strAfterSection = txtSection
strAfterProductHeader = txtProduct_Header

' This updates every sequence number after where the new
sku will be in order to keep the sku's
' in the proper order for the catalog.
Click to expand...

***Values changed in table via SQL (not sure if it includes the record that is
still open in the first form, but it appears to include that form's current
record).

'****************************************
DoCmd.RunSQL "UPDATE tbl_Download_Catalog SET
tbl_Download_Catalog.Sequence_Number =
[tbl_Download_Catalog].[Sequence_Number]+1 " & _
"WHERE (((tbl_Download_Catalog.Sequence_Number)>=" &
txtSequence_Number & "));"
Click to expand...
'****************************************

If this does, in fact, set a value in the same record that is current in the
first form, this would generate your error message. Try setting the first form's
"dirty" property to False before opening the second form and see if the problem
goes away.
 
I just spent the day trying to sleuth out a problem like you describe.

In my case, I had added a new 1-bit-field to a table in SQL*Server.
This field was associated with a checkbox on my ACCESS 2000 form, to
let the user flag an invoice as closed.

The Microsoft Knowledge Base article Bruce identified:
http://support.microsoft.com/default.aspx?kbid=280730
helped me see the problem I had. In my case, I added the field to an
existing table, and the initial values for that field were NULL.
Apparently the JET engine looked at the NULL values differently on the
ACCESS side and SQL*Server side -- and decided that the NULL values
represented different values on each side.

So I went back to SQL*Server and set the values of the rows in that
new column to "0" (and also set the default value to "0"). Then in
ACCESS I relinked the table to make sure all was consistent between
SQL*Server and ACCESS.

It's working fine now.
 
Back
Top